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:

Expand|Select|Wrap|Line Numbers

 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:

Expand|Select|Wrap|Line Numbers

 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!

msquared

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!

msquared

Quote:

Originally Posted by 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!

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

msquared

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

Expand|Select|Wrap|Line Numbers

 
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

Expand|Select|Wrap|Line Numbers

 
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?

msquared

Quote:

Originally Posted by 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?

Try this

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

teric2

Hmm that results in combo4 being empty.

msquared

Quote:

Originally Posted by teric2

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

msquared

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):

Expand|Select|Wrap|Line Numbers

 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.

msquared

Try this

Expand|Select|Wrap|Line Numbers

 
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.

msquared

Quote:

Originally Posted by 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.

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.

msquared

Quote:

Originally Posted by 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.

Not a problem. Glad you got it working.

Another Multiple Combo box Question

re: Another Multiple Combo box Question

re: Another Multiple Combo box Question

re: Another Multiple Combo box Question

re: Another Multiple Combo box Question

re: Another Multiple Combo box Question

re: Another Multiple Combo box Question

re: Another Multiple Combo box Question

re: Another Multiple Combo box Question

re: Another Multiple Combo box Question

re: Another Multiple Combo box Question

re: Another Multiple Combo box Question

re: Another Multiple Combo box Question

re: Another Multiple Combo box Question

re: Another Multiple Combo box Question

re: Another Multiple Combo box Question

re: Another Multiple Combo box Question

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about