Combo box with related tables error

[KingBroil]

Hi,

I have created some cascading comboboxes, using this tutorial:

//blogannath.blogspot.ca/2011/04/microsoft-access-tips-tricks-cascading.html

Worked like a charm.
Lately, I decided to change the structure of my database, starting from scratch again, using plenty of related tables instead of everything in a few tables. Now I used the same method for cascading combo boxes, but Access pops an error message when I try my form. Since I have ID numbers instead of the actual data in the source table of my comboboxes, I suspected that my problem could be there, but couldn't find a hint on how to fix it yet. See attachments for screenshots.

Code:
Private Sub cboGeoLocID_AfterUpdate()
On Error Resume Next

cboSiteLocID.RowSource = " SELECT DISTINCT SiteLocID " & _
"FROM UniqLoc " & _
"WHERE GeoLocID = '" & cboGeoLocID & "' " & _
"ORDER BY SiteLocID"
End Sub


Suggestions would be much appreciated.

KingBroil

 

[pbaldy]

Try taking out the single quotes (apostrophes) around the value. Those are only used for text values.

 

[KingBroil]

That was it, Access tend to be a little frustrating sometimes for a non-programmer.

Thanks again!

 

[pbaldy]

No problemo!

 

[KingBroil]

I still have an issue though, I cannot get the column with the names to display in my subsequent cascading combo boxes, only the ID. I've tried everything with the column count and width, which seem like a common mistake, but Access only shows blank columns beside the ID one. I then turned column head to Yes, but all columns are still blank but the ID one.

Code:
Private Sub cboGeoLocID_AfterUpdate()
On Error Resume Next

cboSiteLocID.RowSource = " SELECT DISTINCT SiteLocID " & _
"FROM UniqLoc " & _
"WHERE GeoLocID = " & cboGeoLocID & " " & _
"ORDER BY SiteLocID"
End Sub

Cheers,

KB

 

[pbaldy]

Well, the row source would have to include them to begin with:

cboSiteLocID.RowSource = " SELECT DISTINCT SiteLocID, OtherField, ThirdField " & _

 

[KingBroil]

Ok, since the names I want to display in my CB are in a table joined with the control source table, I was not able to solve this like you said. I read something about having a query as control source which would combine the info from the 2 tables. I also read about using the Lookup wizard to edit my lookups. I did included all the fields from the parent tables in the lookups property, but nothing shows up.

If the best way to do this is by some VBA programming, I'll do my homework and study on that a bit, any advice?

Your time is much appreciated.

KB

 

[RainLover]

Using the Properties Box you can click on Record Source and build your SQL (With two tables) using a query grid. But you don't need to save as a query.

Hope this helps.

BTW this is how I create all my SQL Statements. I never attempt to do it free hand. If I did I would get it wrong like so many other people do.

 

[KingBroil]

Hi all,

Thanks to you all, I finally managed to have my combo boxes has I wanted them.

I stuck with the initial VBA codeblock I had for the AfterUpdate event, and set the CB's row source to a query with an outer join between the tables I wanted the info from.

The tricky part for me was that I have GeoLocations divised in OnSiteLocations divised in Divisions also divised in Containers
All the underlined items are stored in separate tables. I made a UniqueLocation table to relate all those items in unique places: Ex.: Container #1 is stored in Division "A" at the storage site (OnSiteLocation) in GeoLocations Quebec City, which consist of a unique storage space when combined. However, it is possible to have Container #1 in two different storage site, and several storage sites in more than one GeoLocations.

It might not be the only or best way to do it, but it works!

VICTORY at last, thanks again!

By the way, how can I flag this thread as "Resolved" in the title? What is the best practice here?

KB

 

[RainLover]

By the way, how can I flag this thread as "Resolved" in the title? What is the best practice here?

You don't.

You have done the best thing possible by coming back with your solution and a nice thank you.

I think it is left open just incase someone else has something to add.