This maybe a beginner question however I can’t seem to link 2 columns from 1 table into 2 sub form combo drop down lists. For example I have a table (Tests) with 2 columns called TestName (text) and ModuleCode (text, i.e. BL01). In the subform (Marks) I have 2 combo boxes for both headings, now I want to select the Testname from the drop down combo box and automatically it should populate the applicable ModuleCode associated to the test and vise versa, is this possible and what would be the best approach to accomplish this?

Thank you in advance for any suggestions.

Hi,

There may be various ways to do it. But I find my way is easiest.

I created a db for you for the raised topic but unfortunately I don't see any attachment facility here. Anyhow.

Considering your 2 combo boxes named as CboName & CboModule those are placed on the form, do the following :

1. Delete the rowsource (if u created combo based on query / table)of your second combo box (CboModule) and AfterUpdate event of first combo (i.e. CboName) write down following code lines.

Dim Qst As String
Qst = "SELECT T_Tests.TestName,T_Tests.ModuleCode FROM T_Tests Where T_Tests.TestName='" & Me.CboName & "'"
Me.CboModule.RowSource = Qst
Me.CboModule.SetFocus

2. OnGotFocus event of 2nd combo (CboModule) write following line:

Me.CboModule.DropDown

It works smoothly.

With kind regards,

Ashfaque

N. B. : To make it more productive, do not forget to put DISTINCT clause in your first combo query.

aRtware,

I sent you a database file that you requested for.

Please check your email and let me know if it meets your requirement.

Regards,
Ashfaque

Thank you for your example it helps me understand the breakdown by any chance is the same result possible just using a select sql query within the 2 combo fields?

MAY BE...

If I understand your question correctly, its possible to set up a relationship between the 2 tables in your databasehe relationship screen. Then use one fo the 2 drop downs to populate the other data.

For instance for an inventory system user types in an item id number and the form fills in' the item description package size etc.

I dont know if that answers your question or not.

Thank you for all the suggestions I figured out how to get a similar effect using a select statement here is what I did: On the Marks sub form row source of the TestName combo box I used the following,

SELECT training_tests.TestName FROM training_tests GROUP BY training_tests.TestName ORDER BY training_tests.TestName;

Then on the row source of the ModuleCode combo box I used this query,

SELECT training_tests.ModuleCode FROM training_tests WHERE (((training_tests.TestName)=Forms!frm_main_employe e!frm_marks_sub!combo_marks_test_name)) GROUP BY training_tests.ModuleCode ORDER BY training_tests.ModuleCode;

The result gives me the desired effect with the exception that if a user wants to make an adjustment/correction in picking the TestName the appropriate ModuleCode no longer populates with the corrected TestName and therefore the user will have to exit the form and reselect the TestName so that the ModuleCode field would match. If anyone has any suggestions on this please let me know.

Glad to know that you achieved what you wanted...

Ashfaque