Multiple Combo Boxes as Criteria for Continuous Form

  • Thread starter EmAlbritton via AccessMonster.com
  • Start date
E

EmAlbritton via AccessMonster.com

Morning All.

I have a form that has multiple combo boxes that are criteria for a
continuous form. In a nutshell, users can select any combination of the
combo boxes and click command button that will then list some information
regarding records that currently exist. The purpose of this is so that they
can then select a specific record to update it.

The code for the command button is:

Private Sub Btn_OpenUpdateList_Click()
On Error GoTo Err_Btn_OpenUpdateList_Click

Dim strWhere As String

If Not IsNull(Me.ctrl_MarketSelector) Then
strWhere = strWhere & " AND " & _
"Market = '" & Me.ctrl_MarketSelector & "'"
End If
If Not IsNull(Me.ctrl_ClientSelector) Then
strWhere = strWhere & " AND " & _
"Client = '" & Me.ctrl_ClientSelector & "'"
End If
If Not IsNull(Me.ctrl_CompanySelector) Then
strWhere = strWhere & " AND " & _
"Company = '" & Me.ctrl_CompanySelector & "'"
End If
If Not IsNull(Me.ctrl_CollectorCompanySelector) Then
strWhere = strWhere & " AND " & _
"CollectorCompany = '" & Me.ctrl_CollectorCompanySelector & "'"
End If
If Not IsNull(Me.ctrl_CollectorNameSelector) Then
strWhere = strWhere & " AND " & _
"CollectorName = '" & Me.ctrl_CollectorNameSelector & "'"
End If

If Len(strWhere) > 4 Then strWhere = Right(strWhere, Len(strWhere) - 5)
DoCmd.OpenForm "FrmUpdateListing", , , strWhere


Exit_Btn_OpenUpdateList_Click:
Exit Sub

Err_Btn_OpenUpdateList_Click:
MsgBox Err.Description
Resume Exit_Btn_OpenUpdateList_Click

End Sub


The problem is this...when a choice is made and the command button is
selected, the returned information is repeated. I have tested this with a
selection that is satisfied by only a single record, and it is returned in
the cont. form multiple times.

I don't really know where to go with it. Any help would be appreciated.

Thanks,
emily
 
E

EmAlbritton via AccessMonster.com

Disregard this post. I was trying to copy code from a similar form. I
started from scratch with new forms, and pasted the code and it works now.

ea
 
M

Marshall Barton

EmAlbritton said:
I have a form that has multiple combo boxes that are criteria for a
continuous form. In a nutshell, users can select any combination of the
combo boxes and click command button that will then list some information
regarding records that currently exist. The purpose of this is so that they
can then select a specific record to update it.

The code for the command button is:

Private Sub Btn_OpenUpdateList_Click()
On Error GoTo Err_Btn_OpenUpdateList_Click

Dim strWhere As String

If Not IsNull(Me.ctrl_MarketSelector) Then
strWhere = strWhere & " AND " & _
"Market = '" & Me.ctrl_MarketSelector & "'"
End If
If Not IsNull(Me.ctrl_ClientSelector) Then
strWhere = strWhere & " AND " & _
"Client = '" & Me.ctrl_ClientSelector & "'"
End If
If Not IsNull(Me.ctrl_CompanySelector) Then
strWhere = strWhere & " AND " & _
"Company = '" & Me.ctrl_CompanySelector & "'"
End If
If Not IsNull(Me.ctrl_CollectorCompanySelector) Then
strWhere = strWhere & " AND " & _
"CollectorCompany = '" & Me.ctrl_CollectorCompanySelector & "'"
End If
If Not IsNull(Me.ctrl_CollectorNameSelector) Then
strWhere = strWhere & " AND " & _
"CollectorName = '" & Me.ctrl_CollectorNameSelector & "'"
End If

If Len(strWhere) > 4 Then strWhere = Right(strWhere, Len(strWhere) - 5)
DoCmd.OpenForm "FrmUpdateListing", , , strWhere


Exit_Btn_OpenUpdateList_Click:
Exit Sub

Err_Btn_OpenUpdateList_Click:
MsgBox Err.Description
Resume Exit_Btn_OpenUpdateList_Click

End Sub


The problem is this...when a choice is made and the command button is
selected, the returned information is repeated. I have tested this with a
selection that is satisfied by only a single record, and it is returned in
the cont. form multiple times.
Click to expand...


That code looks like it should do what you want. Since a
filter can not cause extra records to appear, I suspect the
problem is in the form's record source query. Try running
the query directly from the query design window to check
what it is doing.
 
Top