I hope that I can explain this. I have a form that the user will type in either a known record number, product or username and it will search another form and bring up the appropriate record(s).
1)The Username is in a M:M table with NCR so it is a subform. Whenever I try and search with this critieria it doesn't work. I just get my own error message.
2) Whenever I search with the NCRNumber, if I type in a 1, it will pull up 11,12,13, etc...... the product search works fine
3) I am trying to avoid using a filter directly.
Private Sub cmdSearch_Click()
Dim varWhere As Variant
' Initialize to Null
varWhere = Null
If Not IsNothing(Me.txtNCRNumber) Then
varWhere = "[NCRNumber] LIKE '" & Me.txtNCRNumber & "*'"
If Not IsNothing(Me.txtprodName) Then
varWhere = (varWhere + " AND ") & "[AssName] LIKE '" & Me.txAssName & "*'"
If Not IsNothing(Me.cboProcessOwner) Then
varWhere = "[Forms]![frmNCR]![sfrmProcessOwner].IDNumber & " = " & Me.cboProcessOwner.column(0)"
If IsNothing(varWhere) Then
msgbox("You must enter at least one search criteria.", , "NCR SELECTOR")
If IsNothing(DLookup("NcrNumber", "qryNCR ", varWhere)) Then
Call CustomError("No NCR meet your criteria.", , "NCR SELECTOR")
txtNCRNumber.Value = Null
txtproduct.Value = Null
DoCmd.OpenForm "frmNCR", WhereCondition:=varWhere
DoCmd.Close acForm, Me.Name