0

I am pretty new to VBA and i'm trying to create a button on a invoice creation form that will print the invoice. The On Click event of the button should open a report and assign the Invoice ID on the form to the openargs property.

Private Sub cmdPrint_Click()
Dim InvoiceNum As Integer

If Me.Dirty Then    'Save any edits.
    Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
    MsgBox "Select a record to print"
Else
    InvoiceNum = Me.[Invoice Number]
    DoCmd.OpenReport "Invoice", acViewPreview, , , , InvoiceNum
End If
End Sub

Then when the report opens it runs the code:

Dim InvoiceNum As Integer
InvoiceNum = Me.OpenArgs

When I click the button on the form however, the Enter Parameter Box appears. Whats strange is that the the text in this box is the value of the Invoice number I want to assign to the OpenArgs Property. I cant work out how to make the value of OpenArgs the invoice number from the form.

I'm not sure what I have done wrong and can't seem to find the answer anywhere. Any help would be appreciated.

JRichardson
  • 1
  • 1
  • It has to work so something else is going on. 1: Look for code in the OnLoad and other events raised when opening the report. 2: Try renaming the variables. 3: Try using: InvoiceNum = Me![Invoice Number].Value – Gustav Apr 06 '15 at 07:33

3 Answers3

0

Dim InvoiceNum As Integer InvoiceNum = Me.OpenArgs

Something looks to be off when you open the report you are declaring a variable called InvoiceNum and setting the value of that variable to Open Args. I'm not sure what you intend to do with that variable. If your objective is to filter the invoice by the Invoice Number then you should try something like this...

me.filter = "[Invoice Number]= " & Me.OpenArgs

Alternatively What I think is happening is that you have an unbound control on your form whose control Source is InvoiceNum and you are trying to set the value of that to OpenArgs. because it is an unbound control whenever the form opens it will give you a parameter box because InvoiceNum is not a field on the table. If I am correct and this is what is happening you can try setting the Control Source of that directly to the Open Args by using =[OpenArgs] in the Control source and removing all the code from the OnLoad event of the form.

izzymo
  • 916
  • 1
  • 10
  • 14
0

Are you positive that you have a field on your first form named [Invoice Number]? If so, put a breakpoint on the DoCmd.OpenReport line of code and either use your Immediate window or mouse hover to determine what the value of InvoiceNum is at that point. If InvoiceNum is anything other than what you expect, then try renaming the field [Invoice Number] (personally, I never use spaces in a field name, it only leads to problems) to something else and adjust your code accordingly.

If InvoiceNum looks correct, put a breakpoint in your Invoice report on the OpenArgs statement and see what it thinks the value is at that point. It looks like your code is sound, but there are elements that we can't see that may be effecting your variable.

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
0

Thanks for your help guys.

I tinkered with the code a bit more and put break points in it. Turns out the problem wasn't with the OpenArgs like I thought but with how I used it in my Report. I changed the text field I was putting the ID in on the report to a label and used:

InvoiceNum = Me.OpenArgs
    me.[label].caption = InvoiceNum

I didn't add what I had originally to my question as I thought i had commented it out.

Works perfectly now. Thanks again for your help.

JRichardson
  • 1
  • 1