1

*****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

jBry562
  • 25
  • 1
  • 8
  • 1
    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 Apr 25 '17 at 03:11
  • 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 Apr 25 '17 at 03:20
  • 1
    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 Apr 25 '17 at 04:50

2 Answers2

0

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
June7
  • 19,874
  • 8
  • 24
  • 34
  • I apologize for being silent for so long. Currently finishing up my last semester and a lot going on at work. I promise I am going to look into this when I get back onto this project! Thank you so much for your input! – jBry562 May 07 '17 at 02:27
  • Thx.. I was trying to do same on form open ... and was getting Error 2448 "cannot assign a value" –  Feb 15 '22 at 05:47
0

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.

jhTuppeny
  • 820
  • 1
  • 11
  • 16
  • I apologize for being silent for so long. Currently finishing up my last semester and a lot going on at work. I promise I am going to look into this when I get back onto this project! Thank you so much for your input! – jBry562 May 07 '17 at 02:28