Another Multiple Combo box Question

[teric2]

Hi Everyone,

I have a form set as a dialog box with 2 Combo boxes (Combo0 & Combo4) and 2 text boxes (text8 & text10).

The form's record source is set to a table named Customers.

Combo0's row source is set to a query conatining the Customer Name Field in Customers

Combo4 lists only the citys of whaterver customer is picked from combo0.

Text10's control source is the state field in customers
Text8's control source is a unique ID field in customers.

When you choose a customer name in combo0 and then a city in combo4 text10 & text8 become visible and dsplay the related state and unique ID for that location.

My problem is that if there is more than one customer in the same city my code only returns the state(Text10) and unquie ID(Text8) for the first match that it comes to in the table.

Here's the code of combo0 AfterUpdate:

Dim wclause
 
    [Combo4].Visible = True
 
    wclause = "SELECT Customers.CITY FROM Customers WHERE customers.[company name]=[combo0];"
    [Combo4] = " "
    [Combo4].RowSource = wclause

Here's the code from Combo4 AfterUpdate:

Dim rs As Object
 
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[CITY] = '" & Me![Combo4] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
 
    [Text10].Visible = True
    [Text8].Visible = True

I'm sure the problem is in the code of combo4 but I don't know how to fix it. Any help would be greatly appreciated and Thanks in advance!

[MMcCarthy]

Have a look at this tutorial.

Cascading combo/list boxes

[teric2]

Yea, I've looked at this and my Combo boxes work fine. it's the results I want in the text boxes after picking from the combo boxes that I'm having a problem with.

Thanks!

[MMcCarthy]

Yea, I've looked at this and my Combo boxes work fine. it's the results I want in the text boxes after picking from the combo boxes that I'm having a problem with.

Thanks!

The combo0 After Update does not follow the rules of this tutorial. Have another look at it.

[teric2]

I don't think that code will work for me but I could be wrong. Here is the structure of my customer table

RXDC no <-- a unique id number or text string(field is set as text)
customer name
Address1
Address2
City
State
Zip

Each customer may have many locations in different citys each with it's own Unique ID(RXDC no) and record in the table

Combo0's row source is set to a Query that contains the customer name field sorted and set to show unique values so the same customer name doesn't appear over and over again in the combo box.

The code in the tutorial is set to return the unique ID number for the customer name selected. Which, in my case, the name for several locations will only display once.
It's the combination of Combo0(Customer Name) and Combo4(City) that will result in the unique ID(RXDC no) and record I'm looking for to display in my two text boxes.

What I want is to choose a customer name and then a city for that customer and then have the state and unquie ID for that combination display in the text boxes

[MMcCarthy]

Combo0
Row Source = SELECT DISTINCT [customer name] FROM Customers

Private Sub Combo0_AfterUpdate()
Dim strRowSource As String
 
    Me!Combo4.Visible = True
    strRowSource = "SELECT City FROM Customers WHERE [company name]='" & Me!Combo0 & "'"
    Me!Combo4.RowSource = strRowSource
    Me!Combo4.Requery
 
End Sub
 

Assuming you have a textbox to hold the value of the RXDC no called txtRXDC

Private Sub Combo4_AfterUpdate()
 
    Me!txtRXDC = DLookup("[RXDC no]", "Customers", "[customer name]='" & Me!Combo0 & "' AND [City]='"  & Me!Combo4 & "'"
 
End Sub
 

[teric2]

This works great mmccarthy. I knew you'd come through for me. You've helped me before.

One more question.
Some of my customer names have an apostrophe in them to show possesion. When I select one of these I get a "Syntax error" in the code for combo4.

Anyway to fix it other than to remove the apostrophes?

[MMcCarthy]

This works great mmccarthy. I knew you'd come through for me. You've helped me before.

One more question.
Some of my customer names have an apostrophe in them to show possesion. When I select one of these I get a "Syntax error" in the code for combo4.

Anyway to fix it other than to remove the apostrophes?

Try this

"SELECT City FROM Customers WHERE [company name]=" & """ & Me!Combo0 & """

[teric2]

Hmm that results in combo4 being empty.

[MMcCarthy]

Hmm that results in combo4 being empty.

I know there's a way of dealing with this, I just can't remember it at the moment.

I'll get back to you

[MMcCarthy]

Try this ...

"SELECT City FROM Customers WHERE [company name]=""" & Me!Combo0 & """"

If that doesn't work you may need to use this function.

http://www.thescripts.com/forum/thread641552.html

[teric2]

That worked except now I get a syntax error (Missing Operator) for this code in the AfterUpdate procedure of the second combo box(Combo4):

Me!Text8 = DLookup("[RXDC no]", "Customers", "[company name]='" & Me!Combo0 & "' AND [City]='" & Me!Combo4 & "'")

Sorry it took so long to get back to this and thanks again for helping.

[MMcCarthy]

Try this

Me!Text8 = DLookup("[RXDC no]", "Customers", "[company name]=""" & Me!Combo0 & """" & " AND [City]='" & Me!Combo4 & "'")
 

[teric2]

Afraid not. Still get the same error. Guess I'll have to try the function that you pointed me to unless you have any more ideas.

[MMcCarthy]

Afraid not. Still get the same error. Guess I'll have to try the function that you pointed me to unless you have any more ideas.

I'm not sure whats going on but I've tested the code

Me!Text8 = DLookup("[RXDC no]", "Customers", "[company name]=""" & Me!Combo0 & """" & " AND [City]='" & Me!Combo4 & "'")

And it works fine

[teric2]

My mistake. I should have looked closer. I copied and pasted your code but, in the mean time, I somehow lost the original SQL from Combo0.

Works great now!

Thanks tons for your time and patience. This is a great place to learn.

[MMcCarthy]

My mistake. I should have looked closer. I copied and pasted your code but, in the mean time, I somehow lost the original SQL from Combo0.

Works great now!

Thanks tons for your time and patience. This is a great place to learn.

Not a problem. Glad you got it working.