Code form to report/query all records that apply to specific variable 1

[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???

MatChew

codename: vba4dumbE

 

[misscrf]

just a question, but why not put these variables on a form, and use the query builder to set the criteria to the form fields in the query/report fields?

If you want more let me know.


misscrf

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

 

[ChewDinCompSci]

I'm not sure what you mean. The user can choose the Date and Equipment number on the form which produces the report. I am having a lot of trouble with this one and if you know an easier way to do what I'm trying to accomplish, please do share. Many thanks in advance.

MatChew


codename: vba4dumbE

 

[misscrf]

I just see that code and I never use code in parameter reports. can you email or post a small sample of the table(s) that make up the data? and maybe the form for the report to, and the report as well (lol).

I would like to modify it to show you a different way that may work good for you.


misscrf

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

 

[misscrf]

http://www.geocities.com/misscrf/queryreport.mdb

Let me know if that is the kind of thing that you are working on. If so, this can be easy.

Notice the control source of the report, and module 1 which is the optmask criteria if you will need that.

From there we just do an afterupdate or on exit of the 1 date field you have, and make that an update/filter to a combo for trucks with a record on that date. Then they choose that, and report!


misscrf

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

 

[ChewDinCompSci]

Sure I can email it. Wut's your addy?

codename: vba4dumbE

 

[misscrf]

how do I send a private message? we are not supposed to publicly post our emails because it brings spammers to the site.

misscrf

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

 

[ChewDinCompSci]

I'm not sure how to send a private message on here. I don't have a web host to save screen shots of my db table and forms either. Guess I'm just doomed!?!

codename: vba4dumbE

 

[misscrf]

did you look at the link I posted?
I made you a sample db with a table, form, and report ( and module)

check it out and let me know.

misscrf

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

 

[ChewDinCompSci]

Sorry, where did you post the link?

codename: vba4dumbE

 

[misscrf]

http://www.geocities.com/misscrf/queryreport.mdb

misscrf

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

 

[ChewDinCompSci]

Thank you very much misscrf! This db will be very helpful. I figured out what I needed. I had to account for the "0" in "tblRouteLog Query". I changed the SQL to:

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");

This worked fine but the way you have set up Usage Reports is much simpler and more efficient. Thanks again.

MatChew


codename: vba4dumbE

 

[misscrf]

my pleasure!

misscrf

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