Pass values from one form to another (NOT a subform)

[tbrogdon]

I have two tbls:tblMyFirstTable (composite PK of 3 fields) and
tblMySecondTable (includes same composite key as fk and contains
details pertaining to the first table).

I have 2 forms: frmMyFirstForm (autoform of tblMyFirstTable with
subform deleted) and frmMySecondForm (autoform rendition of
tblMySecondTable) so they are not automatically linked.

frmMyFirstForm has 4 controls: a txtField and cboField1 and cboField2
(which together make up the composite PK) and cmdOpenForm.

frmMySecondForm has many fields including txtField, cboField1,
cboFIeld2. It also has cmdExit

I want the user to input data in frmMyFirstForm and click cmdOpenForm
which should open frmMySecondForm and populate fields of the same name
on frmMySecondForm but re-enter themselves for every entry the user
makes on formMySecondForm during this session until clicking cmdExit.
In other words if Field1, 2, and 3 are the PK in tblMyFirstTable, I
want those values automatically entered in tblMysecondTable from
frmMySecondForm after being input on frmMyFirstForm - and the user
will make MULTIPLE entries in frmMySecondForm all of which will need
those values from the 3 fields in frmMyFirstForm.

How do I do this? I've tried coding the cmdOpenForm:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMySecondForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

And the On_Open event of frmMySecondForm to:

DoCmd.GoToRecord , , acNewRec
Me!txtDate = Forms!ProductOperation!txtDate
Me!cboDept = Forms!ProductOperation!cboDept
Me!cboShift = Forms!ProductOperation!cboShift

This obviously (now) doesn't work and has led me down innumerable dead-
ends and is obviously (now) not enough code and probably not the
correct logic.

I am sure I don't know what I am doing with this and I wish this
wasn't so confusing. I think what makes it confusing is the composite
key but I HAVE to use the composite.

Thanks in advance,

Tim

 

[Jeanette Cunningham]

Hi tbrogdon,

The set up you describe with 2 tables is very often made easy for data entry
if you use a form and a subform.

the main form is based on MyFirstTable

the subform is based on MySecondTable

with the subform you set its default view (on the form's property sheet
under the Format tab) to continuous
users will be able to enter several records for the same record in the main
form.

Jeanette Cunningham

 

[tbrogdon]

Hi Jeanette,

I know that it is super simple doing it the other way (I've already
done it) - but I need to do it the way I described NOT with a subform.

Thanks,

Tim

 

[tbrogdon]

Make the DefaultValue properties of the controls on the second form...

=Forms!ProductOperation!txtDate
=Forms!ProductOperation!cboDept
=Forms!ProductOperation!cboShift- Hide quoted text -

Placed the above in the controls of the second form and each one of
these returns: Error#


Tim

 

[Rick Brandt]

Placed the above in the controls of the second form and each one of
these returns: Error#


Tim

Was the other form open when you did the test? As long as the form reference is
accurate and the form being referenced is open it should work.

And you did make those entries in the DefaultValue properties right?

 

[tbrogdon]

Was the other form open when you did the test? As long as the form reference is
accurate and the form being referenced is open it should work.

And you did make those entries in the DefaultValue properties right?

Hey Rick,

The other form wasn't open...but that appears to be ok as the main
issue was in my entries in the Default values. I typed one
incorrectly. Sorry for the hurried tone of my email...and what's more
that has got to be the simplest and most obvious solution.

Is this a routine thing to do? Are there any potential "hazards" that
I might need to look for with this design in the future? I ask only
because I am trying to learn.

Thank you very much...can't see the forest for the trees............

Tim

 

[Rick Brandt]

Hey Rick,

The other form wasn't open...but that appears to be ok as the main
issue was in my entries in the Default values. I typed one
incorrectly. Sorry for the hurried tone of my email...and what's more
that has got to be the simplest and most obvious solution.

Is this a routine thing to do? Are there any potential "hazards" that
I might need to look for with this design in the future? I ask only
because I am trying to learn.

Thank you very much...can't see the forest for the trees............

Tim

The biggest issues of doing this rather than using a "real" subform...

The second form can ONLY be used when the first form is opened.

If you open the first form, then open the second to a new record , then go back
to the first form and change records, the default values on the second will
still be for the record that was displayed when the second form was taken to the
new record position (not the record currently displayed on the first form).
This issue can be eliminated by making the second form modal.

 

[tbrogdon]

The biggest issues of doing this rather than using a "real" subform...

The second form can ONLY be used when the first form is opened.

If you open the first form, then open the second to a new record , then go back
to the first form and change records, the default values on the second will
still be for the record that was displayed when the second form was taken to the
new record position (not the record currently displayed on the first form).
This issue can be eliminated by making the second form modal.

That is exactly how the second form will be used. It will only be
accessed from a button on the first form which contains the default
values...and I will set it to modal so that the user has to -decide-
to leave the form.

It works great now - exactly the way I want.

Thank you,

Tim

 

[tbrogdon]

The biggest issues of doing this rather than using a "real" subform...

The second form can ONLY be used when the first form is opened.

If you open the first form, then open the second to a new record , then go back
to the first form and change records, the default values on the second will
still be for the record that was displayed when the second form was taken to the
new record position (not the record currently displayed on the first form).
This issue can be eliminated by making the second form modal.

Hey just thought I'd also add for anyone else who reads this that just
as you pointed out above...I am actually trying to force the user to
stay in the second form and not allow them to go back to the original
form at all while entering details. For my application it is critical
that they not be allowed back to the first form. I have a reset/undo
button on the first form in case they make an error there but once
the're on the second form I want to keep them from bouncing back and
forth because it will be mess like you point out.

 

[tbrogdon]

Rick,

I guess this is a good time to ask...how do I enter multiple records
in the second form? I can enter one record but even with
DoCmd.GoToRecord,,acNewRec attached after all values are entered for
the current record I still get an area that says: "You can't go to the
specified record."

Any thoughts?

Thanks,

Tim

 

[tbrogdon]

I can enter one record but even with

DoCmd.GoToRecord,,acNewRec attached after all values are entered for
the current record I still get an area that says: "You can't go to the
specified record."

So it looks like the values were entered in a new record from the
first form, the default values on the second form were updated from
the values in the first form, I entered data for a new record in the
second form but I can't proceed to another record on the second and
the data from the first record on the second form was not recorded.

Any thoughts? I have the OnOpen event of the second form set to
acNewRec.

Thanks,

Tim

 

[Rick Brandt]

I can enter one record but even with

So it looks like the values were entered in a new record from the
first form, the default values on the second form were updated from
the values in the first form, I entered data for a new record in the
second form but I can't proceed to another record on the second and
the data from the first record on the second form was not recorded.

Any thoughts? I have the OnOpen event of the second form set to
acNewRec.

Thanks,

Tim

If these tables have a one to many relationship with referential integrity
enforced AND the record on the first form is a new one that you just created,
then you have to save it before opening the second form. Otherwise the child
record created in the second form will not have a parent record to relate to
(since you haven't saved it yet). Just add a line to your code just before the
line that opens the second record...

If Me.Dirty Then Me.Dirty = False

 

[tbrogdon]

If these tables have a one to many relationship with referential integrity
enforced AND the record on the first form is a new one that you just created,
then you have to save it before opening the second form. Otherwise the child
record created in the second form will not have a parent record to relate to
(since you haven't saved it yet). Just add a line to your code just before the
line that opens the second record...

If Me.Dirty Then Me.Dirty = False

Hi Rick-

That worked perfectly. It appears that Me.Dirty is probably truly
handy. What exactly is it doing? What are some other common uses for
this? I'm thinking that I maybe able to make some controls either
available or unavailbale depending on the status of a criterion field
with Me.Dirty. Am I correct in thinking this?


Tim

 

[Rick Brandt]

Hi Rick-

That worked perfectly. It appears that Me.Dirty is probably truly
handy. What exactly is it doing? What are some other common uses for
this? I'm thinking that I maybe able to make some controls either
available or unavailbale depending on the status of a criterion field
with Me.Dirty. Am I correct in thinking this?

A form's Dirty property is true when it has unsaved data changes. Setting it to
False is merely a convenient way to force those changes to be saved. You could
have also used...

DoCmd.RunCommand acCmdSaveRecord

 

[tbrogdon]

A form's Dirty property is true when it has unsaved data changes. Setting it to
False is merely a convenient way to force those changes to be saved. You could
have also used...

DoCmd.RunCommand acCmdSaveRecord

And I could have done that before the DoCmd.GoToRecord,,acNewRec
command on the OnOpen event of the second form also I'm sure.

Thank you again Rick!

Tim