Search returns error message

[MNNovice]

Hello!

I am trying to use a search button to find : 1) VendorName and/or 2)InvoiceNo. One is working while the other is not. When I look for an invoice number, it works but when I try to find a vendor, it does not. The combo box does not list the vendor names as it should. And when I try to type a vendor name I get error message that reads: "The text you entered isn't an item in the list..."

What am I doing wrong? Can anyone please help? Thanks.

The main form is called frmFIND and the subform is called sfrmFindAP which is based on a query. The name of the query is qryAP which is working just fine.


frmFIND has this unbound combo box called cboVendor


Row Source: cboVendor

SELECT 0 as VendorID,
       "<ALL>" as VendorName
 
FROM tblzNull
 
UNION ALL
SELECT tblVendors.VendorID,
       tblVendors.VendorName
 
FROM tblVendors;

cmdFIND

Private Sub cmdFIND_Click()
  ' Update the record source
    Me.sfrmFindAP.Form.RecordSource = "SELECT * FROM qryAP " & BuildFilter
 
    ' Requery the subform
    Me.sfrmFindAP.Requery
End Sub

And here is my code:

Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
 
    varWhere = Null  ' Main filter
 
 
    ' Check for LIKE InvoiceNo
    If Me.txtInv > "" Then
        varWhere = varWhere & "[InvoiceNo] LIKE ""*" & Me.txtInv & "*"" AND "
    End If
 
    ' Check for VendorName
    If Me.cboVendor > 0 Then
        varWhere = varWhere & "[VendorName] = '" & Me.cboVendor.Column(1) & "' AND "
    End If
 
 
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
 
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
 
    BuildFilter = varWhere
 
End Function

[ChipR]

Try your combo box with just the RowSource

SELECT tblVendors.VendorID, tblVendors.VendorName FROM tblVendors

and see if that works first. It looks like you want it bound to column 1 and displaying both or only the second column.

[MNNovice]

ChipR:

It's working after I made the changes you suggested. Here is where I am puzzled. The same logic I used in my frmFIND on another DB, it worked just fine. What was I doing wrong here? Just curious to know. Thanks for your help.

[ChipR]

The cmd_FIND code looked absolutely fine to me, but the RowSource was suspicious, since the combo was not listing the names properly. Do you have a tblzNull?

[MNNovice]

ChipR:

That's it!!! Wow!. You're so smart. How could I completely forget about that important little thing. Now I know. Thank you.

[NeoPa]

Am I right to think that [tblzNull] is a table whose reason is simply to provide a dummy source for such literal values?

If so, remember that you can use the following with very little extra overhead :

SELECT TOP 1
       0 as VendorID,
       "<ALL>" as VendorName
FROM tblVendors
 
UNION ALL
SELECT VendorID,
       VendorName
FROM tblVendors

In each case simply reuse the same table as is already to be used in the other SELECT query.

[MNNovice]

NeoPa:

Yes, you are right. I was missing the tblzNull which caused the problem. It's resolved now.

Thanks for the new tip.