Combobox values based on selection on another combobox

[Twinnie]

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.accdb

Kindly assist.

 

[Cotty42]

Looking at the sample file you attached you are linking to the ID field in your Level_Type table but usingthe text in the Staff_List table.

Try adding the following code to the cboLevel after Update event. This will refresh the Staff_List control each time the cboLevel control is updated. You also need to remove ID from the Select criteria for the cboLevel control and limit the control to a single column.

Private Sub cboLevel_AfterUpdate()
strSelectText = "SELECT Staff_List.ID, Staff_List.Staff_Name "
strFromText = "FROM Staff_List "
strWhereText = "WHERE ((([Staff_List]![Level])= '" & Me.cboLevel & "'));"

strSQLText = strSelectText & strFromText & strWhereText
Me.cbo_Name.RowSource = strSQLText

End Sub

Hope this helps

Dave

 

[Twinnie]

Hello Cotty42,

Thank you for your response.

Can you please assist with how I can do this using Macros and not VBA.

This is supposed to be a web database that will be published via Sharepoint and VBA is not compatible with web database.

Thank you.

 

[Cotty42]

Hi Twinnie

The issue here is that Macro's are no where near as functional as VBA code and, whilst it is possible to run SQL code in a Macro I am not sure that you can use a Macro to amend a form control attribute (i.e. Rowsource), as the VBA code does.

Dave