Choosing a date and equip. number to retrieve specific data

[ChewDinCompSci]

Last week I asked for some help on coding a form to allow the user to choose a specific date and equipment number to retrieve specific data from numerous records and then send that data to a report. Thank you for all the help. I used the following code on my "report generator" form:

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

Dim sql_DateRpt, sqltv, sqlfv As String
Dim rsDateRpt As Recordset
Dim rsTV As Recordset

DoCmd.SetWarnings False

CurrentDb.Execute "Drop table tblrpt_RouteLog"
DoCmd.OpenQuery "qry_MakeRouteTable"

sqltv = "Select * from tblrpt_RouteLog"
Set rsTV = CurrentDb.OpenRecordset(sqltv, dbOpenDynaset)
If rsTV.RecordCount = 0 Then
MsgBox "There is no data for this report. Canceling report..."
rsTV.Close
Set rsTV = Nothing
Else
DoCmd.OpenReport "rpt_qryByTruck", acViewPreview

End If
End If
End Sub

An action query makes a table on the queried data that data is called on by another table ("tblrpt_RouteLog") which is the record source for the report. This table is first dropped and other data goes in each time a new report is made. The following SQL is necessary on the action query "qry_MakeRouteTable":

SELECT [tblRouteLog Query].* INTO tblrpt_RouteLog
FROM [tblRouteLog Query];


I hope this is of some help to someone.

MatChew



codename: vba4dumbE

 

[misscrf]

these kind of things are great. You might want to put it in an FAQ!
Thanks for posting the suggestion :-D

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[ChewDinCompSci]

Thanks misscrf, will do!

MatChew

codename: vba4dumbE