By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,967 Members | 1,267 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,967 IT Pros & Developers. It's quick & easy.

Search returns error message

100+
P: 418
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
Expand|Select|Wrap|Line Numbers
  1. SELECT 0 as VendorID,
  2.        "<ALL>" as VendorName
  3.  
  4. FROM tblzNull
  5.  
  6. UNION ALL
  7. SELECT tblVendors.VendorID,
  8.        tblVendors.VendorName
  9.  
  10. FROM tblVendors;
cmdFIND
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFIND_Click()
  2.   ' Update the record source
  3.     Me.sfrmFindAP.Form.RecordSource = "SELECT * FROM qryAP " & BuildFilter
  4.  
  5.     ' Requery the subform
  6.     Me.sfrmFindAP.Requery
  7. End Sub
And here is my code:
Expand|Select|Wrap|Line Numbers
  1. Private Function BuildFilter() As Variant
  2.     Dim varWhere As Variant
  3.     Dim varItem As Variant
  4.     Dim intIndex As Integer
  5.  
  6.     varWhere = Null  ' Main filter
  7.  
  8.  
  9.     ' Check for LIKE InvoiceNo
  10.     If Me.txtInv > "" Then
  11.         varWhere = varWhere & "[InvoiceNo] LIKE ""*" & Me.txtInv & "*"" AND "
  12.     End If
  13.  
  14.     ' Check for VendorName
  15.     If Me.cboVendor > 0 Then
  16.         varWhere = varWhere & "[VendorName] = '" & Me.cboVendor.Column(1) & "' AND "
  17.     End If
  18.  
  19.  
  20.     ' Check if there is a filter to return...
  21.     If IsNull(varWhere) Then
  22.         varWhere = ""
  23.     Else
  24.         varWhere = "WHERE " & varWhere
  25.  
  26.         ' strip off last "AND" in the filter
  27.         If Right(varWhere, 5) = " AND " Then
  28.             varWhere = Left(varWhere, Len(varWhere) - 5)
  29.         End If
  30.     End If
  31.  
  32.     BuildFilter = varWhere
  33.  
  34. End Function
Apr 28 '09 #1
Share this Question
Share on Google+
6 Replies


Expert 100+
P: 1,287
Try your combo box with just the RowSource
Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 28 '09 #2

100+
P: 418
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.
Apr 28 '09 #3

Expert 100+
P: 1,287
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?
Apr 28 '09 #4

100+
P: 418
ChipR:

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

NeoPa
Expert Mod 15k+
P: 31,709
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1
  2.        0 as VendorID,
  3.        "<ALL>" as VendorName
  4. FROM tblVendors
  5.  
  6. UNION ALL
  7. SELECT VendorID,
  8.        VendorName
  9. FROM tblVendors
In each case simply reuse the same table as is already to be used in the other SELECT query.
Apr 29 '09 #6

100+
P: 418
NeoPa:

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

Thanks for the new tip.
Apr 29 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.