Multiple Choose Boxes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can someone walk me through what I would need to do to have 3 combo boxes on
one form that dictates what data is displayed on a subform within.

Here is my thought....3 combo boxes..(each one is pulling data from it's own
data table.) User chooses from each of the 3 and based on that
criteria...that data is shown in the subform. I have it at the point where
the subform will populate based on combo1...but resets all the data when I
choose from combo2.

This may be complicated but if someone could get me started on a basic
outline of what I need to do...I can then research further....Thanks in
advance!
 
What you need to do is incorporate all 3 combo boxes into the RecordSource
each time any one is changed. Something like this:

Private Sub cbo1_AfterUpdate()
LoadSubform
End Sub

Private Sub cbo2_AfterUpdate()
LoadSubform
End Sub

Private Sub cbo3_AfterUpdate()
LoadSubform
End Sub

Private Sub LoadSubform()
Dim strSQL As String

strSQL = ""

If Not IsNull(cbo1) Then
If strSQL = "" Then
strSQL = "SELECT ... FROM ... WHERE "
End If
strSQL = strSQL & "[Field1]=" & cbo1
End If
If Not IsNull(cbo2) Then
If strSQL = "" Then
strSQL = "SELECT ... FROM ... WHERE "
Else
strSQL = " AND "
End If
strSQL = strSQL & "[Field2]=" & cbo2
End If
If Not IsNull(cbo3) Then
If strSQL = "" Then
strSQL = "SELECT ... FROM ... WHERE "
Else
strSQL = strSQL & " AND "
End If
strSQL = strSQL & "[Field3]=" & cbo3
End If

Me.subform.Form.RecordSource = strSQL

End Sub

Hopefully this will give you some ideas.

Carl Rapson
 
Thanks for the help! Can you tell me where I would put this code? Thanks
again.

Carl Rapson said:
What you need to do is incorporate all 3 combo boxes into the RecordSource
each time any one is changed. Something like this:

Private Sub cbo1_AfterUpdate()
LoadSubform
End Sub

Private Sub cbo2_AfterUpdate()
LoadSubform
End Sub

Private Sub cbo3_AfterUpdate()
LoadSubform
End Sub

Private Sub LoadSubform()
Dim strSQL As String

strSQL = ""

If Not IsNull(cbo1) Then
If strSQL = "" Then
strSQL = "SELECT ... FROM ... WHERE "
End If
strSQL = strSQL & "[Field1]=" & cbo1
End If
If Not IsNull(cbo2) Then
If strSQL = "" Then
strSQL = "SELECT ... FROM ... WHERE "
Else
strSQL = " AND "
End If
strSQL = strSQL & "[Field2]=" & cbo2
End If
If Not IsNull(cbo3) Then
If strSQL = "" Then
strSQL = "SELECT ... FROM ... WHERE "
Else
strSQL = strSQL & " AND "
End If
strSQL = strSQL & "[Field3]=" & cbo3
End If

Me.subform.Form.RecordSource = strSQL

End Sub

Hopefully this will give you some ideas.

Carl Rapson

deeds said:
Can someone walk me through what I would need to do to have 3 combo boxes
on
one form that dictates what data is displayed on a subform within.

Here is my thought....3 combo boxes..(each one is pulling data from it's
own
data table.) User chooses from each of the 3 and based on that
criteria...that data is shown in the subform. I have it at the point
where
the subform will populate based on combo1...but resets all the data when I
choose from combo2.

This may be complicated but if someone could get me started on a basic
outline of what I need to do...I can then research further....Thanks in
advance!
Click to expand...
Click to expand...
 
This code would go in the form's code module. Create the three AfterUpdate
procedures through the Properties window for each combo box. Type in the
code for the LoadSubform procedure yourself, at the end of the form's code
module (the module opens automatically when you create an event procedure
for a control).

Carl Rapson

deeds said:
Thanks for the help! Can you tell me where I would put this code? Thanks
again.

Carl Rapson said:
What you need to do is incorporate all 3 combo boxes into the
RecordSource
each time any one is changed. Something like this:

Private Sub cbo1_AfterUpdate()
LoadSubform
End Sub

Private Sub cbo2_AfterUpdate()
LoadSubform
End Sub

Private Sub cbo3_AfterUpdate()
LoadSubform
End Sub

Private Sub LoadSubform()
Dim strSQL As String

strSQL = ""

If Not IsNull(cbo1) Then
If strSQL = "" Then
strSQL = "SELECT ... FROM ... WHERE "
End If
strSQL = strSQL & "[Field1]=" & cbo1
End If
If Not IsNull(cbo2) Then
If strSQL = "" Then
strSQL = "SELECT ... FROM ... WHERE "
Else
strSQL = " AND "
End If
strSQL = strSQL & "[Field2]=" & cbo2
End If
If Not IsNull(cbo3) Then
If strSQL = "" Then
strSQL = "SELECT ... FROM ... WHERE "
Else
strSQL = strSQL & " AND "
End If
strSQL = strSQL & "[Field3]=" & cbo3
End If

Me.subform.Form.RecordSource = strSQL

End Sub

Hopefully this will give you some ideas.

Carl Rapson

deeds said:
Can someone walk me through what I would need to do to have 3 combo
boxes
on
one form that dictates what data is displayed on a subform within.

Here is my thought....3 combo boxes..(each one is pulling data from
it's
own
data table.) User chooses from each of the 3 and based on that
criteria...that data is shown in the subform. I have it at the point
where
the subform will populate based on combo1...but resets all the data
when I
choose from combo2.

This may be complicated but if someone could get me started on a basic
outline of what I need to do...I can then research further....Thanks in
advance!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top