Multiple combo boxes controlling contents of subform?

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

Guest

I have a form that is intended to allow users to find specific records. On it
are multiple combo boxes to allow them to search via different means -
Employee, Team Lead, Case Number, Date Case Opened, etc. The form has a
subform, which displays the relevant fields of the relevant record(s) in
datasheet view.

So far, I have combo boxes that display the correct items, and a subform
that displays all records. How do I link selections from the combo boxes to
the data that is displayed on the subform? I.e. if the user selects a
particular Employee, only cases for that employee will appear in the subform?
I don't need the user to be able to search by multiple items, e.g. Employee
AND Date Case Opened, just one search criterium is fine.

I'm afraid I couldn't figure it out from other posts or from other search
functionality I've built. TIA.
 
This is what I did to solve a similar problem.

First, if you only want to search one combo box criteria, put to
Combo#_Enter() code in to clear the other combo boxes.

Then the next part of the code is tied to the search button.

I use exact data matches and 'like' data matches for searching names,
addresses.

I'm sure you can convert this to meet your needs.

Cheers

-------------------------------------
Private Sub Combo2_Enter()

Me.Combo4 = ""
Me.Combo6 = ""
Me.Combo8 = ""
Me.Combo10 = ""

End Sub

Private Sub Combo4_Enter()

Me.Combo2 = ""
Me.Combo6 = ""
Me.Combo8 = ""
Me.Combo10 = ""

End Sub

Private Sub Search_Click()
On Error GoTo Err_Search_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "SearchResults"

If Me!Combo2 > "0" Then

stLinkCriteria = "[CADEventNumber] Like '" & "*" & Me.Combo2 & "*'"

ElseIf Me!Combo4 > "0" Then

stLinkCriteria = "[PoliceFileNumber] Like '" & "*" & Me.Combo4 & "*'"

ElseIf Me!Combo6 > "0" Then

stLinkCriteria = "[OtherFileNumber] Like '" & "*" & Me.Combo6 & "*'"

ElseIf Me.Combo8 > "0" Then

stLinkCriteria = "[IncidentAddress] Like '" & "*" & Me.Combo8 & "*'"

ElseIf Me.Combo10 > "0" Then

stLinkCriteria = "[RequestID]=" & Me![Combo10]

Else
MsgBox "Please enter a value in one of the search fields.", vbOKOnly
GoTo Exit_Search_Click

End If


DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Search_Click:
Exit Sub

Err_Search_Click:
MsgBox err.Description
Resume Exit_Search_Click

End Sub
 
Got it working, thanks!

Couple of weird follow-up questions (keep in mind my familiarity with code is
minimal).

1) Far as I know, this line:

DoCmd.OpenForm stDocName, , , stLinkCriteria

causes the subform to pop up when I click the search button, which is okay.
However, is there any way to make the subform update in the parent form
window where I've already put it and not appear as a separate pop-up form? At
present I've placed the subform in the bottom part of the parent form window,
but only shows all records and doesn't "respond" to the search function.

2) If I have to use the pop-up subform, any idea why it's abandoned the
formatting I applied to it? It's showing form view, which I don't like, and
won't keep my spacing or the setting to datasheet view I've already saved.

TIA.

Paul B. said:
This is what I did to solve a similar problem.

First, if you only want to search one combo box criteria, put to
Combo#_Enter() code in to clear the other combo boxes.

Then the next part of the code is tied to the search button.

I use exact data matches and 'like' data matches for searching names,
addresses.

I'm sure you can convert this to meet your needs.

Cheers

-------------------------------------
Private Sub Combo2_Enter()

Me.Combo4 = ""
Me.Combo6 = ""
Me.Combo8 = ""
Me.Combo10 = ""

End Sub

Private Sub Combo4_Enter()

Me.Combo2 = ""
Me.Combo6 = ""
Me.Combo8 = ""
Me.Combo10 = ""

End Sub

Private Sub Search_Click()
On Error GoTo Err_Search_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "SearchResults"

If Me!Combo2 > "0" Then

stLinkCriteria = "[CADEventNumber] Like '" & "*" & Me.Combo2 & "*'"

ElseIf Me!Combo4 > "0" Then

stLinkCriteria = "[PoliceFileNumber] Like '" & "*" & Me.Combo4 & "*'"

ElseIf Me!Combo6 > "0" Then

stLinkCriteria = "[OtherFileNumber] Like '" & "*" & Me.Combo6 & "*'"

ElseIf Me.Combo8 > "0" Then

stLinkCriteria = "[IncidentAddress] Like '" & "*" & Me.Combo8 & "*'"

ElseIf Me.Combo10 > "0" Then

stLinkCriteria = "[RequestID]=" & Me![Combo10]

Else
MsgBox "Please enter a value in one of the search fields.", vbOKOnly
GoTo Exit_Search_Click

End If


DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Search_Click:
Exit Sub

Err_Search_Click:
MsgBox err.Description
Resume Exit_Search_Click

End Sub

------------------------------------------------
MCB said:
I have a form that is intended to allow users to find specific records. On it
are multiple combo boxes to allow them to search via different means -
Employee, Team Lead, Case Number, Date Case Opened, etc. The form has a
subform, which displays the relevant fields of the relevant record(s) in
datasheet view.

So far, I have combo boxes that display the correct items, and a subform
that displays all records. How do I link selections from the combo boxes to
the data that is displayed on the subform? I.e. if the user selects a
particular Employee, only cases for that employee will appear in the subform?
I don't need the user to be able to search by multiple items, e.g. Employee
AND Date Case Opened, just one search criterium is fine.

I'm afraid I couldn't figure it out from other posts or from other search
functionality I've built. TIA.
Click to expand...
Click to expand...
 
I'm sorry, I am still learning this myself, and just like to offer some
solutions I have found.

As far as the subform popping up, you will probably need to compare your old
code to that which I gave you and modify it so your subform stays where you
want it.

The DoCmd.OpenForm line opens the form separate from the calling form, and
off the top of my head I don't know how to modify it.

Good luck.

MCB said:
Got it working, thanks!

Couple of weird follow-up questions (keep in mind my familiarity with code is
minimal).

1) Far as I know, this line:

DoCmd.OpenForm stDocName, , , stLinkCriteria

causes the subform to pop up when I click the search button, which is okay.
However, is there any way to make the subform update in the parent form
window where I've already put it and not appear as a separate pop-up form? At
present I've placed the subform in the bottom part of the parent form window,
but only shows all records and doesn't "respond" to the search function.

2) If I have to use the pop-up subform, any idea why it's abandoned the
formatting I applied to it? It's showing form view, which I don't like, and
won't keep my spacing or the setting to datasheet view I've already saved.

TIA.

Paul B. said:
This is what I did to solve a similar problem.

First, if you only want to search one combo box criteria, put to
Combo#_Enter() code in to clear the other combo boxes.

Then the next part of the code is tied to the search button.

I use exact data matches and 'like' data matches for searching names,
addresses.

I'm sure you can convert this to meet your needs.

Cheers

-------------------------------------
Private Sub Combo2_Enter()

Me.Combo4 = ""
Me.Combo6 = ""
Me.Combo8 = ""
Me.Combo10 = ""

End Sub

Private Sub Combo4_Enter()

Me.Combo2 = ""
Me.Combo6 = ""
Me.Combo8 = ""
Me.Combo10 = ""

End Sub

Private Sub Search_Click()
On Error GoTo Err_Search_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "SearchResults"

If Me!Combo2 > "0" Then

stLinkCriteria = "[CADEventNumber] Like '" & "*" & Me.Combo2 & "*'"

ElseIf Me!Combo4 > "0" Then

stLinkCriteria = "[PoliceFileNumber] Like '" & "*" & Me.Combo4 & "*'"

ElseIf Me!Combo6 > "0" Then

stLinkCriteria = "[OtherFileNumber] Like '" & "*" & Me.Combo6 & "*'"

ElseIf Me.Combo8 > "0" Then

stLinkCriteria = "[IncidentAddress] Like '" & "*" & Me.Combo8 & "*'"

ElseIf Me.Combo10 > "0" Then

stLinkCriteria = "[RequestID]=" & Me![Combo10]

Else
MsgBox "Please enter a value in one of the search fields.", vbOKOnly
GoTo Exit_Search_Click

End If


DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Search_Click:
Exit Sub

Err_Search_Click:
MsgBox err.Description
Resume Exit_Search_Click

End Sub

------------------------------------------------
MCB said:
I have a form that is intended to allow users to find specific records. On it
are multiple combo boxes to allow them to search via different means -
Employee, Team Lead, Case Number, Date Case Opened, etc. The form has a
subform, which displays the relevant fields of the relevant record(s) in
datasheet view.

So far, I have combo boxes that display the correct items, and a subform
that displays all records. How do I link selections from the combo boxes to
the data that is displayed on the subform? I.e. if the user selects a
particular Employee, only cases for that employee will appear in the subform?
I don't need the user to be able to search by multiple items, e.g. Employee
AND Date Case Opened, just one search criterium is fine.

I'm afraid I couldn't figure it out from other posts or from other search
functionality I've built. TIA.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top