Using Combo box in search form (1 Viewer)

[Shipper225]

So I've managed to use a search form that was posted on here but I am having a problem being able to search based on the displayed results of a combo box. I am trying to have a field search the second column of the combo box but I don't know the code to do so. To simplify the question here, is there a way I can use the search function that I built to find only the results based in column two (the displayed field on the search form) here is the code I am currently using:

     'Item Search.
    If Not IsNull(Me.cboItem) Then
        strWhere = strWhere & "([Transaction Item] Like ""*" & Me.cboItem & "*"") AND "
    End If

Right now all it lets me do is search but the first column the primary key Inventory.ID and not what I need Inventory.Item

 

[RainLover]

Me.cboItem.Column(1)

The first Column is Column 0 so 1 is the second.

 

[Shipper225]

Where would I add that line in my code? I tried both sections and when I compiled I got an error. I forgot to mention I am using Access 2007

 

[vbaInet]

Where do you think you should add that line of code Shipper? Let's see what you did.

Also, don't you have a numeric TransactionID field?

 

[Shipper225]

Also, don't you have a numeric TransactionID field?

I do have a numeric TransactionID field, but since I am trying to search similar products I can't use that (also there are hundreds of products listed so very hard know what ID to look up) I tried putting the provided string as follows

Private Sub cmdFilter_Click()

    Dim strWhere As String
    Dim lngLen As Long
    [COLOR=Red]Me.cboItem = Me.cboItem.Column(1)[/COLOR]
    Const conJetDate = "\#mm\/dd\/yyyy\#"
    
    'Item Search.
    If Not IsNull(Me.cboItem[COLOR=Red].Column(1)[/COLOR]) Then
        strWhere = strWhere & "([Transaction Item] Like ""*" & Me.cboItem[COLOR=Red].Column(1)[/COLOR] & "*"") AND "
    End If

First I tried to define it in the top section, then I tried putting it in just the If statement, after that I tried putting it in the where, as a last ditch I tried a combination of all of them but can't seem to figure out where it belong. I'm still teaching myself VB so it's trial and error for me.

 

[vbaInet]

Get rid of this line:

    Me.cboItem = Me.cboItem.Column(1)

And change this line:

        strWhere = strWhere & "([Transaction Item] Like '*" & Me.cboItem.Column(1) & "*') AND "

 

[Shipper225]

When I get rid of the top line and modify the line you instructed I get a compile error "Compile error: Method or data member not found" and it highlights the .Column(1) in that string

 

[Shipper225]

I've attached a sample of the database.

 

[vbaInet]

I don't need to look at your database yet. cboItem is not the name of the combo box.

 

[Shipper225]

cboItem is the unbound field I type into for the search, Transaction Item is the field that is supposed to be searched.

 

[vbaInet]

But what is the name of the combo box control? A control is not a field. A field is an "item" in a table, a control is an object on a form (e.g. textbox, combo box etc).

 

[Shipper225]

The control source for the combo field is: Transaction Item
The row source for the field is: SELECT Inventory.ID, Inventory.Item, Inventory.Discontinued FROM Inventory;
And it is bound to column 1
I don't see anything that says Combo Box Control

 

[vbaInet]

Ok, you are using a lookup field and really shouldn't. Here's why:

http://access.mvps.org/access/lookupfields.htm

Once you've gotten rid of that, you need to change the control to a Combo box.

 

[Shipper225]

This may be a stupid question but once I remove the row source how am I supposed to change the control to a combo box since the control source remains the same (thank you so much for your patience)

 

[vbaInet]

What that link is suggesting is that you:

* Open your table in Design View
* Go to the field and click on the Lookup tab
* Change the Control Type from Combo Box (or List Box) to Textbox

Then go back to your form, drop a combo box onto it and follow the Combo Box Wizard to links things up. This link may help too:

http://www.btabdevelopment.com/ts/default.aspx?PageId=34

 

[Shipper225]

Those links were very useful, thank you very much for all the assistance. Search form works like a dream and I've learned a few new tricks that'll keep me from asking the same questions again.

 

[vbaInet]

You're welcome!

Happy developing.