Passing a parameter from a subform to a new form?

T

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.
 
S

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
 
T

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?

SteveM said:
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 said:
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.
 
S

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

Ted M H said:
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?

SteveM said:
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 said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top