Can I select a combination of controls to sort records?

  • Thread starter Thread starter Jan Il
  • Start date Start date
J

Jan Il

Hi all! Access 2000, Windows ME

I have a dilemma, and I hope someone can help me sort it out. Our company
recently switched to a new tri-agency data system,. that is scant, if
nothing else, when it is up and running.

My problem is, our department needs to find certain expense information in
order to create open purchase orders for certain vendors. We can not get
access to the accounting information, as it is not working properly. In
order to these open PO's in effect so that our field maintainers can secure
necessary parts for repairs, we need to provide a FY expense amount for each
vendor.

We have been using the Access database I developed for our department for
the last year, and I have all the necessary data in it, but, I need to be
able to sort the records by a combination of Account codes, Vendor name,
Department, and date period.

I have created a command button that I am trying to find a way to code to
allow for these various combinations to sort the data as necessary. I have
a filter form in place that will sort by a single control and the date
period, but, it was not necessary to sort with a larger combination until
now.

The controls are; (this is an old form, so excuse the less than creative
control names)

Combo114 - Vendor
Combo99 - Dept
Text0 - AcctCode
Text9 - PoNo
Text62 - From date
Text64 - To date

The name of the new command button is cmdExpResearch

I have been trying to find a way to set the Event procedure of the
cmdExpResearch button to something like;
Check all controls, if there are entries in any of the controls, open form
AcctCode Budget Form and display all records according to the combinations
entered. I don't have a great deal of time to play with it, as the need for
our maintainers to get parts is primary at the moment, and...no one seems to
know how to fix the new program, so....right now..all we have is my Access
database to put this information together as soon as possible.

I have tried several methods, in various combinations of relationships of
code, but, my coding is not very good for this type of combination. I have
tried to find something in the Help files that would give me a better idea
of how to formulate the code, but, so far, I'm not having much luck. I've
tried to adapt some other codes that are somewhat similar, but, the
combinations are not working.

I would truly appreciate it if someone would provide me with a Help file,
how to best word my search, or a KB article that would give me more
information on how to create the code to get the results I need.

Best regards,
Jan :)
 
There is a book that you might want to invest in. It is called Beginning
Access 2000 VBA from Wrox Press. This books has a sample search form where
you can designate several fields to use as search criteria. The code behind
this form is too extensive to publish in this newsgroup.
 
Hi! Thank you very much for your time to reply.

I'll see if I can find the book you suggested.

Jan :)
 
Can you query the records and sort them in the query then create a form
based on the query?
 
Hi, and thank you for your time to reply.
-----Original Message-----
Can you query the records and sort them in the query then create a form
based on the query?
Click to expand...

Yes, I suppose this could be done, however, I already have
a query and forms in place to deal with the records. In
the essence of time, I was trying to find a way to create
a button on the filter from that already has the controls
to sort the records in the accociated form. It is a simple
matter of creating the event procedure of the button to
test the controls on the form and call up the records
according to those controls with selections in the same
associated form. Instead of sorting by one, it might sort
by 2, or three, or all four, and by date period.

I did somthing similar for another database, if I can just
adapt the code to this button, it will work.

I know what I want to do, and it can be done very simply,
I just can't seem to get the code right. But, I'm working
on it, and I'll get it yet. I must be getting closer, I'm
only getting 3 errors now instead of 5. ;-)

Thank you again for your time to assist, I appreciate it
very much.

Best regards,
Jan :)
 
Nailed it! I finally managed to pull together a simple
code that works. It is a combination of codes I have used
before in other forms or apps, my thanks to all who helped
contribute.

It may not be perfect, or 'pretty', but, in the essence of
time, which I don't have at the moment, I have tested it
from all controls, and conbinations, and it does work as
needed;

Private Sub cmdExpResearch_Click()
On Error GoTo Err_cmdExpResearch_Click

Dim strWhere As String

Dim stDocName As String
Dim stLinkCriteria As String

strWhere = ""
' do vendor
If IsNull(Me!Combo114) = False Then
strWhere = "[Vendor]=" & "'" & Me![Combo114] & "'"
End If

Me![Combo114] = Null


' do dept
If IsNull(Me!Combo99) = False Then
If strWhere <> "" Then strWhere = strWhere & " and "
strWhere = strWhere & "[Dept]=" & "'" & Me![Combo99]
& "'"
End If

Me![Combo99] = Null

' do poNo

If IsNull(Me!Text9) = False Then
If strWhere <> "" Then strWhere = strWhere & " and "
strWhere = strWhere & "[PONo]=" & "'" & Me![Text9] & "'"
End If

Me![Text9] = Null

' do acctcode

If IsNull(Me!Text0) = False Then
If strWhere <> "" Then strWhere = strWhere & " and "
strWhere = strWhere & "[AcctCode]=" & "'" & Me![Text0]
& "'"
End If

Me![Text0] = Null

stDocName = "AcctCode Budget Form"

stLinkCriteria = strWhere
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdExpResearch_Click:
Exit Sub

Err_cmdExpResearch_Click:
MsgBox Err.Description
Resume Exit_cmdExpResearch_Click

End Sub

Best regards.
Jan :)
 
Back
Top