Open Report from SQL Query

[dzirkelb]

I am trying to put a value into a variable from an sql statement, and I am getting an error...here is my code which is way wrong:

Function PrintPackSlip()
    Dim strPackSlip As String
    
    strPackSlip = "SELECT PackSlip FROM InvoicePackSlipPrint"

    DoCmd.OpenReport strPackSlip, acViewNormal
End Function

The results of the query will always be one result, so no where clause is necessary. However, the error I get is:

The report named "SELECT PackSlip FROM InvoicePackSlipPrint" you entered is mispelled or blah blah...

Can someone point me into the right direction? Also, instead of having it view the report, could I have it print it instead?

 

[pbaldy]

The argument you're specifying is for the report's name, not its source. Just base the report on that SQL, and call the report by name.

 

[dzirkelb]

How would I go about doing what you just suggested? My vb skills are not the best.

 

[pbaldy]

No VB skills required. Create a report if you haven't already, with the query "InvoicePackSlipPrint" as its record source. Let's say your report is named "ReportName". Your code changes to:

DoCmd.OpenReport "ReportName", acViewNormal

 

[dzirkelb]

The report name will change, however. Here is an example of the data from teh table InvoicePackSlipPrint

InvoiceNum: 123456
PackSlip: PACKING SLIP-A

That PackSlip can change to PACKING SLIP-B, or PACKING SLIP-C...I want the function to know to open the report which is in the table...It will never open a static report.

Also, I wish to open the report and print it automatically. I can do this with a macro, but not sure how to do it with vb. I will need all this to run in a macro also, as the access db will run as a program sorta...open, print a report based on what is in the table, then close itself.

 

[pbaldy]

Ah, I see where you're going. Try this:

    Dim strPackSlip As String
    
    strPackSlip = DLookup("PackSlip", "InvoicePackSlipPrint")

    DoCmd.OpenReport strPackSlip, acViewNormal

The acViewNormal should make it go straight to the printer.

 

[dzirkelb]

That works perfectly!

Thanks!

 

[pbaldy]

No problem; sorry I didn't pick up on what you were trying to do right off. Sometimes I get thick-headed.

 

[dzirkelb]

Hehe, np, I am not doing something normal...having my access database be a print server in a sense!

now i'm getting access application errors on my sql 2003 machine (that didnt' happen on my xp machine), but that is another issue...goign to upgrade it from 97 and see if that helps.