Message box popping up

[robercs]

I am a rookie when it comes to VBA. This is my first time writing code so please bear with me. I have a pretty simple form that I am using to filter and sort data. In the Form I have a Combo box with 3 values (Portable, Van, None). The idea is that the user selects the type from the combo box and then enters the two dates they are interested in. The form should filter the data by type and date. The problem is that the user selects a value from the combo box and then the dates they are interested in. When you hit the command button to filter and pull up the print preview of the report a message box appears that says enter parameter. The message box also contains the value the user selects from the combo box. Once you re-type the value into the message box the print preview of the form appears with the right data. My question is where does the message box come from? I don’t have any code written that tells it to pop up? Any thoughts…….




Sub cmdGenerateReport_Click()
Dim StrWhere As String
Dim strDates As String
Dim StrDocName As String
'Dim blnTrim As Boolean

If Me.cboTestType = "Portable" Then
StrWhere = StrWhere & "[Test Type]=" & Me.cboTestType & " And "
'blnTrim = True

ElseIf Me.cboTestType = "Van" Then
StrWhere = StrWhere & "[Test Type]=" & Me.cboTestType & " And "

ElseIf Me.cboTestType = "none" Then
StrWhere = StrWhere & "[Test Type]=" & Me.cboTestType & " And "
End If

If IsNull(Me.txtStartDAte) And Me.txtStartDAte = "" Then
If Not IsNull(Me.txtEndDate) And Me.txtEndDate <> "" Then
StrWhere = StrWhere & "[finish (date)] <=" & Me.txtStartDAte & "#"
'blnTrim = False
End If

Else
If (Not IsNull(Me.txtStartDAte) And Me.txtStartDAte <> "") And (Not IsNull(Me.txtStartDAte) Or Me.txtStartDAte <> "") Then
StrWhere = StrWhere & "[Finish (date)] Between #" & Me.txtEndDate & "# AND #" & Me.txtStartDAte & "#"
'blnTrim = False

End If
'If blnTrim Then
'StrWhere = Left(StrWhere, Len(StrWhere) - 5)
'End If
StrDocName = "General Report"
DoCmd.OpenReport StrDocName, acViewPreview, , StrWhere
End If
End Sub

[Yance]

I think it comes from your query report. Have you enter the parameter in your query report refered to your combo box / variables.

[Yance]

I think it comes from your query report. Have you enter the parameter in your query report refered to your combo box / variables.

[robercs]

Yance,

That's what I thought was happening but my Form is tied to a Table not a Query. So how would that be possible?

[Yance]

Can you attach your project example here?

[Yance]

Or can you figure your table structure?

[hansup]

You need to be able to see what strWhere looks like after your VBA code has finished building it. Insert a Debug.Print statement before you open the report:
Debug.Print "strWhere -> " & strWhere
StrDocName = "General Report"
DoCmd.OpenReport StrDocName, acViewPreview, , StrWhere You can view the output from Debug.Print in the Immediate Window. From your Access application window, you can get to the Immediate Window with the Ctrl+g keystroke combination.

I think when your combo selection is Van, you will find that part of strWhere looks like this:

[Test Type]=Van

You need quotes around the word Van to let Jet/ACE know it's supposed to be literal text. Without the quotes, Jet doesn't know what it is, and therefore assumes it is the name of a parameter ... and that's why it asks for a value for Van.

Try revising those lines as:

StrWhere = StrWhere & "[Test Type]=""" & Me.cboTestType & """ And "

I think you have another problem with this line:

StrWhere = StrWhere & "[finish (date)] <=" & Me.txtStartDAte & "#"

I suspect it should be:

StrWhere = StrWhere & "[finish (date)] <=#" & Me.txtStartDAte & "#"

Also, the following expression can never be True:

IsNull(Me.txtStartDAte) And Me.txtStartDAte = ""

txtStartDate can be Null, or it can be a zero length string. It can never be both Null And a zero length string at the same time.

If you want a more concise way to determine whether txtStartDate contains some characters, consider:

If Len(Me.txtStartDate & vbNullString) > 0 Then ...