VBA Access 2003

March 19, 2009 at 18:17:11
Specs: Windows XP
I didn't know where went wrong. When i select View All in both the combo box, it appear the data that i want. Yet if when i change the variable of the CateName and View All in the FormSelection, the data appear all, which is not what i need. Anyone can tell me where when wrong?

Option Compare Database
Option Explicit

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 CateName and FormSelection
DoCmd.OpenReport stDocName, acPreview, , "CateName ='" & CateName.Value & _
"' 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()

'not one of the choice in DDL
'not view not outstanding not completed
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


See More: VBA Access 2003

Report •


#1
March 19, 2009 at 22:20:40
is it "View all" or "View All"???

Report •

#2
March 19, 2009 at 22:26:40
View All.
I've solved the top of the error alr. Would you mind seeing if
these coding got error?

DoCmd.OpenReport stDocName, acPreview, , "ProductCat ='" & ProductCat.Value & _
"FormSelection ='" & FormSelection.Value & "'"

Becos it keep saying Syntax Error(missing operator) in query expression

Thanks for your reply btw. (:


Report •

#3
March 19, 2009 at 22:38:20
"ProductCat ='" & ProductCat.Value & _
"FormSelection ='"

You never close your single quote.

Report •

Related Solutions

#4
March 19, 2009 at 22:50:41
"ProductCat ='" & ProductCat.Value & "'"_
"FormSelection ='" & FormSelection.Value & "'"

Like that?


Report •

#5
March 19, 2009 at 23:16:19
That would do it, but you also need some sort of relation between the two conditional statements.

"ProductCat ='" & ProductCat.Value _
"' AND FormSelection ='" & FormSelection.Value & "'"


Report •

#6
March 19, 2009 at 23:23:10
It Works!! Thanks!! :D

Report •


Ask Question