Combobox selection populates Listbox

[juniorz]

Hi all,

having trouble trying to get this too work. Im creating a DB where a form contains a dropdown combo box, staff will select their name and once selected a listbox below will populate with their classes.

currently i am using this:

Me!List2.RowSourceType = "Table/Query"
Me!List2.RowSource = "Select [AT Class select query].[Subject Name] From [SNAP schoosub] Where [AT stafflist].[Staff ID] = " & Me![Combo0]

The code is placed in the after update of the combobox, where list2 is the listbox. "AT class select query" is a query which when ran prompts a user for there name and will populate accordingly. "SNAP schoosub" is the table that contains "subject name".

any help appreciated.

 

[pbaldy]

That's invalid SQL, since the query being referred to in the SELECT clause is not included in the FROM clause. You can create a query that uses the combo as a criteria and make that the listbox rowsource, and requery it from the combo after update event. Alternatively, fix that SQL, and it's even worse as I just noticed the table in the WHERE clause is also not in the FROM clause.

 

[juniorz]

ok, so i have modified it to this to try and fix the sql.

Me!List2.RowSourceType = "Table/Query"
Me!List2.RowSource = "Select [AT Class select query].[Subject Name] From [AT Class select query].[Subject Name] Where [AT Class select query].[Staff ID] = " & Me![Combo0]

When I select a name, it now does not prompt any errors or ask for any input, however it does not populate the listbox.

 

[pbaldy]

Is the bound column of the combo the correct value?

 

[juniorz]

both boxes are set to 1, do I need to set anything up on the listbox? i.e control source or row source in order for it to read from the combo?

 

[pbaldy]

Is the first column of the row source the [Staff ID] expected by the listbox? Based on that SQL, the listbox should have a column count of 1 and a column width like 2" or whatever (not 0"; 2", a hidden first column).

 

[juniorz]

i think i see my issue,
I need the listbox to be populated by just the subject name, where the combo is saying it wants the list to be the staff ID.

I want the combo which contains the staff ID to populate the listbox with only the subject name.

Im just not sure how to write that using the sql.

 

[CJ_London]

your sql in post 3 says

...From [AT Class select query].[Subject Name] Where [AT Class select query].[Staff ID] = " & Me![Combo0]

you need to remove the bit in red

 

[juniorz]

your sql in post 3 says

...From [AT Class select query].[Subject Name] Where [AT Class select query].[Staff ID] = " & Me![Combo0]

you need to remove the bit in red

removed it however when I try and run it, i receive a box asking to enter parameter queries.

 

[pbaldy]

Geez, I didn't notice that. Did you try:


Me!List2.RowSource = "Select [AT Class select query].[Subject Name] From [AT Class select query] Where [AT Class select query].[Staff ID] = " & Me![Combo0]

 

[juniorz]

interesting development, so I have changed the code to match Paul. Once i select a name from the drop down menu, a box appears asking for the staff id code, i type in the code, and it asks me for a code again, typed it in again and received another box asking for another input however this time it has a description the staff code, i type the code a third time and then the list box populates.

any ideas about that?

 

[pbaldy]

The parameter prompts are Access telling you it can't find something, in this case 3 things. Can you attach the db here?

 

[CJ_London]

my guess would be it is in the query you are querying

 

[juniorz]

unfortunately I cant upload database due to confidentiality of people in it.

i have taken these screencaps however.

e1: once i choose a selection from the dropdown box this is what appears twice, I need to put in that staff code twice to populate the list.

e2: a snap of the query the sql is trying to run from.

 

[CJ_London]

so what does AGND mean to you? it's not in the AT Class Select query, nor the query referencing it

 

[juniorz]

AGND would refer to a staff code or in this case a selection from the combobox

 

[CJ_London]

But since it is treating it as a parameter that means you have a control or field called AGND - you entered AGNJ in the combo box

 

[juniorz]

right, could it possibly be the problem is where the combobox is getting its information from? This is going through a table.

 

[CJ_London]

doesn't look like it

 

[juniorz]

Thanks for your help with this,

i solved the issue by going to what pbaldy originally said and creating the combo as criteria and modifying the listbox row source.

Seems to have worked quite well.