Problem coding form to report queried data

[ChewDinCompSci]

I have a form that allows the user to choose the date and an equipment number to produce a report on. The trouble I'm having is that nothing happens when you click on the command button to preview the report. I want the data to be sent to a report an empty report I have already made. This is what I have for the command button.

Private Sub cmdmakereport_Click()
Dim strWhere As String
If IsNull([cboRptDate]) Or IsNull([cboRptEquip]) Then
MsgBox "You must enter both a date and equipment number."
DoCmd.GoToControl "cboRptDate"
End If
strWhere = "[Date] =#" & Me.cboRptDate & "# " & _
"AND [EquipmentNumber=""" & Me.cboRptEquip & """"
End Sub

My report is called rpt_qryByTruck. I'm almost certain I need a Docmd.OpenReport in here. Any ideas???

 

[FancyPrairie]

Try this:

Private Sub cmdmakereport_Click()

  Dim strWhere As String

  If IsNull([cboRptDate]) Or IsNull([cboRptEquip]) Then

    MsgBox "You must enter both a date and equipment number."
    DoCmd.GoToControl "cboRptDate"

  Else

    strWhere = "[Date] =#" & Me.cboRptDate & "# " & _
    "AND [EquipmentNumber=""" & Me.cboRptEquip & """"

    Docmd.OpenReport "rpt_qryByTruck",acViewPreview,,strWhere

  End If
End Sub

 

[ChewDinCompSci]

Thanks for the help Fancy. Just wondering, why the end if after the DoCmd.OpenReport?

codename: vba4dumbE

 

[ChewDinCompSci]

I tried that line and get the run-time error 3078, cannot find input table 'tblRouteLog'

This is my main table from which my data is being queried. Do you know what could be causing this error?

codename: vba4dumbE

 

[FancyPrairie]

Note the "Else" statement. That's why the "End If" statement is where it is.

I suspect that the problem is that you have your Record Source set to a table rather than a query (i.e. Select * from tblRouteLog). Not sure though.

You may not be doing what you're think you're doing. To test it out, do the following:

1. Create a new query (same as Record Source of Report)
For example, Select * from tblRouteLog

2. Switch your query from design view to SQL view

3. Add the following code (line in red) to your sub and then execute your code as you did before.

strWhere = "[Date] =#" & Me.cboRptDate & "# " & _
"AND [EquipmentNumber=""" & Me.cboRptEquip & """"

Debug.Print strWhere

Docmd.OpenReport "rpt_qryByTruck",acViewPreview,,strWhere

4. Copy and paste the results of the debug statement into your new query (created above). Now run it to see if it works.

 

[ChewDinCompSci]

I cannot express how much I appreciate your help. I am making great headway. I have the following SQL for my report:

SELECT * FROM tblRoutLog WHERE ((([tblRouteLog].[Date]=[Forms]!ReprtGenerator![cboRptDate] And [tblRouteLog].[Equipment Number])=[Forms]![ReprtGenerator]));

Is this unnecessary here and causing a problem?

codename: vba4dumbE

 

[ChewDinCompSci]

Oh my, I just realized I spelled report wrong in two places there. Going to correct that now and see how things go.

codename: vba4dumbE

 

[FancyPrairie]

If the SQL statement above is the Record Source for your report, then you don't need to use or set "strWhere" in your code. Just open the report like this:

Docmd.OpenReport "rpt_qryByTruck

 

[FancyPrairie]

You also spelled tblRouteLog as tblRoutLog.

 

[ChewDinCompSci]

Thanks, I changed the spelling there as well.

I have taken out all strWhere code and am now using just the SQL on the report. However I keep getting run-time error 2501: The OpenReport action was cancelled. What could be causing this?

codename: vba4dumbE