Displaying relevant values from another table in a listbox

[jmickela]

I have a form that functions as information display/update for a table. It also needs to display a list of relevant codes from another table. The codes in the second table have an ID that will match an ID field on the main table. However when I do this I either get every code in the code table, or nothing.

Here is the SQL that I'm trying to use:

SELECT table_code_lookup.code_text, table_codes.code_extra, table_codes.alum_id, table_codes.ID
FROM (table_code_lookup INNER JOIN table_codes ON table_code_lookup.code_id=table_codes.code_num) INNER JOIN table_alumni ON table_codes.alum_id=table_alumni.ID
WHERE (((table_codes.alum_id)=me.id));

However anything I can think of putting for me.id doesn't work. I've written a sub that will modify the query and insert the correct ID num, but I can't figure out how to get it to run when I change records with the record selector.

I have the nagging feeling that I'm missing something totally simple, but can't figure out what it is, where am I going wrong?

 

[Krij]

Hi!

Don't forget to requery the listbox: Me!lstSomething.Requery

Put the code in the AfterUpdate event of ID field or in the nextRecord button and the other buttons that may beffect a change in the ID field

I have a form that functions as information display/update for a table. It also needs to display a list of relevant codes from another table. The codes in the second table have an ID that will match an ID field on the main table. However when I do this I either get every code in the code table, or nothing.

Here is the SQL that I'm trying to use:

SELECT table_code_lookup.code_text, table_codes.code_extra, table_codes.alum_id, table_codes.ID
FROM (table_code_lookup INNER JOIN table_codes ON table_code_lookup.code_id=table_codes.code_num) INNER JOIN table_alumni ON table_codes.alum_id=table_alumni.ID
WHERE (((table_codes.alum_id)=me.id));

However anything I can think of putting for me.id doesn't work. I've written a sub that will modify the query and insert the correct ID num, but I can't figure out how to get it to run when I change records with the record selector.

I have the nagging feeling that I'm missing something totally simple, but can't figure out what it is, where am I going wrong?

 

[jmickela]

Hi!

Don't forget to requery the listbox: Me!lstSomething.Requery

Put the code in the AfterUpdate event of ID field or in the nextRecord button and the other buttons that may beffect a change in the ID field

It turns out that this was the problem all along. I just wasn't requerying the box. Thanks a lot.