Changing criteria using VBA

[jimblob21]

I want to search a database (using queries) for Orders using [Ente
Order No] as the criteria and I also want to search for Orders usin
[Enter Company Name]. Is there any way of changing the criteria usin
VBA (or any other method for that matter) so that I need only on
query? I am currently using two queries, qrySearchOrderNo
qrySearchCompName. The fields in both are exactly the same

 

[Larry Linson]

If you create a form with two text boxes, one for Order No and the other for
Company Name, you can, in VBA, determine which has been entered and
construct the WHERE clause to use in the Query.

The pertinent code, probably in the Click event of a Command Button, would
be something like

If Not IsNull(Me!txtOrderNo) Then
strMyQuery = strBaseQuery & "[OrderNo]="""& txtOrderNo & """"
Me.sbfResults.Form.RecordSource = strMyQuery
ElseIf Not IsNull(Me!txtCompanyName) Then
strMyQuery = strBaseQuery & "[CompanyName]="""& txtCompanyName &
""""
Me.sbfResults.Form.RecordSource = strMyQuery
Else
MsgBox "You must enter either Order Number or Company Name"
End If

Caveat: this is untested "air code". It assumes that you want to display the
results in a Form embedded in a Subform Control, sbfResults, on the same
form where you are entering the criteria. I didn't use spaces in the Field
Names because I find that to be less than optimal.

Larry Linson
Microsoft Access MVP

 

[Guest]

One simple way to accomplish this is just to add a new field to one query,
say SearchField: [Order No]&[Company Name]. Then set the criteria as: Like
("*" & [Enter Order No or Company Name Part]&"*"). And use it as the
exclusive data source. Part or all of the company name or order number can
then be entered to satisfy the search.

In some apps I have extended this method to include several fields as a
general search criteria.

 

[jimblob21]

Larry Linsonwrote
If you create a form with two text boxes, one for Order No and th
other fo

 

[jimblob21]

Joe Dwrote
One simple way to accomplish this is just to add a new field to on
query,