Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Combobox does not find correct record 1

Status
Not open for further replies.

DSGF

Technical User
Mar 22, 2008
17
US
I have created a form with two comboboxes. One selects company Reps by zip and the other by city. Zip combo works fine because there is always only one zip. There can be several like city names so in my City combobox I show city, county and state.
However, whenever a city is selected, it chooses the 1st record in the database no matter which was selected in the combobox.
For example.
I select Las Vegas Clark County NV
but it's results are Las Vegas San Miguel County NM
I see that this is happening because the code says fs.FindFirst...


Code:
Private Sub Combo60_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[PO_NAME] = '" & Me![Combo60] & "'"
    Me.Bookmark = rs.Bookmark
            
    Combo40.Value = ZIP_CODE.Value ' set Zip Combobox value to zip code
    etc.....

So I tried the following code but it still does not work...


Code:
Set rs = Me.Recordset.Clone
    rs.FindFirst "[PO_NAME] = '" & Me![Combo60] & "' WHERE  [ST_ABBREV] = '" & Me.Combo60.Column(2) & "'"
    Me.Bookmark = rs.Bookmark

When I run this code I get
Run Time error 3007
Syntax error (missing operator) in expression.


In debug mode I can see that Me![Combo60] = "Las Vegas" and Me.Combo60.Column(2) = "NV".
And in the Immediate Window it says:
[PO_NAME] = 'Las Vegas' WHERE [ST_ABBREV] = 'NV'

Im a bit new to Access. Is there a simple solution to my questions or am I in over my head? Thanks for any help.

 
How about:

Code:
    rs.FindFirst "[PO_NAME] = '" & Me![Combo60] & "' AND [ST_ABBREV] = '" & Me.Combo60.Column(2) & "'"

Are you aware that columns start from zero, so column 2 is the third column?
 
Perfect. I originally tried AND but my syntax was wrong. Thank you VERY MUCH!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top