passing variable from form to query (1 Viewer)

[wgma]

I have a form that is used for data entry.Once the data has been entered the user can click a button at the bottom of the screen that will print reports. One of the reports is based on a query that takes 2 parameters. When I run the report I get an error saying "You can't reference a property of method for a control unless the control has focus"This is the code calling the report: DoCmd.OpenReport "rptManifest", acNormal, , "[qryManifest.BillOfLadingNbr]=" & Me.txtBillOfLadingNbr.Text & " AND [qryManifest.ExportCarrierID]=" & Me.cboExportCarrier.ValueWhen I debug it complains about Me.txtBillOfLadingNbr.Text. Here is my query:SELECT lkupshipper.company+Chr(13)+Chr(10)+lkupshipper.address AS Shipper, tlbShip.ShipName, tblBillOfLading.ExportRef, tblBillOfLading.BillOfLadingNbr, tblShipment.NbrOfPkgs, tblShipment.Description, tblShipment.GrossWeight, tblBillOfLading.FreightCharges, tblBillOfLading.ID, Sum(tblPrepaid.Prepaid) AS Prepaid, Date() AS rptDateFROM ((tlbShip RIGHT JOIN (lkupshipper RIGHT JOIN tblBillOfLading ON lkupshipper.id=tblBillOfLading.ShipperID) ON tlbShip.ID=tblBillOfLading.ExportCarrierID) INNER JOIN tblShipment ON tblBillOfLading.ID=tblShipment.BillOfLadingID) LEFT JOIN tblPrepaid ON tblBillOfLading.ID=tblPrepaid.BLIDWHERE tblBillOfLading.BillOfLadingNbr=forms!frmBillOfLading!txtBillOfLadingNbr And tblBillOfLading.ExportCarrierID=forms!frmBillOfLading!cboExportCarrierGROUP BY lkupshipper.company+Chr(13)+Chr(10)+lkupshipper.address, tlbShip.ShipName, tblBillOfLading.ExportRef, tblBillOfLading.BillOfLadingNbr, tblShipment.NbrOfPkgs, tblShipment.Description, tblShipment.GrossWeight, tblBillOfLading.FreightCharges, tblBillOfLading.ID, lkupshipper.company;How do I pass multiple parameters to a query?Thanks.

 

[pbaldy]

Drop the .Text, which requires focus.

 

[wgma]

That worked. Thanks.

Now I have another problem. When I click on the button to print the report I get awindow that pops up stating "Enter parameter value". Above the textbox on the popup is the value of the parameter and then below that I have the textbox to enter the parameter.

How do I get this to work?

Thanks.

 

[pbaldy]

The parameter prompt typically comes up when something is misspelled and Access can't find it. What is the text of the prompt? That should lead you to what it's trying to find.

 

[wgma]

When I click on the button it prompts me for the parameter. The prompt looks something like this:

Enter Parameter value
CN0809HOMO
-------------------------
| textbox |
-------------------------

This is the value that it should be pulling from the form - CN0809HOMO.

 

[LPurvis]

It'll just be due to a datatype issue
(I assume the following is what you're using and what's causing the issue - the question text is tricky to decipher :-s)

You'd want perhaps:

DoCmd.OpenReport "rptManifest", acNormal, , "BillOfLadingNbr = '" Me.txtBillOfLadingNbr & "' AND ExportCarrierID = " & Me.cboExportCarrier

 

[wgma]

OK, I had to work on something else for a while but now I am back.

I tried what you suggested and it took care of the first variable but the application still asks for the parameter for the ExportCarrierID.

this is my Where statement:

WHERE tblBillOfLading.BillOfLadingNbr=Forms!frmBillOfLading.txtBillOfLadingNbr And tblBillOfLading.ExportCarrierID=Forms!frmBillOfLading.cboExportCarrier

do I need to put quotes around the Forms!frmBillOfLading.txtBillOfLadingNbr variable somehow? If so, how do I do that in this code?

Otherwise, here is the code that calls the report:

DoCmd.OpenReport "rptManifest", acViewPreview, , "BillOfLadingNbr ='" & Me.txtBillOfLadingNbr & "' AND qryManifest.ExportCarrierID = " & Me.cboExportCarrier.Value

I put quotes around Me.txtBillOfLadingNbr and qryManifest.ExportCarrierID is a number. I am not sure of what I am doing wrong here.

Thanks.

 

[LPurvis]

If you're refering to the form object as an expression in your query then you don't want to delimit it no.
Where does the query come in to play though? Is it the recordsource of the report?
If so - why do you limit the source both in the query and in the Wherecondition of the OpenReport method?

 

[wgma]

Yes, the query is the record source for the report and I am delimiting it in both places because I don't know what I am doing and I saw an example of this somewhere.

If there is a better way please tell me.

Thanks for your response.

 

[LPurvis]

You only need the criteria in one location.
Either in the query or in the call to the report.

However either way (or indeed both as it now) - once the expression is entered correctly you should receive no prompting.
Is there an example MDB to look at?

 

[wgma]

I took it out of the VBA and I get no prompting. Thanks.