Multiple Search Command criteria in a form

[vaa571]

Hi there,

I created a form with a search command that searchs my database "Open Order
Table" for a part number and I would like to add another search criteria to
the code below...
Open Orders Table has "Part Number" and "Lot#" field and i would like to be
able to search by the lot# or by the Part Number. If searching by lot# it
woud had to be exact match but in the part number I would like to avoid
typing the entiry entry.
the code below I have to enter the entire part number...

Private Sub Command42_Click()
On Error GoTo Err_Command42_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Open Orders"

stLinkCriteria = "[PART NUMBER]=" & "'" & Me![PART NUMBER] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command42_Click:
Exit Sub

Err_Command42_Click:
MsgBox Err.Description
Resume Exit_Command42_Click

End Sub

 

[golfinray]

See www.allenbrowne.com. Allen has some cascading combo code that might be
the way for you to go for a more extensive search feature.

 

[John W. Vinson]

Hi there,

I created a form with a search command that searchs my database "Open Order
Table" for a part number and I would like to add another search criteria to
the code below...
Open Orders Table has "Part Number" and "Lot#" field and i would like to be
able to search by the lot# or by the Part Number. If searching by lot# it
woud had to be exact match but in the part number I would like to avoid
typing the entiry entry.
the code below I have to enter the entire part number...

Private Sub Command42_Click()
On Error GoTo Err_Command42_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Open Orders"

stLinkCriteria = "[PART NUMBER]=" & "'" & Me![PART NUMBER] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command42_Click:
Exit Sub

Err_Command42_Click:
MsgBox Err.Description
Resume Exit_Command42_Click

End Sub

To search by just the beginning of the part number use the LIKE operator with
an * wildcard:

stLinkCriteria = "[Part Number] LIKE """ & Me![Part Number] & "*"""

It's not clear what you want done with the lot number: do you want separate,
independent searches by the two fields, or to search on the part and lot
numbers together, or let Access determine whether the user has entered a
criteirion in one or the other control and run different searches on that
basis?