Passing Values Between Forms using OpenArgs

1
votes

*****EDITED

So I have a form titled "NewInvoice". This form edits a table "Invoice" which contains the following; Invoice Number, Customer, Order Date. In this form I have a button which opens a subform titled "InvoiceItem". This edits a table with the same name that contains Invoice Number, Item Code, Dimensions, Etc.

Now currently, my button has an event procedure as follows.

Private Sub CreateInvoiceItem_Click()

DoCmd.OpenForm "InvoiceItem", OpenArgs:="InvoiceNumber"

End Sub

(The reason I am using a button and opening the form in a separate window is because I have cascading combo boxes in the sub form that become broken when I insert the sub form into the parent form)

Now where I am having trouble is setting the Form Load command. What I would like to occur is that the InvoiceNumber which is filled out in the Parent form auto fills in the sub form when the button is clicked.

Private Sub Form_Load()

**** NEEDED CODE****

End Sub

2
formsms-accessautofillopenargs
There are several ways to accomplish this. The simplest approach is to build form/subform arrangement, no code is required. For other methods, what research have you done? When you develop code that has specific issue, post a question. – June7
Well i have looked around the web in a few places that have turned up solutions which I don't quite understand. The big thing for me is I do not just want to simply copy someone else code and have it work, I would like to know what tools to use and what code is actually doing so that I may apply it to other situations. So yes I could use a subform that directly connects, but this solution did not work for me as in the subform that is connected I have cascading combo boxes which I have coded. These in turn do not work when I try and use them as a subform in the main form. – jBry562
The comboboxes will work if the RowSource is properly structured SQL statement. Don't use the form name in the SQL statement of the second (dependent) combobox, just reference the name of the first (primary) combobox, like: SELECT fieldname FROM tablename WHERE ID = [combobox name];. This does assume both comboboxes are on the subform. Best to name data controls different from the fields they are bound to, like cbxID. – June7

2 Answers

0
votes

So try fixing the comboboxes as described in comment under question. Also, recommend code to requery the dependent combobox be in its GotFocus event. Keep in mind, cascading combobox with lookup alias will not work nice in continuous or datasheet form.

If you really want to pass value to independent form, the OpenArgs is a good approach.

Probably need to open the form to a new record row.

DoCmd.OpenForm "InvoiceItem", , , , acFormAdd, acDialog, Me!InvoiceNumber

Need code that makes sure the form is on a new record.

Private Sub Form_Load()
If Me.NewRecord Then Me!InvoiceNumber = Me.OpenArgs
End Sub
0
votes

I find that the best way to do this is to add a Public sub to the form that you're opening and then pass whatever parameters you need to this function when you open the form. So to do what you're looking to do add a function like this to the form that you're opening;

Public Sub SetUpForm(InvoiceNumber as Long)

    txtInvoiceNumber.Value = InvoiceNumber

End Sub

Where txtInvoiceNumber is the control on the form that you want to put the value into.

Then from your button;

DoCmd.OpenForm "InvoiceItem"
Forms!InvoiceItem.SetUpForm InvoiceNumber

This will pass your value for the invoice number to the control on the form that you're opening. This also gives you a lot more flexibility to control the process because you can pass more than one parameter to the sub and in the sub you can perform any number of tasks.