search button w cmbo box - Single-Field Search Button in Microsoft Access


	Back to Search Button Comments List

Upload Images Link Email

search button w cmbo box

Sean Smith

14 months ago

Hello everyone. Using Access 256, I added the simple search button to a text box and it worked on my form. I'd like to add one to a relational combo box where the user only knows the description displayed, not the primary key which is the secondary key for the input table. Since the description is not in the working table, I've tried changing the Me.Filter code to Me.Filter = "ProductID='" & Me.cmbItemNo.Column(1) & "'" thinking this would link the displayed value to the recorded value. When I run this I get Run time error'3709' and stops at the line modified. Any suggestions on how to do this?

	Sami Shamma 14 months ago
I don't think you need any of this. Please send the Row Source of the combo box, and we will be able to help.

Sean Smith OP

14 months ago

Hi Sami, thank you for helping. Below is the row source of the combo box you requested. It may be obvious, but the full code I'm trying to use comes from the video titled Search Button.

SELECT [tblProducts].[ProductID], [tblProducts].[ItemNo], [tblProducts].[Description] FROM tblProducts ORDER BY [ItemNo];

The working table has ProductID as the secondary key. My users know The ItemNo, which is actually want I want to be able to search, i said description above.

	Sami Shamma 14 months ago
Is the product ID showing in box? If so, reduce its width in format to zero, then your users will only see the item number and description.

	Sean Smith OP 14 months ago
No. ProductID is the autonumber primary key of the product table and doesn't show in the combo box. ItemNo is what I'd like to search by and is the first visible column in the combo box.

	Richard Rost 14 months ago
You can't filter based on a column of a combo box. You'd have to update the RecordSource of the parent form with a custom SQL statement to show just that record. Something like: `Me.RecordSource = "SELECT * FROM ProductT WHERE ItemNo=""" & MySearchValue & """"`

	Richard Rost 14 months ago
MySearchValue could be the column of your combo box if that's what you're using to search... `... & MyCombo.Column(1) & ...` Then put that code in the AfterUpdate event of your combo box.

Sean Smith OP

14 months ago

Richard and Sami, thank you for your help on this. I was able to get the search button to work like I wanted it to. Using SQL statements never occurred to me. I tinkered with your suggestion and ended with a final solution that omitted referencing the combo box all together:

Private Sub btnSearchItemNo_Click()
    Dim S As String
    S = InputBox("Enter the description to search:", "Search by Description")

        Me.Filter = "ProductID IN (SELECT ProductID FROM tblProducts WHERE Description Like '*" & S & "*')"
        Me.FilterOn = True

End Sub

	Sami Shamma 14 months ago
Glad all worked out for you.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Search Button.