Setting a value on a form to a field on a subform

[Bec_FS]

I have created a form which contains the key field, on this form is a button
that calls up another form (one-to-many). When I am entering data, on the
main form with the key field on it, and I push the button to call up the
subform, I would like it to place the value (key field) of the main form,
onto the subform I just called up from the button, so that I don't have to
enter the number that it is linked to! Hope this makes sense!

 

[Richard Hollenbeck]

Bec_FS,

I'm not an expert, but since I've received so much help from this and other
Access newsgroups, I ought to atempt, once in a while, to give some help
back. If this helps, great! If not, please consider the source and my good
intentions:

Open the parent form in design view then open the properties box. If you
then highlight the subform (I don't know if I'm saying that correctly) and
click on the "All" tab in the properties window, the first four options
should be Name, Source Object, Link Child Fields, and Link Master Fields. I
think you should be able to experiment with the third and fourth options
(Link Child Fields and Link Master Fields) and solve your problem. I hope
an expert or MVP comes behind me and either says it better than I just tried
to do or confirms that I said it right. Please tell me if this works.

Rich Hollenbeck

 

[Bec_FS]

I really appreciate you responding, I probably was not clear, but the subform
is not part of the main form. I am calling the subform up with a button. I
then want it to automatically enter the key field from the main form into my
subform so that I can enter new data for that site.

Thanks for trying!!

 

[Richard Hollenbeck]

I have had some experience opening forms based on data in another form. I
don't think it is a subform though. I have had little luck pushing data
into the new form, but I have found it pretty easy to have the second form
"pull" the data from the first form if it's still open. For example, in the
second form, I might try something like:

Private sub Form_Load() 'in the second form
txtSomeTextBoxForKeyFieldData = Forms!frmFirstForm!txtKeyField 'or
whatever
End Sub

That usually works for me.

Even better, you could set the WHERE condition on the second form
DoCmd.OpenForm from the first form:

DoCmd.openForm "frmSecondForm", , stLinkCriteria

where "stLinkCriteria" is a string variable filtering the second form to the
key field of the first form.

I sure hope this helps. I've received so much help from the selfless MVPs
and others that I have to try to help in return. If this doesn't help,
please disregard. I'm only trying to help.

Rich

 

[Bec_FS]

Thank you, I did try that code and I did get it to work, but it only did it
on the first record that I wanted to enter in the table. When I tried a
second entry I got the message "You cannot add or change a record because a
related record is required in T_SiteVisitInformation", which makes since,
because that code executes on open form, which only happens once. I will try
to put it on the field itself. Unless you may have other suggestions.

 

[Richard Hollenbeck]

Bec_FS,

Is your second form bound to a table or query? Oftentimes, whenever I run
into situations like that, I just make the whole second form "Unbound" and
then pull in exactly the data I need from the various tables on Load. Then
when I'm ready to save the record I do UPDATE or INSERT INTO queries in the
second form's module, as appropriate, to get the right data back into the
affected tables--usually connected with a cmdSaveRecord button or something
like that. That gives me total control over what data is going to what
table and when. I've found that Bound forms can sometimes be a little
stubborn in the way you describe.

Rich

. . .When I tried a
second entry I got the message "You cannot add or change a record because a
related record is required in T_SiteVisitInformation", which makes since,

.. . . .

 

[Richard Hollenbeck]

In the same way, when I need to create a new record in an unbound form I
sometimes put a cmdNewRecord button on the form which, depending on the
situation, either gathers data from input boxes or from a third pop-up form
off the second form, then does an INSERT INTO query. Maybe it's not the
most elegant way to go, but it seems to work okay.

Rich