3 Syncronized Combo Boxes

[ToddNYC]

Hello -- I have been trying for several days to get this 3 combo boxes to synchronize but haven't been able to figure it out yet. There are many posts about this, but I think I'm making it worse by combining the ideas from various posts all together! I was able to get the first two boxes to work, but yesterday I revised the order of the table data based on an article I red and now I can't even get any to work.



Any advice/suggestions would be appreciated!

I've attached a shortened version of the Access file.

Thanks, Todd

[ssanfu]

I can't open your example until tonight, but attached is an Access 2000 example of how I set up cascading combo boxes. The left button uses combo boxes and the right button uses list boxes.

Look at the row source and the after update event code for each combo box.

[ToddNYC]

Thanks you ssanfu for the reply -- I'll take a look through your example to see if it sets me straight!

[alansidman]

read this thread and look at the examples presented. Two options. Choose which one is right for you.

http://www.access-programmers.co.uk/...d.php?t=219997

[ssanfu]

So I looked at your mdb and made a few changes. Got rid of the "Look Up FIELDs".
Changed the PK field names - all three tables with "ID" is confusing.You had the bound field set to zero - bound field starts a one. I use queries for all row sources to be able to sort the options.
And I don't like to keep changing the row source query - so I set the criteria to reference the combo box...
Changes the event code......
Also set referential integrity in the relationship window.

[ToddNYC]

Steve, thank you so much for this, I am still working on trying to integrate the work you've done into my database. It all works great until I mix it into my larger structure. I'm going to keep on trying to figure it out though! Thanks again for you time and help.

Todd

[ToddNYC]

Hi Steve, I've been trying to figure this out for a few days now and can't do it. Would you mind taking another look? I've included the entire database.

What I'm trying to do is take the example you provided me and integrate it into the rest of my db. And I just can't figure it out!

My end goal is to be able to input all venue data via the "Venue Form" (comboboxes seem to be working for city and state) including the location data you built. But I can't get what you've provided saved to my table while also keeping its reference intact. The location1, 2 and 3 tables will have a button under the names on the Venue Form opening up a form of their as new locations will always be getting added.

Thank you and sorry I can't figure this out on my own (or by using my new "Access 2010 The Missing Manual" book!)venues_example.zip

[ssanfu]

I'll look at it when I get home tonight...

[ToddNYC]

Thank you - I'm still playing around with it too.

[ToddNYC]

I think I made a bit of progress, but something is still not firing right. I've attached the most recent update. THanks again.

venues_example (2).zip

[ssanfu]

I found a field misspelled and a couple of wrong field types. See if this is what you want...

[ssanfu]

I looked at your example venues_example (2).zip - took me a while to figure out what you did.

Using look up FIELDS (different from look up tables) and multi-valued fields is not considered to be good programming practice.

(BTW - The field "Location3_ID" in the table is misspelled >> "Lcoation3_ID")

[ToddNYC]

Sorry I made it so confusing! Thanks for taking a look at my design, programming -- and my spelling errors... I am going to go through your adjustments this morning to see how you got it to work. I VERY MUCH appreciate you taking the tame to help me with this. Thank you!!

[ToddNYC]

I've been working with this all day and have your design integrated pretty well, thanks again. One thing I can't figure out is that some of the Location 3 data (SubSubLocation) doesn't remain in Table1. It seems that if I update a record with Location 3 data, it deletes the information from the previous one. A similar thing occurs on sample you passed along too, so I am having a hard time troubleshooting. Here is the VBA, does anything jump out at you? (It's a bit too complicated for me to breakdown!!)

Option Compare Text
Option Explicit

'Private Sub cboLocation_AfterUpdate()
'    Me.cboSubLocation.Value = 1
'    Me.cboSubSubLocation.Value = 1
'    Me.cboSubSubLocation.Requery
'    Me.cboSubLocation.Requery
'End Sub

Private Sub cboLocation1_AfterUpdate()
    Me.cboLocation2 = 1
    Me.cboLcoation3 = 1
    Me.cboLocation2.Requery
    Me.cboLcoation3.Requery
'    Me.cboSubLocation.Value = 1
'    Me.cboSubSubLocation.Value = 1
End Sub

'Private Sub cboSubLocation_AfterUpdate()
'    Me.cboSubLocation.Value = 1
'    Me.cboSubSubLocation.Requery
'End Sub


Private Sub cboLocation2_AfterUpdate()
    Me.cboLcoation3 = 1
    Me.cboLcoation3.Requery

End Sub

And if you've seen enough of this project and want to just move on, I completely understand!!

[ssanfu]

OK, try this one. I must have been asleep... talk about spelling errors