Combobox values based on another combobox selection

[Twinnie]

testk1.zip



Hello All,

I am a newbie to Access 2010 however i don't know where I goofed because I seem to have done this before.

I am having issues with my combo box populating values based on the selection from another combo box.

Database : contains 2 tables: Staff_List, Level_Type and a form called frm_Test

The first combobox cboLevel gets its value from the Level_Type table with the select statement SELECT [Level_Type].[ID], [Level_Type].[Level_Type] FROM level_Type;

The values to be displayed on the second combobox cbo_Name is expected to be dependent on the Level selection made from the first combobox i.e if Level 1 is selected in cboLevel then only Names of people in Level 1 will be displayed in second combobox.

The select statement I put there is SELECT Staff_List.ID, Staff_List.Staff_Name
FROM Staff_List
WHERE ((([Staff_List]![Level])=[Forms]![frm_test]![cboLevel]));

This is not displaying anything in the second combobox after I tried it in the Form view, just empty.

I have attached the sample database called testk1.zip below.

Kindly assist.

testk1.zip

[ranman256]

sorry, the cboLevel combo data has "bound col" = 1, you want it to be 2 the LEVEL ,not the index.
Your query has 2 columns in it, you want 2.

[lfpm062010]

The combo box "Level". Need to put "Level_Type" first then "ID". Make sure you change the Column Width setting from 0";1" to 1";0".

To make sure, I also put the

Me![CboName].Requery

in the "AfterUpdate" event of combo box "Level".

[ranman256]

The [Level_Type].[ID] is useless in this table. It should have been a single column keyed on LEVEL.
(then the bound_column = 1 again)

[Twinnie]

Thank you for your help ranman256 and ifpm06201.

I have done as you instructed but the cboName is not populating yet.

I used macro on the AfterUpdate property of the cboLevel combo: Requery Control Name cbo_Name as suggested.

On the cbo_Level "row source property"- The combo box "Level". Need to put "Level_Type" first then "ID". Make sure you change the Column Width setting from 0";1" to 1";0". so I have modified the Select statement on cboLevel: SELECT level_Type.Level_Type, level_Type.ID FROM level_Type;

I also changed the Column Width setting from 0";1" to 1";0".

Please see attached document

I am using Macros because this is a web database and it does not support VBA.

[lfpm062010]

This is what I have changed.

1) Your query to combo name is wrong.


testk2.accdb

[Twinnie]

Hello ifpm062010,

I am certain this is a novice question I'm asking right now but I sincerely crave your indulgence to explain this please.

I have checked the syntax of the cboname query that you corrected and the one I used. I can seem to spot the exact error. I feel I might have probably tampered with the property sheet properties. Kindly elaborate on this. If you also have any material or link where I can read this up, I will appreciate it.

Thank you.

[lfpm062010]

This is what you have in your cbo_Name Row Source property
SELECT Staff_List.ID, Staff_List.Staff_Name FROM Staff_List WHERE ((([Staff_List]![Level])=" & [Forms]![frm_test]![cboLevel] & "));

I changed to
SELECT Staff_List.ID, Staff_List.Staff_Name FROM Staff_List WHERE (((Staff_List.Level)=[Forms]![frm_test]![cboLevel]));

In your case, your criteria become a text string of " & [Forms]![frm_test]![cboLevel] & " (it is trying to find exact match "[Forms]![frm_test]![cboLevel]" and not the value of [Forms]![frm_test]![cboLevel]). Also the "!" should not cause the problem. But the "." is preferred.

I hope this explain your confusion.

[Twinnie]

Thank you so much. I am grateful