Code form to query/report all records relating to a specific variable

[ChewDinCompSci]

I have a form that allows the user to select a specific date (cboRptDate) and equipment number (cboRptEquip) out of a large amount of records and then create a report on those choices. The report works fine however I want the user to have the option of also choosing all trucks (equipment) that were used on a specific date chosen. The code I have tried is on the RowSource for the equipment combobox is...

SELECT [LKP_tblEquipmentNumbers].[Equipment Number] FROM LKP_tblEquipmentNumbers, UNION SELECT "0" As AllChoice From LKP_tblEquipmentNumbers;

Also, just to provide a little more info about how this form works, the code for the command button to create the report:

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

'Disable all warning messages for changes made by action query
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

When I choose "0" and click the cmd I get the "There is no data..." msgbox. Any ideas??? Any help is much appreciated.

MatChew

codename: vba4dumbE

 

[Crowley16]

you seem to be dropping the table, then recreating it...

are you sure when you recreate the table that everything works and there is infact data in the table, or even there is indeed a table?

I think the problem lies in your maketable query, can you post that up please?

--------------------
Procrastinate Now!

 

[ChewDinCompSci]

The SQL for my qry_MakeRouteTable:

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

I hope you're following this because I barely understand how the code works myself, lol. I start off with tblrpt_RouteLog as an empty table.

codename: vba4dumbE

 

[ChewDinCompSci]

Crowley16,

the SQL for the query "tblRouteLog Query" is:

SELECT tblRouteLog.Log_ID, tblRouteLog.Date, tblRouteLog.[Equipment Number], ........ FROM tblRouteLog WHERE (((tblRouteLog.Date)=[Forms]![frmReportGenerator]![cboRptDate]) AND ((tblRouteLog.[Equipment Number])=[Forms]![frmReportGenerator]![cboRptEquip]));

Thanks so much for taking the time to help me

codename: vba4dumbE

 

[Crowley16]

if you are just copying tblRouteLog there's no need for that step at all...

try using:
sqltv = "Select * from [tblRouteLog Query]"

and make sure the data you want exsists in tblRouteLog...

Also, since you want to set conditions in the report, you should filter the results, i.e.

sqltv = "Select * from [tblRouteLog Query] WHERE reportDate = #" & cboRptDate & "# AND equipmentID = '" & cboEquip & "';"

of course, if you want the value '0' to select all equipments, then you need to test the value of cboEquip and generate a different sql statement, i.e.

sqltv = "Select * from [tblRouteLog Query] WHERE reportDate = #" & cboRptDate & "#;"



--------------------
Procrastinate Now!

 

[ChewDinCompSci]

I don't want to simply copy tblRouteLog. I want to query those records that apply to what is chosen in the two combo boxes on my form. I checked to make sure applicable records exist before chosing a date and equipment number. It works fine for choosing one equipment number for a single date. All i wish to do is have my form also report all equipment numbers for the date chosen.

I tried using the following as you suggested,

sqltv = "Select * from [tblRouteLog Query] WHERE Date = #" & cboRptDate & "#;"

... however, I still get the msqbox that there is no data.

Are you saying the step with the MakeTable query may be causing the problem?

codename: vba4dumbE