Solved Convert these macros to VBA? (1 Viewer)

db-noob

Member
Local time
Today, 14:58
Joined
Oct 16, 2020
Messages
47
Hello all and Happy New Year!

In the database I built in 2019, I created macros for the two main forms to 1) save current record as PDF or 2) print current record. I know there's that button that will convert a form's macros to VBA, but I don't want all of them converted.

Here's screenshots of 2 of the 4 macros I want to convert. Something I'd like change about these buttons is that users have had to type or paste the number they want (WorkOrder# or POrderNumber) since a Enter a Parameter value message box pops up. Is there some way to automatically "copy & paste" that number?

Thanks for reading!

EDIT: I should add that I built reports for both forms. I was forgetful and didn't use the tbl, frm, and rpt prefixes.

PO-PDF-Button.jpg
PO-Print-Button.jpg
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:58
Joined
Feb 28, 2001
Messages
27,001
Your question about somehow conveying a number into the macro/VBA is FAR easier since in VBA you can perform substitution based on a form. Since you would probably RUN the VBA from a form, if the number is in a control on that form, it might be as simple as assigning the report for the full table but THEN using VBA to assert a report filter before (or as part of) opening it. The "convert Macro to VBA" action for the one that opens the report would certainly use the equivalent of a DoCmd.OpenReport for which one of the arguments is a filter that constrains the data being gathered for the report. This would surely be easily managed by building the string dynamically based on something on the form. Like a text box, for example.


The 4th argument is the filter, which might be implemented via

Code:
DoCmd.OpenReport "PurchaseOrdersReport", acNormal, , "[PurchaseOrders].[PONumber]= " & Me.PONumber, acWindowNormal

In the example above, I left the OpenArgs (last argument) blank and omitted the trailing comma. If there is a control for the PONumber on the form that is launching this report, it could be as simple as I showed here. But it doesn't work so easily for macros.
 

db-noob

Member
Local time
Today, 14:58
Joined
Oct 16, 2020
Messages
47
Your question about somehow conveying a number into the macro/VBA is FAR easier since in VBA you can perform substitution based on a form. Since you would probably RUN the VBA from a form, if the number is in a control on that form, it might be as simple as assigning the report for the full table but THEN using VBA to assert a report filter before (or as part of) opening it. The "convert Macro to VBA" action for the one that opens the report would certainly use the equivalent of a DoCmd.OpenReport for which one of the arguments is a filter that constrains the data being gathered for the report. This would surely be easily managed by building the string dynamically based on something on the form. Like a text box, for example.


The 4th argument is the filter, which might be implemented via

Code:
DoCmd.OpenReport "PurchaseOrdersReport", acNormal, , "[PurchaseOrders].[PONumber]= " & Me.PONumber, acWindowNormal

In the example above, I left the OpenArgs (last argument) blank and omitted the trailing comma. If there is a control for the PONumber on the form that is launching this report, it could be as simple as I showed here. But it doesn't work so easily for macros.
Hello The_Doc_Man,

Thanks for the reply! I'll certainly give that a try tomorrow and post my progress.

Here's what Access gave me when I clicked that convert button.

Code:
Private Sub cmdPDF_Click()
On Error GoTo cmdPDF_Click_Err

    ' _AXL:<?xml version="1.0" encoding="UTF-16" standalone="no"?>
    ' <UserInterfaceMacro For="Command135" Event="OnClick" xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><Statements><Action Name="OpenForm"><Argument Name="FormNam
    ' _AXL:e">Main Menu</Argument></Action></Statements></UserInterfaceMacro>
    DoCmd.OpenReport "PurchaseOrdersReport", acViewReport, "", "[PurchaseOrders]![POrderNumber]=[PurchaseOrdersReport]![POrderNumber]", acNormal
    DoCmd.OutputTo acOutputReport, "PurchaseOrdersReport", "PDFFormat(*.pdf)", "", False, "", , acExportQualityPrint


cmdPDF_Click_Exit:
    Exit Sub

cmdPDF_Click_Err:
    MsgBox Error$
    Resume cmdPDF_Click_Exit

End Sub
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:58
Joined
Feb 28, 2001
Messages
27,001
Looks more or less right for the two DoCmd options. To transfer in the number, that part that looks like

Code:
"[PurchaseOrders]![POrderNumber]=[PurchaseOrdersReport]![POrderNumber]"

is the part you would edit as I suggested earlier. I don't think you need to edit anything else UNLESS you wanted to change the name of the output file. The file name would be the 2nd argument of the DoCmd.OutputTo, and the file type (and format) is specified by the third argument.

 

db-noob

Member
Local time
Today, 14:58
Joined
Oct 16, 2020
Messages
47
Looks more or less right for the two DoCmd options. To transfer in the number, that part that looks like

Code:
"[PurchaseOrders]![POrderNumber]=[PurchaseOrdersReport]![POrderNumber]"

is the part you would edit as I suggested earlier. I don't think you need to edit anything else UNLESS you wanted to change the name of the output file. The file name would be the 2nd argument of the DoCmd.OutputTo, and the file type (and format) is specified by the third argument.

Tested the following code and both seem to work splendidly! Will start implementing this for my other form(s). Thank you!

Code:
' Improved version of the PDF button
Private Sub cmdPO_PDF_Click() 'TESTING 1/8/2021
DoCmd.OpenReport "PurchaseOrdersReport", acViewReport, "", "[PurchaseOrders].[POrderNumber]= " & Me.[POrderNumber], acNormal
DoCmd.OutputTo acOutputReport, "PurchaseOrdersReport", "PDFFormat(*.pdf)", "", False, "", , acExportQualityPrint
End Sub

' Improved version of the Print button
Private Sub cmdPO_Print_Click() 'TESTING 1/8/2021
DoCmd.OpenReport "PurchaseOrdersReport", acViewReport, "", "[PurchaseOrders].[POrderNumber]= " & Me.[POrderNumber], acNormal
DoCmd.RunCommand acCmdPrint
End Sub

Is there anyway I could modify this so that the PDF will save as the POrderNumber? So, say, the Purchase Order Number is 60785, is it possible to have the file name be that number--and then the user chooses where they want to save it--instead of defaulting to "PurchaseOrdersReport.pdf"?

Would I have to include something like this?
Code:
Private Sub
Dim FileName as <?>
FileName = Me.POrderNumber

DoCmd.OpenReport "PurchaseOrdersReport", acViewReport,"","[PurchaseOrders].[POrderNumber]= " & Me.[POrderNumber], AcNormal
DoCmd.OpenReport acOutputReport, "PurchaseOrdersReport", "PDFFormat(*.pdf)", FileName, False, "",,acExportQualityPrint
End Sub
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:58
Joined
Feb 28, 2001
Messages
27,001
To save the PDF to include the number, if your number is available, it goes in the DoCmd.OpenReport 2nd argument. If the field is on the form as POrderNumber then

Code:
DoCmd.OpenReport acOutputReport, "POReport" & CStr(Me.POrderNumber), "PDFFormat(*.pdf)", .....

That would make the name POReport60785.PDF based on the specifics of your question, if that was what was in the form's field. Alternatively, if you wanted fixed-length digit sequences, then instead of the Cstr option you would use Format( Me.POrderNumber, "000000" ) to give you a six-digit number with leading zeros. In which case the name would be POReport060785.PDF (because of the leading zero added by the Format function.)
 

db-noob

Member
Local time
Today, 14:58
Joined
Oct 16, 2020
Messages
47
To save the PDF to include the number, if your number is available, it goes in the DoCmd.OpenReport 2nd argument. If the field is on the form as POrderNumber then

Code:
DoCmd.OpenReport acOutputReport, "POReport" & CStr(Me.POrderNumber), "PDFFormat(*.pdf)", .....

That would make the name POReport60785.PDF based on the specifics of your question, if that was what was in the form's field. Alternatively, if you wanted fixed-length digit sequences, then instead of the Cstr option you would use Format( Me.POrderNumber, "000000" ) to give you a six-digit number with leading zeros. In which case the name would be POReport060785.PDF (because of the leading zero added by the Format function.)

I tried this on another form, but I get this error:
error-2059.jpg


Code:
' Saves current Work Order as a PDF
' Improved version of the PDf macro button
Private Sub cmdWO_PDF_Click() 'Added 1/8/2021
DoCmd.OpenReport "WorkOrderRpt", acViewReport, "", "[WorkOrders].[WorkOrder#]= " & Me.[WorkOrder#], acNormal
'DoCmd.OutputTo acOutputReport, "WorkOrderRpt", "PDFFormat(*.pdf)", "", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "WorkOrderRpt" & CStr(Me.WorkOrder_), "PDFFormat(*.pdf)", "", False, "", , acExportQualityPrint
End Sub
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:58
Joined
Feb 28, 2001
Messages
27,001
That error (2059) says either (a) you spelled the name of something incorrectly without regard to whether or not the item exists, or (b) you were naming an object that doesn't exist because it was not created.

I note that you are using special characters like # and _ in names. This can be made to work but long term will make you regret such usage.
 

db-noob

Member
Local time
Today, 14:58
Joined
Oct 16, 2020
Messages
47
That error (2059) says either (a) you spelled the name of something incorrectly without regard to whether or not the item exists, or (b) you were naming an object that doesn't exist because it was not created.

I note that you are using special characters like # and _ in names. This can be made to work but long term will make you regret such usage.
I knew that using # as a shortcut for 'number' would come back to haunt me. Even when I changed that line to the following (brackets around WorkOrder#), I still got that error:

Code:
DoCmd.OutputTo acOutputReport, "WorkOrderRpt" & CStr(Me.[WorkOrder#]), "PDFFormat(*.pdf)", "", False, "", , acExportQualityPrint
 

db-noob

Member
Local time
Today, 14:58
Joined
Oct 16, 2020
Messages
47
Tried the same thing with my Purchase Orders form and that didn't work either. I suppose I can cut my losses and just have it output to a folder on the shared server with the field as the name of the file.

The primary problem was addressed, so thank you @The_Doc_Man !
 

Users who are viewing this thread

Top Bottom