Use Button To Call/Open 2nd Form & Pass Variable

[maduckie123]

Hi,

There are myriads of posts here and in Google groups about how to pass
variables between an opened and a called form. However, several contradict
others and proclaim their method to be "the way" to do this. I've tried many
different suggestions I've found on this board and elsewhere online -- all to
no avail. In a nutshell, here is what I need to happen:

Button on opened multi-record Form named "MainForm" will call a second single-
record Form named "ChildForm". Since the user can select/highlight any
single record from MainForm, when the button is pressed, the MainForm must be
smart enough to recognize which record has been selected and one of the data
elements (which are unique) must be passed to the ChildForm so that a query
will fire in ChildForm based on the unique criteria selected/passed on/from
the MainForm. Nothing needs to be passed from the ChildForm to the MainForm;
ChildForm is for look-up (read-only) purposes only.

I need advice on what events to put where and the respective arugments/syntax
that will make this work. Thanks in advance for your help.....

 

[Dirk Goldgar]

Hi,

There are myriads of posts here and in Google groups about how to pass
variables between an opened and a called form. However, several
contradict
others and proclaim their method to be "the way" to do this.

That could be because, give the power of Access, there are often several
good ways to do something, as well as some ways that work but have
drawbacks. Or it could be because there can be different functional
requirements in what initially seems to be the same process.

I've tried many
different suggestions I've found on this board and elsewhere online -- all
to
no avail. In a nutshell, here is what I need to happen:

Button on opened multi-record Form named "MainForm" will call a second
single-
record Form named "ChildForm". Since the user can select/highlight any
single record from MainForm, when the button is pressed, the MainForm must
be
smart enough to recognize which record has been selected and one of the
data
elements (which are unique) must be passed to the ChildForm so that a
query
will fire in ChildForm based on the unique criteria selected/passed
on/from
the MainForm. Nothing needs to be passed from the ChildForm to the
MainForm;
ChildForm is for look-up (read-only) purposes only.

I need advice on what events to put where and the respective
arugments/syntax
that will make this work. Thanks in advance for your help.....

We need a little clarification, I think. It's one thing to pass a value to
a form when you open it, so that code on the opened form can retrieve and
process that value. It's another to open a form filtered by a particular
value specified by the calling routine. Which of these are you trying to
do?

 

[maduckie123]

Thanks for taking the time to reply.

Given your two choices, I'd say that the first scenario is what I'm
attempting. All the called form need do is to return a record from a
different table (child table) based on the single value passed to the called
form from a single field in the record that was selected at the time the user
presses the button on the main form. The query criteria will be a single,
distinct value having a single correlating record in the child table.

Hi,

There are myriads of posts here and in Google groups about how to pass
variables between an opened and a called form. However, several
contradict
others and proclaim their method to be "the way" to do this.

That could be because, give the power of Access, there are often several
good ways to do something, as well as some ways that work but have
drawbacks. Or it could be because there can be different functional
requirements in what initially seems to be the same process.

I've tried many
different suggestions I've found on this board and elsewhere online -- all

[quoted text clipped - 19 lines]

arugments/syntax
that will make this work. Thanks in advance for your help.....

We need a little clarification, I think. It's one thing to pass a value to
a form when you open it, so that code on the opened form can retrieve and
process that value. It's another to open a form filtered by a particular
value specified by the calling routine. Which of these are you trying to
do?

 

[Dirk Goldgar]

Thanks for taking the time to reply.

Given your two choices, I'd say that the first scenario is what I'm
attempting. All the called form need do is to return a record from a
different table (child table) based on the single value passed to the
called
form from a single field in the record that was selected at the time the
user
presses the button on the main form. The query criteria will be a single,
distinct value having a single correlating record in the child table.

And yet, that decription seems to me to match the second scenario I
described. So maybe we're not quite on the same page with this. Let me
describe the scenario as I understand it:

1. User is viewing a form that displays one or more "parent" records. Each
parent record contains a field that relates to a record in a child table.

2. The user clicks a button to open another form that will display the child
record, if any, corresponding to the current parent record.

3. When the user is done with the child form, he simply closes it and
returns to the parent form.

If that's the scenario you're trying to implement, then this is exactly the
case I was referring to when I referred to "open[ing] a form filtered by a
particular value specified by the calling routine."

Suppose I have a table "tblParent" with fields "ParentID" (primary key) and
"ChildID", and a related table "tblChild", with field "ChildID" (primary
key). Suppose further that I have forms "frmParent" and "frmChild" based
respectively on these tables. On frmParent, we put a button named
"cmdOpenChild". The code for that button would be as simple as this:

'----- start of example code -----
Private Sub cmdOpen_Child_Click()

DoCmd.OpenForm "frmChild", _
WhereCondition:="ChildID = " & Me.ChildID

End Sub
'----- end of example code -----

That's assuming that the field ChildID is numeric; for example, an
autonumber in tblChild and a Long Integer in tblParent. If the field is a
text field, on the other hand, the where-condition must enclose it in
quotes, so the OpenForm line might look like this:

DoCmd.OpenForm "frmChild", _
WhereCondition:="ChildID = " & Chr(34) & Me.ChildID & Chr(34)


That will work for a text field, unless the text field contains the
double-quote character represented above by Chr(34). If that happens to be
the case, we have to do a teensy bit more work, but I won't bother posting
that until you tell me it's necessary.

The above seems very simple and straightforward to me, so maybe I've missed
something. And, of course, if this is not the scenario you had in mind,
then we may have to take a different approach.

 

[maduckie123 via AccessMonster.com]

Thanks for your help; it works now.

Thanks for taking the time to reply.

[quoted text clipped - 6 lines]

presses the button on the main form. The query criteria will be a single,
distinct value having a single correlating record in the child table.

And yet, that decription seems to me to match the second scenario I
described. So maybe we're not quite on the same page with this. Let me
describe the scenario as I understand it:

1. User is viewing a form that displays one or more "parent" records. Each
parent record contains a field that relates to a record in a child table.

2. The user clicks a button to open another form that will display the child
record, if any, corresponding to the current parent record.

3. When the user is done with the child form, he simply closes it and
returns to the parent form.

If that's the scenario you're trying to implement, then this is exactly the
case I was referring to when I referred to "open[ing] a form filtered by a
particular value specified by the calling routine."

Suppose I have a table "tblParent" with fields "ParentID" (primary key) and
"ChildID", and a related table "tblChild", with field "ChildID" (primary
key). Suppose further that I have forms "frmParent" and "frmChild" based
respectively on these tables. On frmParent, we put a button named
"cmdOpenChild". The code for that button would be as simple as this:

'----- start of example code -----
Private Sub cmdOpen_Child_Click()

DoCmd.OpenForm "frmChild", _
WhereCondition:="ChildID = " & Me.ChildID

End Sub
'----- end of example code -----

That's assuming that the field ChildID is numeric; for example, an
autonumber in tblChild and a Long Integer in tblParent. If the field is a
text field, on the other hand, the where-condition must enclose it in
quotes, so the OpenForm line might look like this:

DoCmd.OpenForm "frmChild", _
WhereCondition:="ChildID = " & Chr(34) & Me.ChildID & Chr(34)

That will work for a text field, unless the text field contains the
double-quote character represented above by Chr(34). If that happens to be
the case, we have to do a teensy bit more work, but I won't bother posting
that until you tell me it's necessary.

The above seems very simple and straightforward to me, so maybe I've missed
something. And, of course, if this is not the scenario you had in mind,
then we may have to take a different approach.