linked forms??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I have a main form with a primary key. I want to open
another form specifically for THAT key. The key field is
also on the 2nd form. I have a filter that basically says
to open the form where the two keys match. But what I
really want to do is assign the value from the main form
to the second form and add additional data.

What it's doing is bringing up the 2nd form blank. If I
enter data, it creates a new record. But, if I save the
first form and exit, then re-open it and access the 2nd
form, all the data is there and no new record is created.

I don't understand why it won't just transfer the data to
the 2nd form immediately, but does after I exit. Any
ideas? Thanks.
 
Hi all,

I have a main form with a primary key.
Click to expand...

nitpick: you have a Table with a primary key. You may be displaying
that on a form, but the form doesn't have a key.
I want to open
another form specifically for THAT key. The key field is
also on the 2nd form. I have a filter that basically says
to open the form where the two keys match. But what I
really want to do is assign the value from the main form
to the second form and add additional data.
Click to expand...

You can pass the value of the Key from the mainform in the OpenArgs
property of the OpenForm method; then on the second form, you can use
the form's Open event to set the Default property of the key field to
the Openargs value.
 
What is the proper syntax to identify the OpenArgs value?
How do I tell it to go look at the OpenForm method on the
other form?
 
betsy said:
What is the proper syntax to identify the OpenArgs value?
How do I tell it to go look at the OpenForm method on the
other form?
Click to expand...

See Help (you don't have A2K do you?) on the OpenForm command; the very
last parameter counts as OpenArgs. So to send the value:

DoCmd.OpenForm "your2ndForm", , , , , , theValue

In any form, there is a variable OpenArgs. It will be Null except when
you explicitly passed something to the form, like above.
 
Apparently this doesn't work for controls with an
autonumber format. Any ways around this?
 
Apparently this doesn't work for controls with an
autonumber format. Any ways around this?
Click to expand...

What are you trying to do with the autonumber? You should be able to
pass an Autonumber to the form, with syntax like

DoCmd.OpenForm "formname", OpenArgs:=Me!txtID

where txtID is the control displaying the autonumber value.

If you are trying to store the OpenArgs value passed while opening the
form into an Autonumber field on the second form - you CAN'T, and you
SHOULDN'T. An Autonumber makes a good surrogate Primary Key but it
cannot be edited, and cannot be used as a foreign key link. The second
form should be updating a Long Integer foreign key field, not an
autonumber.
 
This is so frustrating! I really envy your fluency in this
stuff.

Here's my code that is an event procedure on the main form
on the AfterUpdate property of field "X":

DoCmd.OpenForm "VAWAinfo", OpenArgs:=Me![Text29]

"VAWAinfo" is the 2nd form I want to open, [Text29] is the
control that I want to assign the value of the autonumber
stored in the PetitionerID field on the first form.

I'm getting an error message saying that it cannot find
the field [text29].I've tried with the full syntax to
identify the field on the 2nd form but that doesn't work
either.

I need these numbers to match because I want to make sure
the data entered on the VAWAinfo form is for the SAME
record as the Main form. I'm trying to avoid it creating a
new record, with a new ID# for this additional data on the
second form.

This is killing me!! Can you please tell me what I'm doing
wrong?????
 
This is so frustrating! I really envy your fluency in this
stuff.

Here's my code that is an event procedure on the main form
on the AfterUpdate property of field "X":

DoCmd.OpenForm "VAWAinfo", OpenArgs:=Me![Text29]

"VAWAinfo" is the 2nd form I want to open, [Text29] is the
control that I want to assign the value of the autonumber
stored in the PetitionerID field on the first form.
Click to expand...

Ah - sorry. You've got it backwards; I wasn't clear in my explanation.

In the OpenForm line you're still on the *first* form. You want to
determine the value that you want to pass to the second form, and set
OpenArgs to that value. So the OpenForm line should be

DoCmd.OpenForm "VAWAinfo", OpenArgs:=Me!PetitionerID

This will set OpenArgs to a text string containing the id - "318" for
instance.

Then in VAWAinfo's Open event you'ld have code like

Private Sub Form_Open(Cancel as Integer)
Me!Text29 = Me.OpenArgs
End Sub
I need these numbers to match because I want to make sure
the data entered on the VAWAinfo form is for the SAME
record as the Main form. I'm trying to avoid it creating a
new record, with a new ID# for this additional data on the
second form.
Click to expand...

Have you considered making VAWAinfo a Subform of the main form? No
code needed at all!
 
Well, that worked...sort of. It opened the 2nd form fine
and [text29] field does have the value from the first
form...BUT it opened the form for ALL the records and gave
them all the same value, i.e, on the first form the
current record has ID # 12 for John Doe, open the 2nd
form, ID # is still 12 for John Doe, but also for Jane
Smith, etc... who may not need to have info from the
Second form. Why can't it just filter so that the 2nd form
is opened ONLY for the current record and transfers the ID
number?
-----Original Message-----
This is so frustrating! I really envy your fluency in this
stuff.

Here's my code that is an event procedure on the main form
on the AfterUpdate property of field "X":

DoCmd.OpenForm "VAWAinfo", OpenArgs:=Me![Text29]

"VAWAinfo" is the 2nd form I want to open, [Text29] is the
control that I want to assign the value of the autonumber
stored in the PetitionerID field on the first form.
Click to expand...

Ah - sorry. You've got it backwards; I wasn't clear in my explanation.

In the OpenForm line you're still on the *first* form. You want to
determine the value that you want to pass to the second form, and set
OpenArgs to that value. So the OpenForm line should be

DoCmd.OpenForm "VAWAinfo", OpenArgs:=Me!PetitionerID

This will set OpenArgs to a text string containing the id - "318" for
instance.

Then in VAWAinfo's Open event you'ld have code like

Private Sub Form_Open(Cancel as Integer)
Me!Text29 = Me.OpenArgs
End Sub
I need these numbers to match because I want to make sure
the data entered on the VAWAinfo form is for the SAME
record as the Main form. I'm trying to avoid it creating a
new record, with a new ID# for this additional data on the
second form.
Click to expand...

Have you considered making VAWAinfo a Subform of the main form? No
code needed at all!


.
Click to expand...
 
Well, that worked...sort of. It opened the 2nd form fine
and [text29] field does have the value from the first
form...BUT it opened the form for ALL the records and gave
them all the same value, i.e, on the first form the
current record has ID # 12 for John Doe, open the 2nd
form, ID # is still 12 for John Doe, but also for Jane
Smith, etc... who may not need to have info from the
Second form. Why can't it just filter so that the 2nd form
is opened ONLY for the current record and transfers the ID
number?
Click to expand...

It can. But that's not what you asked.

I'm not sure I understand. Do you want to open the second form to an
existing record and update a field in the second form's table to the
ID from the first form? Or do you want to open to a new, blank record?
 
Sorry for being so unclear. Here's what my problem really
is. I have a ton of data and it won't all fit on 1 form.
So, I want to categorize the data onto multiple forms for
the SAME record, so that when data is entered on any form,
the record is updated. I want to open these other forms
when certain conditions are met, or when a button is
clicked. All the fields on all the forms are in the main
table. I thought that the best way to make sure that the
other forms were for the same record, was to have the ID
be on all the forms. My intention was to open the other
forms and filter them so that they correspond ONLY to the
record that was current on the main form.

Over the weekend I also tried using the Tab Controls to
categorize the data, which is fine, but I couldn't use
some of the DoCmd. methods to automatically switch to
other pages (I guess the Tabs are not the same as
pagebreaks).

These forms are meant to be data entry forms for thousands
of records, so I'm trying to make it as user friendly and
easy to read as possible.
-----Original Message-----
Well, that worked...sort of. It opened the 2nd form fine
and [text29] field does have the value from the first
form...BUT it opened the form for ALL the records and gave
them all the same value, i.e, on the first form the
current record has ID # 12 for John Doe, open the 2nd
form, ID # is still 12 for John Doe, but also for Jane
Smith, etc... who may not need to have info from the
Second form. Why can't it just filter so that the 2nd form
is opened ONLY for the current record and transfers the ID
number?
Click to expand...

It can. But that's not what you asked.

I'm not sure I understand. Do you want to open the second form to an
existing record and update a field in the second form's table to the
ID from the first form? Or do you want to open to a new, blank record?



.
Click to expand...
 
Sorry for being so unclear. Here's what my problem really
is. I have a ton of data and it won't all fit on 1 form.
Click to expand...

Then... speaking frankly... your tables are incorrectly structured.

I've needed as many as sixty fields in a table... twice in the past
twenty years of database development work. (I've *used* more, under
protest, but never needed more).

Rather than fighting with Forms, normalize your data so that instead
of having one to many relationships embedded in each record, you have
one to many relationships between tables.

I'll answer the questions as asked because I understand that you may
not be in a position to Do The Right Thing, but I'd really recommend
it.
So, I want to categorize the data onto multiple forms for
the SAME record, so that when data is entered on any form,
the record is updated. I want to open these other forms
when certain conditions are met, or when a button is
clicked. All the fields on all the forms are in the main
table. I thought that the best way to make sure that the
other forms were for the same record, was to have the ID
be on all the forms. My intention was to open the other
forms and filter them so that they correspond ONLY to the
record that was current on the main form.
Click to expand...

In that case, just use the Filter property of the OpenForm method (see
the online help in VBA for OpenForm and follow the link for Filter).
Be sure that the ID is in a textbox on all the forms, and fill it in
from the OpenArgs value that you pass.
Over the weekend I also tried using the Tab Controls to
categorize the data, which is fine, but I couldn't use
some of the DoCmd. methods to automatically switch to
other pages (I guess the Tabs are not the same as
pagebreaks).
Click to expand...

You can move to a different tab by either setting the Page property of
the tab control in code, or by setting the focus to a control on the
desired tab page.
 
-----Original Message-----
Then... speaking frankly... your tables are incorrectly structured.

I've needed as many as sixty fields in a table... twice in the past
twenty years of database development work. (I've *used* more, under
protest, but never needed more).
Click to expand...

So, how did you or would you, represent these 60+ fields
on a form(s) for data entry purposes?
Rather than fighting with Forms, normalize your data so that instead
of having one to many relationships embedded in each record, you have
one to many relationships between tables.
Click to expand...

I'm not sure what you mean by "normalize". I don't think I
have 1 to many relationships within the record. What I
thought I did, or at least tried to do was have a 1 to
many relationship between the Main table (parent) and
another table (kid) b/c a parent can have more than 1
child.

Does adding new forms for the same record change the
relationship?

Are you suggesting I split up the data into other smaller
tables?
 
Are you suggesting I split up the data into other smaller
tables?
Click to expand...

Yes. Without knowing what the data is, or for that matter how wide
your tables actually are, I can't say that this is necessary nor how
to do it... but if you have more than sixty fields in a single table,
I'd be VERY interested to see it. I know of very few entities which
have over sixty independent, atomic attributes, and I'd be curious to
see one!
 
Back
Top