Code form to populate records relating to a chosen variable 2

[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 would be much appreciated. Thank you.

MatChew

codename: vba4dumbE

 

[DoubleD]

Not sure why you're running a make table query. Why not just base your report on a query.
I assume there is some type of filter in your qry_MakeRouteTable based on your ComboBox. Have you adjusted the filter to account for your 0 selection?

I am what I am based on the decisions I have made.

DoubleD

 

[ChewDinCompSci]

I didn't start this db but I believe the make table query was used in conjuncton with an option to export the data to excel. The SQL for the qry_MakeRouteTable is...

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

So all the queried data ends up in "tblrpt_RouteLog"

The filter on "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]));

Do I need to adjust something here to account for the 0 selection?

codename: vba4dumbE

 

[DoubleD]

The filter will need to be changed for tblRouteLog Query to adjust for the 0 selection.

Something like this should work:
SELECT tblRouteLog.Log_ID, tblRouteLog.Date, tblRouteLog.[Equipment Number]... FROM tblRouteLog
WHERE (((tblRouteLog.Date)=[Forms]![frmReportGenerator]![cboRptDate]) AND ((tblRouteLog.[Equipment Number])=[Forms]![frmReportGenerator]![cboRptEquip]))) OR (((tblRouteLog.Date)=[Forms]![frmReportGenerator]![cboRptDate]) AND ("0")=[Forms]![frmReportGenerator]![cboRptEquip])));



I am what I am based on the decisions I have made.

DoubleD

 

[PHV]

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


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DoubleD]

Yep, PHV's is cleaner SQL.

I am what I am based on the decisions I have made.

DoubleD

 

[ChewDinCompSci]

Tek-Tips has officially become my favorite forum, no, my favorite website. DoubleD, PHV... I love you both! Thank you so much for the help.

MatChew

codename: vba4dumbE

 

[ChewDinCompSci]

Also, I would like to use "<All Trucks>" instead of "0". I replaced the 0 with All Trucks in both SQLs however, I get a data type mismatch error when I click cmdmakereport. The Equipment Number is of data type Number. I tried to change it but it says it cannot be changed as "it is part of one or more relationships." This change isn't absolutely necessary but I'm sorta anal like that.

codename: vba4dumbE

 

[DoubleD]

Can you add a column to LKP_tblEquipmentNumbers for Description? If so, your combo box can contain two columns, 1 hidden column containing the numbers, and 1 displayed column showing descriptions.
Your combo box will be based on your first column, so you should be set from that point.

I am what I am based on the decisions I have made.

DoubleD