Code Error

Hi there. i've been trying to figure what went wrong with this VBA coding that i used it for mircosoft access 2003. When i click View All on both combo box, it shows what i want, but when i change the variable in the combo for either one or both, it display nothing or everything.


Private Sub Command10_Click()
Dim choice As String
If (FormSelection.Value = "Vendor") Then
choice = "A"
ElseIf (FormSelection.Value = "Inhouse") Then
choice = "B"
ElseIf (FormSelection.Value = "Japan") Then
choice = "C"
End If

On Error GoTo Err_Command10_Click

Dim stDocName As String

stDocName = "ProductRpt"

'>> Total of 4 combinations <<
'Category Name = view all Form Selection = view all
If (CateName.Value = "View All" And FormSelection.Value = "View All") Then
DoCmd.OpenReport stDocName, acPreview

'Category Name = view all Form Selection != view all
ElseIf (CateName.Value = "View All" And FormSelection.Value <> "View All") Then
DoCmd.OpenReport stDocName, acPreview, , "FormSelection = " & choice

'Category Name != view all Form Selection = view all
ElseIf (CateName.Value <> "View All" And FormSelection.Value = "View All") Then

'Category Name! = view all Form Selection != view all
DoCmd.OpenReport stDocName, acPreview, , "CateName = '" & CateName.Value & "'"

Else

'rest is team and criteria
DoCmd.OpenReport stDocName, acPreview, , "CateName = '" & CateName.Value & _
"' and FormSelection = " & choice
End If
Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub

Private Sub FormSelection_AfterUpdate()

If (FormSelection.Value = "View all") Then
ElseIf (FormSelection.Value = "Vendor") Then
ElseIf (FormSelection.Value = "Inhouse") Then
ElseIf (FormSelection.Value = "Japan") Then
Else
FormSelection.Value = "View all"
End If
End Sub

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories