Problem reporting queried data

[ChewDinCompSci]

I am attempting to send data to a report that is queried by a form where the end user chooses a date and an equipment number. So I'd like to report only on a specific date and equipment number from all my data. I've used the following code for On Click to produce the report:

Private Sub cmdmakereport_Click()
If IsNull([cboRptDate]) Or IsNull([cboRptEquip]) Then
MsgBox "You must enter both a date and equipmentnumber."
DoCmd.GoToControl "cboRptDate"
End If
End Sub

AND... code I've added to the report itself:

Option Compare Database
Option Explicit

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = -1
End Sub

Private Sub Report_Close()
DoCmd.Close acForm, "frmReportGenerator"
End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmReportGenerator", , , , , acDialog, "rpt_qryByTruck"
If Not IsLoaded("frmReportGenerator") Then
Cancel = True
End If
End Sub

The problem is that nothing happens when I click on the command button cmdmakreport. I have a feeling that I'm missing something simple here but I've had a lot of trouble figuring it out. Any ideas?

 

[dhookom]

There is no code to open the report.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[ChewDinCompSci]

How do I report only that data which reflects the selection of "Date" and "Equipment Number" made by the user?

Matt

 

[dhookom]

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

The above code assumes EquipmentNumber is text. If it is numeric, then change the line to

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

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[ChewDinCompSci]

Thanks for the help dhookom. However, clicking cmdmakeroprt still does nothing. I have created a report called rpt_qryByTruck and want the data queried here to go into that report. I realize it's not as simple as Docmd.OpenReport "rpt_qryByTruck" Sorry if I was too vague, I am still somewhat new to vb.

 

[dhookom]

Oops my bad.

Add this line after the strWhere = ....

DoCmd.OpenReport "rpt_qryByTruck",acPreview, , strWhere

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[ChewDinCompSci]

I also forgot to add... I included an SQL for the report itself which might make this line unnecessary. The SQL is:

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

Now I get run-time error 2501: The OpenReport action was cancelled. Do you know what is causing this?




codename: vba4dumbE

 

[dhookom]

I would remove the where clause from the sql in the record source and only apply the filter with the strWhere clause in the DoCmd.OpenReport. Do you have any code running in the report?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]