Two combo box populate a third

[Greg553]

Hello, i got a little stuck on tis. I am trying to figure out how to populate a combo box based on the result of the choices of two other boxes.
Combo boxes are

Programs combo box
fields are program_ID, programs

Weeks combo box
Fields are weeks_ID, Weeks

the combination of these two choices populates the Reps combo box.

Ex choosing strength & week 1-3 results in a certain rep scheme .
there are 3 programs and 4 different weeks so you can have 12 different rep schemes.

i/m not sure ow to code the reps scheme box to get my result

thanks
greg

 

[Greg553]

Hello again. What i am trying to do is populate a combo box based on the choices of two other combobox's.
the two combo boxes are not based on each other, just the 3rd is based on the first two.
here is my code.
not sure if i'm even close , but gives me a error.

Private Sub Listbox_weeks_AfterUpdate()
Dim sListboxReps As String

sListboxReps = " Select [TblReps].[Rep_ID] " & _
" [TblReps].[Program_ID] " & _
" [TblReps].[Week_ID] " & _
"From TblReps " & _
"Where [Program_ID] = " & Me.Listbox_programs.Value And [Week_ID] = " & me.Listbox_weeks.Value" me.Listbox_weeks.Value"
Me.Listbox_reps.RowSource = sListboxReps
Me.Listbox_reps.Requery
End Sub

You choose a program in first combo which gives a value of 1-2 or 3 (which is program_ID)
than choose your week, which gives another value from 1 to 4 (week_ID)
based on these two values the 3rd combo box is populated with a single row from table reps.

I'm not sure how to use the AND statement or if this is right.

 

[PHV]

Where Program_ID=" & Me!Listbox_programs & " AND Week_ID=" & Me!Listbox_weeks

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Greg553]

Thanks PHV.
Still having a little trouble as 3rd listbox won't populate.
Do i have the event in the right place.
I get no errors now but does not fill in row source of 3rd listbox

Thanks
Greg

 

[Golom]

What PHV said and

sListboxReps = " Select [TblReps].[Rep_ID][red][b],[/b][/red] " & _
               " [TblReps].[Program_ID][red][b],[/b][/red] " & _

You need commas after your first two fields in the select.

Depending on the data types of [Program_ID] and [Week_ID], you may need quotes around the combo box field values like this
[blue]
"Where Program_ID='" & Me!Listbox_programs & "' AND Week_ID='" & Me!Listbox_weeks & "'"
[/blue]
with credit to PHV.