Second combobox value populated from one of 2 tables based on first combobox value

[jbeets]

Hello!

I have a form with 2 comboboxs . cbo_JobType has 2 options "Hourly" or "PieceWork".
I have two tables "tbl_HourlyJobs" and "tbl_PieceWork". The hourly jobs have individual pay rates based on the employee. The Piecework jobs are all paid the same but there are about 40 of them.

What I am hoping to do is, based on what option is selected in cbo_Jobtype I would like the combobox to populate from the correct table.



I think I need to use VBA and possibly a Select Case statement but I am not sure how to write it.

Any suggestions on how I can make this work?

Thank you so much for any help you can provide!

[June7]

This is known as cascading or dependent combobox (or listbox) and is a very common topic. Yes, VBA will be involved.

[ssanfu]

Just curious - why do you have 2 tables? Are the table structures that different?

[jbeets]

Just curious - why do you have 2 tables? Are the table structures that different?

Well, I didn't know how else to do it basically. The first table lists the hourly job rates for each employee. There are 13 different jobs and each employee may have a different payrate for each job. The second table has the jobs that are paid by the pieces completed. Each employee makes the same rate for these jobs but there are about 40 different jobs and 40 employees.

Also, I understand dependent combo boxes but I have never tried to base it off of two tables feeding into the same drop down. Since I couldn't figure that out I just did two different comboboxs and only the one that relates to the main combo box will populate.

[June7]

VBA would just change the combobox RowSource property.

[ssanfu]

Without knowing the table structures, I would have 1 table with an additional field for JobType - "Hourly" or "PieceWork".
Then it is easier to select the fields you want to have in combo box 2.


As far as VBA, there are several options.
You could have a couple of constants that have the SQL for each table and change the row source in the combo box

If Me.cboJobType = "Hourly" Then
  sSQL = "Select Field1, Field2 From tbl_HourlyJobs"
Else
  sSQL = "Select Field1, Field2 From tbl_PieceWork"
End If

Me.cboBox2.SQL = sSQL

Case to post your table structures?
Or you could build the SQL string using VBA.

[jbeets]

Thank you so much, this was much easier than I was making it out in my head. Thank you for walking me through how this would work!

[ssanfu]

Happy to help....