Displaying Matching Records from Combo Boxes

[Guest]

I have a form with two Combo Boxes to filter data when the open form button
is pressed. The code below only works when i choose an option from both combo
boxes... i want to be able to choose no options or one option from either.
any help would be appreciated. thanks.

Private Sub btnCompanyManagement_Click()
On Error GoTo Err_btnCompanyManagement_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCompany_Management"

stLinkCriteria = "[Status]=" & "'" & Me![cmbStatus] & "'"
stLinkCriteria = "[Account_Manager]=" & "'" & Me![cmbAccount_Manager] &
"'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btnCompanyManagement_Click:
Exit Sub

Err_btnCompanyManagement_Click:
MsgBox Err.Description
Resume Exit_btnCompanyManagement_Click

End Sub

 

[Graham Mandeno]

Hi Intrepid

The code you have shown us will filter only on [Account_Manager] and ignore
[Status].

You need something like this:

If Not IsNull(cmbStatus) Then
stLinkCriteria = "[Status]=" & "'" & Me![cmbStatus] & "'"
End If
If Not IsNull(cmbAccount_Manager) Then
If Len(stCriteria) > 0 then stCriteria = stCriteria & " and "
stLinkCriteria = stCriteria & "[Account_Manager]=" _
& "'" & Me![cmbAccount_Manager] & "'"
End If
DoCmd.OpenForm stDocName, , , stLinkCriteria

This will add filter criteria to the stCriteria string only if the combo
boxes are not Null (i.e. have a value selected).

 

[Guest]

Hi,

That would have worked great. Thanks.

But I changed it over to use QueryDef instead and have it all working now.
But thank you still.

IY.

Hi Intrepid

The code you have shown us will filter only on [Account_Manager] and ignore
[Status].

You need something like this:

If Not IsNull(cmbStatus) Then
stLinkCriteria = "[Status]=" & "'" & Me![cmbStatus] & "'"
End If
If Not IsNull(cmbAccount_Manager) Then
If Len(stCriteria) > 0 then stCriteria = stCriteria & " and "
stLinkCriteria = stCriteria & "[Account_Manager]=" _
& "'" & Me![cmbAccount_Manager] & "'"
End If
DoCmd.OpenForm stDocName, , , stLinkCriteria

This will add filter criteria to the stCriteria string only if the combo
boxes are not Null (i.e. have a value selected).
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a form with two Combo Boxes to filter data when the open form button
is pressed. The code below only works when i choose an option from both
combo
boxes... i want to be able to choose no options or one option from either.
any help would be appreciated. thanks.

Private Sub btnCompanyManagement_Click()
On Error GoTo Err_btnCompanyManagement_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCompany_Management"

stLinkCriteria = "[Status]=" & "'" & Me![cmbStatus] & "'"
stLinkCriteria = "[Account_Manager]=" & "'" & Me![cmbAccount_Manager] &
"'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btnCompanyManagement_Click:
Exit Sub

Err_btnCompanyManagement_Click:
MsgBox Err.Description
Resume Exit_btnCompanyManagement_Click

End Sub