passing record from one form to a subform of another form

[Bing Li]

I have a form - formA with a command button to open another form - FromB.
FromB contains a subform. When FromB opens I would like the subform of FromB
to open a new record with the same information entered by user from the
formA.

How should I go about this?

Thank you.

 

[Damian S]

Hi Bing Li,

Have a BeforeInsert event on the Form B subform so that once they type the
first character in a new record in your Form B subform it will grab the
information that you want, eg:

me.FIELDNAME = forms!formA.FIELDNAME

Of course, if you just want to DISPLAY the information rather than copy it
you shouldn't bind your FormB field to a database field. Lastly, it's poor
database design to store identical information in multiple tables... If you
are grabbing an ID to relate the data that's fine - if you are grabbing
identical data and storing it in two different places you should consider
whether you really need to do that rather than just linking the existing data
through a Primary Key/Foreign Key pair.

Hope that helps.

Damian.

 

[Bing Li]

Hi Damian,

Thanks for your help, it work perfectly. I am just passing the ID so that I
can link the records between the forms.

I now have another question: Once FormB is opened, I would like to
close/hide FormA. If I tell my command button which opens FormB to close
FormA once B is opened, I will get an error message with the BeforInsert
event saying FormA can not be found. So I have to keep FormA open until
record on fromB is saved. I am thinking of putting a close formA command in
the AfterUpdate event in FromB but I am not sure how to do it. And also is
this the best way to do this? Sorry about the silly questions this is my
very first Access project.

Thank you

Bing

 

[Damian S]

Hi again Bing,

If they are linked by an ID field, if you include your formB as a subform of
FormA with the field linking set properly (Link Child Field, Link Master
Field) your problems will all go away. Access will then handle the whole
linking issue and you won't need the beforeinsert code. This is generally a
better way to go about it, although there are times having a popup form is
also desirable.

If you really need/want the formB in a separate form, you could consider
making it popup/modal so that the user can't click onto anything else until
they finish on FormB.

Damian.

 

[Bing Li]

Thank you Damian, this helps me a lot!

Hi again Bing,

If they are linked by an ID field, if you include your formB as a subform
of
FormA with the field linking set properly (Link Child Field, Link Master
Field) your problems will all go away. Access will then handle the whole
linking issue and you won't need the beforeinsert code. This is generally
a
better way to go about it, although there are times having a popup form is
also desirable.

If you really need/want the formB in a separate form, you could consider
making it popup/modal so that the user can't click onto anything else
until
they finish on FormB.

Damian.