Pass a value to a report for printing? 1

[LLBP]

I want to print a serial number onto a label from VBA. This db is multiuser and all are adding serial numbers regularly. I am currently storing the values in a temp table and passing the value to a query based on the temp table. The report is based on the query and it prints ok. There must be a way to pass the value directly to an unbound field on a report, right??

 

[earthandfire]

How do you run the report?

 

[PaulBricker]

It sounds like you can just use the DLookUp() function in an unbound control.

=DLookUp("fieldName", "queryName")

If you query returns more than one serial number, then you will have to use the third arugment of the function to get the correct serial number.

=DLookUp("fieldName", "queryName", "SomeFieldInQuery = '" & SomefieldInReport & "'")

Paul

 

[LLBP]

This is the code currently:

DoCmd.OpenReport "PrintSerialLabel", acViewNormal, "qryPrintSNReport", "SerialNumber =" & NextSN

Here is the query SQL:

SELECT TempSN.SerialNumber
FROM TempSN;

I append the SN to the tempSN table so that the query has something to lookup. I want to bypass all that and send the SN value to the report directly. Can I reference a global variable from a report? Can I send the value of a global variable to a report field?

 

[earthandfire]

If you have a global variable SerialNumber, then create a public function

Public function GetSerialNumber
GetSerialNumber = SerialNumber
End Function

Set the controlSource for the reportcontrol to

=GetSerialNumber()

hope this helps