combo box selection to set contents of listbox 1

[pduncan]

I have a combo box named cboRCNo which is populated with a list of names. This is it's row source:

SELECT tblController.RCNo, tblController.RCName
FROM tblController
ORDER BY tblController.RCName;


I have a table of events (tblEvent) that has all of the data associated with an event - for this example I am concerned with these columns:
Date, ActivityID, LocationID and RCNo.
Of the columns listed above, all but Date is actually a lookup value to another table.

on my form, I have a combo box (see cboRCNo above) that I want to query tblEvent, and populate a lisdtbox (lstEvents) with the Date, Activity, and Location of all of the events in tblEvents that are associated with the Name (RCNo) selected in the combo box.

To do this I placed this in the After Update event of cboRCNo:
lstEvents.RowSource = "SELECT tblEvent.EventDate, tblEvent.ActivityID, " _
& "tblEvent.LocationID FROM tblEvent WHERE RCNo=" & cboRCNo

It worked - sorta - except that instead of seeing the "names" of the activities and locations listed in tblEvent, I get the number instead - even though the table (tblEvent) appears to save the name of the looked up value.

Can someone please tell me how to get the names of the looked up values to populate the listbox instead of the numbers in the linked table?


Thanks, PDUNCAN
Memphis, TN - USA
When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.

 

[RoyVidar]

Include the linked table in the query. It seems you are "suffering" from the conditions that made some rather good developers create this page The Evils of Lookup Fields in Tables. You are getting the number form this table, because that's what's stored in the field.

The query could then look something like this:

[tt] "SELECT tblEvent.EventDate, tblActivity.Description, tblEvent.LocationID " & _
"FROM tblEvent inner join tblActivity on " & _
"tblevent.activityid = tblactivity.activityid " & _
"WHERE RCNo=" & cboRCNo[/tt]

Typed not tested

Roy-Vidar

 

[pduncan]

Thanks - that worked for the Activity - I see how you joined the tblActivity - but the Location is still a number. It is a lookup to tblLocation - can you show me how to use two inner joins? I tried for several hours this morning to figure it out myself with no luck.

Also, given the link you sent, should I consider re-writing this application without the lookup tables?

Thanks, PDUNCAN
Memphis, TN - USA
When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.

 

[RoyVidar]

You should keep the lookup tables, but drop the table level lookups. Set relationship the "proper way", and use the combo wizard on the forms to create the lookups.

Just join the other table too, I'm a bit rusty on writing multiple joins, but perhaps something like this?

[tt]....from tblActivity inner join (tblLocation inner join " & _
"tblEvent on tblLocation.LocationID = tblEvent.LocationID) " & _
"on tblactivity.activityid = tblevent.activityid " & _
"WHERE RCNo=" & cboRCNo[/tt]

or just use the QBE, then copy paste, and insert the relevant spaces, quotes and...

Roy-Vidar

 

[pduncan]

thanks for the help - have a star - I think others will probably make this same mistake

Thanks, PDUNCAN
Memphis, TN - USA
When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.

 

[pduncan]

I changed the tblEvent from lookups to "regular" - now instead of the actual "words" being stored, it is the ID in the other tables. Unfortunately - the dual inner joins are not working. Even using the query editor - it doesn't error out, it is just blank. Is there a workaround? If two inner joins wont work, how best to do this?

Thanks, PDUNCAN
Memphis, TN - USA
When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.

 

[RoyVidar]

I don't know, try playing with the query builder, start with just one table (event), then add another one, make that work, then add the last. Add the criterion (cboRCNo) last. Or, you aren't by any chance experimenting with this now without having the form open and without a value in the combo?

Should you have orphan records, which seems a bit odd, given the previous setup, you may have to play with outer joins (right click the relationship in the query design, chose properties), in SQL it could perhaps look something like this:

[tt]....from tblActivity right join (tblLocation right join " & _
"tblEvent on tblLocation.LocationID = tblEvent.LocationID) " & _
"on tblactivity.activityid = tblevent.activityid " & _
"WHERE RCNo=" & cboRCNo[/tt]

Roy-Vidar