Combo box values depending on another Combo Box Selection 2

bxgti4x4 · Jan 7, 2004

Ref: Thread 702-740081

I have a form with a combo box (Dispname)which enables the user to select from a table called "Ports". I would like the next combo box (Termname) to be able to display a list of the terminals that are associated with the port selected in Dispname combo box, so that the user can select the appropriate terminal. These terminals are listed against port name in another table called "Terminals".

I have tried to use the ideas in the thread above but cannot get the "Termname" combo box to give me the list required. The code I am using in the row source is:

Code:

 SELECT [terminal name] FROM [terminals] WHERE [terminals].[port name]=cbo[dispname]

However, when I use this code, I receive an error message as follows:

Syntax error (missing operator) in query expression '[terminals].[port name]=cbo[dispname]

Could someone please give some ideas on how to resolve this problem

Thanks
John Draper

gusbrunston · Jan 7, 2004

[tt]
Hi John:

I needed to do this so often...

I found Microsoft's easy to follow instructions for synchronizing combo boxes, printed it, and it is always within reach.

I suggest you go to the following URL and see if it doesn't give you an easy way to do what you want.

http://support.microsoft.com/support/kb/articles/Q209/5/95.asp

HTH

Cheers,[/tt]

[glasses]

[tt]Gus Brunston - Access2000(DAO)
Skill level based on 1-10: 7
Webmaster: www.rentdex.com[/tt]

bxgti4x4 · Jan 7, 2004

Thanks very much for that tip, I'll try it out and respond when I have done so.

Best Regards
John

Spenney · Jan 7, 2004

I have had to do this in the past for Country / State combo boxes - I found that the key to the issue is to Requery the Row Source of the second combo box on the AfterUpdate event of the first. This is an example of what I have done in the past...

-Row source of Combo 1
SELECT DISTINCTROW dbo_country.ctry_code, dbo_country.ctry_name
FROM dbo_country
ORDER BY dbo_country.ctry_name;

-AfterUpdate event of Combo 1

Code:

Private Sub cboCountry_AfterUpdate()
    cboState_Province.Enabled = True
    cboState_Province.Value = Null
    cboState_Province.Requery
End Sub

-Row Source of Combo 2
SELECT DISTINCTROW dbo_sub_country.subcty_code, dbo_sub_country.subcty_name FROM dbo_sub_country WHERE (((dbo_sub_country.ctry_code)=forms![frmTerritory_Select]![cboCountry])) ORDER BY dbo_sub_country.subcty_name;

bxgti4x4 · Jan 7, 2004

Gus,

I've tried this but am having problems. I copied the code from the Knowledge Base Article and changed the names of the Combi Boxes and Fields accordingly. Nothing happened and the second combi box was not populated.

My table (Terminals) has three fields - ID, Port and Terminal. Combi Box 1 has the name "Disport" and Combi Box 2 has the name "Dispterm". My code therefore becomes:

Code:

Private Sub Disport_AfterUpdate()
Me.Dispterm.RowSource = &quot;SELECT Terminal FROM&quot; & _
   &quot; Terminals WHERE Port = &quot; & Me.Disport & _
   &quot; ORDER BY Terminal&quot;
Me.Dispterm = Me.Dispterm.ItemData(0)

This appears to be the same as the example. Any ideas?

John

gusbrunston · Jan 7, 2004

[tt]
Hi Spenny:

You're right--the row source of the combo box 2 is set in the AfterUpdate event of combo box 1. When that's done, in the property sheet of combo box 2, the Row Source can be left blank.

Here's Microsoft's sample code for the AfterUpdate event of combo box 1, where box 1 is named "Categories" and box 2 is named "Products":

Me.Products.RowSource = "SELECT ProductName FROM" & _
" Products WHERE CategoryID = " & Me.Categories & _
" ORDER BY ProductName"
Me.Products = Me.Products.ItemData(0)

[glasses]

[tt]Gus Brunston - Access2000(DAO)
Skill level based on 1-10: 7
Webmaster: www.rentdex.com[/tt]

MakeItSo · Jan 7, 2004

Hi John!

If your port/disport is a text variable and not a number, you must replace
WHERE Port = " & Me.Disport & _
" ORDER BY Terminal"

with
WHERE Port = '" & Me.Disport & _
"' ORDER BY Terminal"

(Added single quotes around Me.Disport)

Greetings,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

gusbrunston · Jan 7, 2004

[tt]
Hi:

If you're still having problems, in the AfterUpdate event of "Disport" try changing "Terminals" to "Dispterm" as follows:

Private Sub Disport_AfterUpdate()
Me.Dispterm.RowSource = "SELECT Terminal FROM" & _
" Dispterm WHERE Port = " & Me.Disport & _
" ORDER BY Terminal"
Me.Dispterm = Me.Dispterm.ItemData(0)

HTH

[glasses]

[tt]Gus Brunston - Access2000(DAO)
Skill level based on 1-10: 7
Webmaster: www.rentdex.com[/tt]

bxgti4x4 · Jan 7, 2004

Sorry Guys, I tried putting the single quote in, both before and after the double quotes - no different. I then tried changing Terminals to Dispterm, still no result.

John

gusbrunston · Jan 7, 2004

[tt]
We can do this!

Try:

Me.Dispterm.RowSource = "SELECT " & _
"Terminals.Terminal FROM Terminals" & _
" WHERE Terminals.Port = " & Me.Disport & _
" ORDER BY Terminal"
Me.Dispterm = Me.Dispterm.ItemData(0)

If this doesn't work, post the Row Source code from the property sheet of cbo DispPort, if any.

[glasses]

[tt]Gus Brunston - Access2000(DAO)
Skill level based on 1-10: 7
Webmaster: www.rentdex.com[/tt]

gusbrunston · Jan 7, 2004

[tt]
Maybe it would have to be:

Me.Dispterm.RowSource = "SELECT " & _
"Terminals.Terminal FROM Terminals" & _
" WHERE Terminals.Port = " & Me.Disport & _
" ORDER BY Terminals.Terminal"
Me.Dispterm = Me.Dispterm.ItemData(0)

[glasses]

[tt]Gus Brunston - Access2000(DAO)
Skill level based on 1-10: 7
Webmaster: www.rentdex.com[/tt]

gusbrunston · Jan 7, 2004

[tt]
Hi:

One last thought before I hit the sack:

Microsoft's example and all the times I've used this synchronization procedure, the combo boxes have been based on two tables. In the Microsoft example there is a table called "Categories" and a table called "Products".

For this to work for you, the first box should be based on a table called "Ports", and the second box should be based on a table called "Terminals".

HTH

Gus

[glasses]

[tt]Gus Brunston - Access2000(DAO)
Skill level based on 1-10: 7
Webmaster: www.rentdex.com[/tt]

MakeItSo · Jan 7, 2004

Gus, sorry, but this is simply wrong.

MakeItSo · Jan 7, 2004

John: Have you checked, if your cbo really has the nameDisport or is it just bound to this field?
Refer to the name the control has on the form. Perhaps that's the problem.

bxgti4x4 · Jan 7, 2004

Yes, I've checked the name under "Other Properties" and the cbo is definitely called "Disport". I have'nt tried Gus's last two ideas yet so I'll wait for your comment.

Best Regards
John

MakeItSo · Jan 7, 2004

Errrr - just seen something:
According to your first post to this thread, your code should read
[blue]
Private Sub Dispname_AfterUpdate()
Me.Termname.RowSource = "SELECT Terminal FROM" & _
" Dispterm WHERE Port = " & Me.Dispname & _
" ORDER BY Terminal"
Me.Termname = Me.Termname.ItemData(0)

[/blue]
Correct?
Andy

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

bxgti4x4 · Jan 7, 2004

Andreas,

Sorry, I think I have confused everyone. When I was trying out Gus's original idea, I changed some of the original table and cbo names to make it easier for myself. The original cbo "Dispname" became "Disport" and the second cbo became "Termname" became "Dispterm".

In following the Microsoft Template I simply substituted my names for those in the template. The only thing that I have noticed (see Gus's comment above) is that, after running the form and updating the "Disport" cbo, the row source in the "Dispterm" cbo is still blank. According to Gus, there should be something in there.

A thought has just occurred to me. I said at the very beginning that cbo "Disport" is based on a table called "Ports" whereas the cbo "Dispterm" is looking up from the table "Terminals. The Port names in both tables are identical, the field in "Terminals" being lookup based on "Ports". I don't think this has any bearing on the problem but you never know.

Best Regards
John

MakeItSo · Jan 7, 2004

Really strange:
Have you checked, if your "disport" is a non-numerical value, as I mentioned earlier and added single quotes to your sql statement:
Me.Dispterm.RowSource = "SELECT " & _
"Terminal FROM Terminals" & _
" WHERE Port = '" & Me.Disport & _
"' ORDER BY Terminal"
Me.Dispterm = Me.Dispterm.ItemData(0)

Plus: Check the term in italics: is the field name in the table Terminals still "Ports" or is it something different now?

gusbrunston · Jan 7, 2004

[tt]
Because I have to do something else later today, I have built a database to make this work.

There are two tables: tblPorts and tblTerminals

The one form is not bound to any table or query.

The AfterUpdate event in cboPorts is:

Private Sub cboPorts_AfterUpdate()
Me.cboTerminals.RowSource = "SELECT TerminalsName " & _
"FROM tblTerminals " & _
"WHERE PortsID = " & Me.cboPorts & _
" ORDER BY TerminalsName"
Me.cboTerminals = Me.cboTerminals.ItemData(0)
End Sub

In the above SQL:
Names of tables begin with "tbl"
Names of combo boxes begin with "cbo"
Other names are field names.

The PK in tblPorts is PortID (long). PortID is also field in tblTerminals.

This works.

cboPorts displays ports 1, 2 and 3.
If I select port 3, then cboTerminals displays "Fifth Terminal" and "Sixth Terminal" because those are the only terminals with a PortID of "3".

I will email it to anyone who wants it, and who's security setup will allow them to open the attachment. For my good friends John and Makeitso, I can make it available to you through my account on "Xdrive".

And you never have to apologize for letting me know I'm wrong about something. I have learned more by being wrong than by being right; although I think being right is an easier and softer way!

Cheers,[/tt]

[glasses]

[tt]Gus Brunston - Access2000(DAO)
Skill level based on 1-10: 7
Webmaster: www.rentdex.com[/tt]

bxgti4x4 · Jan 8, 2004

Guys,
Thanks for the suggestions. I have made some progress in that the Row Source of my second cbo "Dispterm" is now being filled in after I update the first cbo "Disport".
The entry looks like this:

Code:

SELECTTerminal FROM TerminalsWHERE Port = BostonORDER BY Terminal

The "Boston" in the code is the port I have selected in cbo "Disport".

There are about 6 terminals against the port "Boston" in my Terminals table but the cbo is not being populated at all.

I have tried both the previous formats suggested by Gus and by Andreas but I'm afraid that the only format that gets me this far is the original Microsoft format:

Code:

Me.Dispterm.RowSource = &quot;SELECT&quot; & _
    &quot;Terminal FROM Terminals&quot; & _
    &quot;WHERE Port = &quot; & Me.Disport & _
    &quot;ORDER BY Terminal&quot;
    Me.Dispterm = Me.Dispterm.ItemData(0)[code/]

Best Regards
John

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!

Combo box values depending on another Combo Box Selection 2

Technical User

Programmer

Technical User

Technical User

Technical User

Programmer

Programmer

Programmer

Technical User

Programmer

Programmer

Programmer

Programmer

Programmer

Technical User

Programmer

Technical User

Programmer

Programmer

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor