Combo box values depending on another Combo Box Selection 2

[bxgti4x4]

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:

 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

 

[bxgti4x4]

I've been investigating a bit further and have tried deleting the where statement. If I do this, the "Dispterm" cbo is populated with all the terminals listed in the terminals table. The problem seems to be with the WHERE statement.

I tried writing a query, using the criteria of one port name (Boston). I ran this and the terminals associated with Boston were listed. I then looked at the query in SQL view to see how the WHERE statement was built. This looks exactly the same as the WHERE statement in the code above.

Its all very odd.

 

[MakeItSo]

Have you tried populating the box and then running the query you've built?
Compare the port entries in table terminals with those in Ports. Do so by creating a join between the two tables on [Terminal]. Does a simple
SELECT Terminals.Terminals
FROM Terminals INNER JOIN Ports ON Terminals.Terminals = Ports.Terminals;
produce any results? If not, then Terminals does not contain the proper Terminal names.

 

[bxgti4x4]

The original query I ran linked the two tables so that I could make sure that the port names in each were identical. I actually tried modifying the code so that the selection was from the terminals query rather than the terminals table and from the query selected the port name from the ports table. The box would still not populate.

 

[MakeItSo]

Hhhmmmmm

I'd say, that's one mean combo, you're having. Ever thought about deleting both combo's from the form and just re-creating them?

 

[bxgti4x4]

I tried that, in effect, by creating a new form. Is there any point in my creating a new database and sending it to you? You might see something that I have missed.

 

[MakeItSo]

In fact that could be the best solution.
But watch out: Never import/export forms containing vb-code. That can corrupt your db! --> see several threads in this forum
If you have any code in your forms, I suggest you:
1) create a simple copy of your db
2) set the "Contains module" property of the form in the copied db to "No" and then export...

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

http://www.andygalambos.de

 

[bxgti4x4]

Thanks for the offer, it is much appreciate. I'll send it to you shortly.

 

[MakeItSo]

For all readers' info:
a) There were spaces missing. The SQL during runtime read "SELECTTerminal..." instead of "SELECT Terminal..."
b) added single quotes
changed "...WHERE Port=xyz..."
to "...WHERE Port='xyz'..."

Combos up and running... ;-)

 

[bxgti4x4]

That's fixed it. Thanks very much to everyone who has tried to help with this problem. It is usually the silly mistakes that are the cause of the problem and as I am still only a novice at SQL,I often miss the little things that are important. Another pair of eyes are invaluable at times like this.

Best Regards
John

 

[livvie]

Hi Guys
Im having the same problem with this. I have tried all the changes you suggested (I think) but I still cant get it to work. The one difference I have is that my first Combo box cmbCust has 2 fields ID and Name and my second cmbCTD2 has 3 CTD Number CID (sames as ID in first combo) and PartNO. Here is the code:
Private Sub cmbCust_AfterUpdate()

Me.cmbCTD2.RowSource = "SELECT CTD Number FROM" & _
" tblDrawings WHERE tblDrawings.cid = '" & Me.cmbCust & _
"' ORDER BY CTD Number"
Me.cmbCTD2 = Me.cmbCTD2.ItemData(0)
End Sub

Any ideas ?

 

[gusbrunston]

[tt]
Dear Livvie:

I suggest you start a new thread. By asking for help here, you're may be limiting your possible responses. Often, when I see a thread that has many responses already, I won't take the time to look at it. A new thread would get the attention of people now active and with the time to help.

Besides, I spent so much time on this for John, that I can't imagine I'd have anything new to offer on the subject. Good luck.

Cheers,[/tt]

[tt]
Gus Brunston - Access2000/2002(DAO)[/tt]