Passing a parameter from a subform to a new form?

[Ted M H]

I’ve got a primary form that shows a single customer record from the customer
table. I have a sub form that shows all the shipping orders for that
customer in the shipping order header table. I use a simple query on the sub
form to select shipping order records where the customer number in the
shipping order header table equals the customer number in the primary form.
Let’s call this FormSet1.

I need to add another set of forms, one that will list a single shipping
order‘s header data from the shipping order header table and the other to
list all of the product details associated with that shipping order. The
product details are in the shipping order details table. Let’s call this
FormSet2

I want the user to double-click on the shipping order number in one of the
shipping order records listed in the sub form in FormSet1 and have Access
open FormSet2 with both the primary form (shipping order header) and sub form
(associated product details) already populated with the shipping order number
that is double-clicked on in the sub form in FormSet1.

I can get FormSet2 to work great as long as I’m willing to manually enter
the shipping order number as a parameter. I want the double-click event in
the sub form in FormSet1 to run a simple macro that opens FormSet2, but
somehow the shipping order number that the user double-clicked on is getting
lost on the way to FormSet2.

Any idea how I can get my macro to grab the shipping order number that’s in
the sub form when my user double clicks it, and then pass that shipping order
number to my FormSet2?

Many thanks.

 

[SteveM]

In your OnDblClick event procedure, place the following code:

DoCmd.OpenForm "FormSet2",,,"[shipping order number] = " & Me.[shipping
order number],,acDialog

The acDialog at the end, opens the form on top and forces the user to close
it before continuing to edit the main form.

Change 'shipping order number' to the name of your respective fields.

Steve

 

[Ted M H]

Hi Steve,

Thanks for the quick reply. I should have mentioned that I am pretty much
clueless with VBA coding, and I've never written an event procedure. I use
simple macros on events. I tried to create an event procedure in place of my
macro and it crashed when I double-clicked. I'm sure I've keyed something
wrong.

Is there a way to accomplish this with macros so I don't have to learn VBA
and event procedures to get my problem solved?

In your OnDblClick event procedure, place the following code:

DoCmd.OpenForm "FormSet2",,,"[shipping order number] = " & Me.[shipping
order number],,acDialog

The acDialog at the end, opens the form on top and forces the user to close
it before continuing to edit the main form.

Change 'shipping order number' to the name of your respective fields.

Steve

I’ve got a primary form that shows a single customer record from the customer
table. I have a sub form that shows all the shipping orders for that
customer in the shipping order header table. I use a simple query on the sub
form to select shipping order records where the customer number in the
shipping order header table equals the customer number in the primary form.
Let’s call this FormSet1.

I need to add another set of forms, one that will list a single shipping
order‘s header data from the shipping order header table and the other to
list all of the product details associated with that shipping order. The
product details are in the shipping order details table. Let’s call this
FormSet2

I want the user to double-click on the shipping order number in one of the
shipping order records listed in the sub form in FormSet1 and have Access
open FormSet2 with both the primary form (shipping order header) and sub form
(associated product details) already populated with the shipping order number
that is double-clicked on in the sub form in FormSet1.

I can get FormSet2 to work great as long as I’m willing to manually enter
the shipping order number as a parameter. I want the double-click event in
the sub form in FormSet1 to run a simple macro that opens FormSet2, but
somehow the shipping order number that the user double-clicked on is getting
lost on the way to FormSet2.

Any idea how I can get my macro to grab the shipping order number that’s in
the sub form when my user double clicks it, and then pass that shipping order
number to my FormSet2?

Many thanks.

 

[SteveM]

I never use macros but here's how to do it:

Go to the OnDblClick property and click the ellipsis button (...)
Choose Macro Builder, give your macro a name in the dialog that appears
Choose OpenForm as the Action
Enter the name of the form to open (Form Name)
Choose Dialog in (Window Mode)
In the (Where Condition) type:
[shipping order number] = Forms!Formset1!SubformName.Form.[shipping order
number]

Actually, using the event procedure is much easier. Try this:
Go to the OnDblClick property and choose [Event Procedure] then click the
ellipsis button (...)
Now past this:
DoCmd.OpenForm "FormSet2",,,"[shipping order number] = " & Me.[shipping
order number],,acDialog

....between the Sub and End Sub lines of code. That's it!

In both cases you need to change FormSet2 to the actual name of the form you
want to open and [shipping order number] needs to be changed to name of your
matching fields.

Steve

Hi Steve,

Thanks for the quick reply. I should have mentioned that I am pretty much
clueless with VBA coding, and I've never written an event procedure. I use
simple macros on events. I tried to create an event procedure in place of my
macro and it crashed when I double-clicked. I'm sure I've keyed something
wrong.

Is there a way to accomplish this with macros so I don't have to learn VBA
and event procedures to get my problem solved?

In your OnDblClick event procedure, place the following code:

DoCmd.OpenForm "FormSet2",,,"[shipping order number] = " & Me.[shipping
order number],,acDialog

The acDialog at the end, opens the form on top and forces the user to close
it before continuing to edit the main form.

Change 'shipping order number' to the name of your respective fields.

Steve

I’ve got a primary form that shows a single customer record from the customer
table. I have a sub form that shows all the shipping orders for that
customer in the shipping order header table. I use a simple query on the sub
form to select shipping order records where the customer number in the
shipping order header table equals the customer number in the primary form.
Let’s call this FormSet1.

I need to add another set of forms, one that will list a single shipping
order‘s header data from the shipping order header table and the other to
list all of the product details associated with that shipping order. The
product details are in the shipping order details table. Let’s call this
FormSet2

I want the user to double-click on the shipping order number in one of the
shipping order records listed in the sub form in FormSet1 and have Access
open FormSet2 with both the primary form (shipping order header) and sub form
(associated product details) already populated with the shipping order number
that is double-clicked on in the sub form in FormSet1.

I can get FormSet2 to work great as long as I’m willing to manually enter
the shipping order number as a parameter. I want the double-click event in
the sub form in FormSet1 to run a simple macro that opens FormSet2, but
somehow the shipping order number that the user double-clicked on is getting
lost on the way to FormSet2.

Any idea how I can get my macro to grab the shipping order number that’s in
the sub form when my user double clicks it, and then pass that shipping order
number to my FormSet2?

Many thanks.