I created 2 Dynamic combo boxes. One displays list the other doesn't any reasons why?

[wedd]

Hi everyone, I''m creating combo boxes which when the user clicks on the first combo box and select an optio from the list all the items that relate to it can be selected in the second combo box with options that relate to it. My first combo box called Location name shows the list Maitland Park Centre, Queen's Crescent Centre and Fleetwood centre; however my second combo box venueName is not recognising and takes me back into the vb editor highlighting on the code: Me.CboLocationName in yellow, and doesn't display the options that relate...I'm not sure why this is occuring as it seeems correct code.

My tables are as follow:

Loc which fireld are locationID, LocationName, LocationDescription and the second table which is called venuenames has the fields venueID, Venue names and locations. The code I am using to run on both combo boxes to display in relation to each other is written below. Any reason why this is happening? It could be something obvious which I haven't noticed; but as mentioned earlier the first combo box populated with a drop down list, but the second hasn't. Thanks for your contributions:friends:


My final outcome should be:

Combo box 1 selects Location name Maitland Park Sports center and in combo box 2 (venue names) there should be options to choose from which are Sports, Classroom.

If the user selects Combox1 again (Locationname) and selects Queen Crescent Centre the second combo box should display Main Hall 1, Main Hall 2, Multimedia suite, IT-Drop In, and Gym to choose from the list.

And thirdly, if the user selects Fleet Community centre from combo box 1(Locationname) when he selects from combo box 2 (venuename) the options meeting hall and side room should be displayed for the user to choose from. I've listed my code below:



Private Sub CboLocationName_AfterUpdate()
Me.cboVenue.RowSource = "SELECT VenueName FROM" & _
" VenueName WHERE LocationID = " & Me.CboLocationName & _
" ORDER BY VenueName "
Me.CboVenueName = Me.CboVenueName.ItemData(0)
End Sub

[SoftwareMatt]

What is the error?

My guess would be that the datatype is a string so it needs quotes as follows:

Me.cboVenue.RowSource = "SELECT VenueName FROM" & _
" VenueName WHERE LocationID = '" & Me.CboLocationName & _
"' ORDER BY VenueName "

[hansup]

In your description, you mentioned 2 combo boxes. But your AfterUpdate procedure seems to suggest you have 3 combos: CboLocationName; cboVenue; and CboVenueName.

So are there actually 3 combos? Or are cboVenue and CboVenueName actually the same combo, but one is spelled wrong?

[hansup]

SELECT VenueName
FROM VenueName
ORDER BY VenueName;

Do not use the same name for a field and table.

[L@ja]

Hello,
I suggest the following:
tables has prefix eg.:
tc_.... -config table
t_.... - table
f_.... - form

etc...
your life will be easy.
regards

[wedd]

well spotted, Hansup! My two combo boxes are suppose to be cboLocation and cboVenue...the code should be attached to the cboLocation box and cboVenue should display the names that correlate with the Locations...

[wedd]

Thanks L@ja and Hansup that could be the reason for my confusion. My 2 tables's fields have similar names that could be why the code isn't able to run...

[hansup]

Did you set Option Explicit in the Declarations section of your form's module as I suggested in your other thread?

Don't code without it!!!!!

[wedd]

I did have it set on Opion Explicit..I'll probably have to rename my tables and fields again...

[wedd]

Hi, Hansup I think that's my issue is the naming convention and creating logical tables... one of my fields within the locations table are Queen's crescent community centre which offer (IT-Drop In, Gym, Multimedia Suite which will be placed in the venue's table). Also within the Locations table is Fleet community centre of which (Gym, conference room) are within the venue's table. And finally within the locations table is Maitland Sports centre which offer (Gym, side room, meeting hall which are placed within again the venue's table)..this is where I get confused when writing the code...I've created 2 tables locations and venues...and scrathing my head how to code it for 2 separate combo boxes...

[hansup]

Make a copy of your database. Discard anything and everything which doesn't directly apply to the problem at hand. Run compact & repair. Then create a Zip of the database and post it here as an attachment.

If your database is in A2010 ACCDB format, I can't help you. But someone else may be able to.

[wedd]

Hansup, I've created one table...but would like to create 2 combo boxes....as mentioned in my previous thread thats what I'd like to achieve...let me know if you have any questions...its an access 2007 database. Thanks!:friends:

[hansup]

Where is the form you're having trouble with? All I see in that database is a single table.

[wedd]

Hi, Hansup. I've enclosed a copy of the form..if you scroll down you can view the 2 combo boxes...:friends:

[hansup]

The database you posted includes 2 forms. "Splash Screen" doesn't include any combos. When I tried to open the form "Rates of Community Centres Facilities", I got an error that the record source "Rates of Community Centres Facilities" doesn't exist.

[wedd]

yes, it's a bit difficult trying to copy a certain certain part of my database on here...if you want can you private send me your email address and 'll send a copy of the database via my email address?

[wedd]

Hansup, I found a solution..thanks!

[hansup]

Hansup, I found a solution..thanks!Congratulations, wedd! Please post your solution to help out the next person with a similar problem.

Thanks,
Hans

[wedd]

Here's a sampled example with instructions.