Whats wrong with my code? Need help troubleshooting. (1 Viewer)

[scottk]

The objects used here are: polLabels, a button, PoliticsSelect, a list box, and Branch, an option group.

The idea here is to open a selected report and apply a filter based on whatever is selected in the option box

The problem exists with the following two lines

DoCmd.OpenReport "Address Label - Representative Lookup by Constituent", acViewPreview, , strBranch

and

DoCmd.OpenReport "Address Label - Representative Lookup by County", acViewPreview, , strBranch

This same command works on other subroutines but I'm not sure why it is not working correctly here. Any help you can give me would greatly be appreciated.

Private Sub polLabels_Click()

Dim strFunction As String
Dim strBranch As String

If IsNull(Forms![Reports Switchboard]!PoliticsSelect) Then
strFunction = ""
Else
strFunction = Forms![Reports Switchboard]!PoliticsSelect
End If

Select Case Me!Branch
Case 1
strBranch = "[Branch] = CA House"
Case 2
strBranch = "[Branch] = CA Senate"
Case 3
strBranch = "[Branch] = US House"
Case 4
strBranch = "[Branch] = US Senate"
Case 5
strBranch = ""
End Select

If strFunction = "" Then
MsgBox "You Must Select a Query"
Else
If (strFunction = "District Lookup by Constituent") Or (strFunction = "District Lookup by County") Then
MsgBox "This Report is not valid for the selected function"
Else
If strFunction = "Representative Lookup by Constituent" Then
DoCmd.OpenReport "Address Label - Representative Lookup by Constituent", acViewPreview, , strBranch
Else
DoCmd.OpenReport "Address Label - Representative Lookup by County", acViewPreview, , strBranch
End If
End If
End If

End Sub

 

[rich.barry]

I'd say your problem would be that in your Case's, the value that strBranch is set to must be a SQL WHERE statement. That means the text must be in quotes.
Unfortunately the whole WHERE is already written inside quotes, so for the inner quotes, you must resort to an apostrophe.

eg strBranch = "[Branch] = 'CA House'"

 

[scottk]

Well done Rich, that works perfectly!

Thanks for the help.