Forms and SubForms

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

Guest

I have a key on my main form and wish it to go to the subform to be written
on the second table. Then I wish it to join up with the Date/Time element to
write to the second table. How do I do this? I know how to join the two
table elements in a query but how do I do it on a Form/Subform? Ideas?
 
In the subform control's properties, data tab, look at the Master/Child Link
fields property. You can select the field names there if you know what they
are or you can click the ellipsis (...) button and select your fields from
there.

Master refers to the parent form, Child refers to the subform.

Steve
 
Steve,

Thank you for your response but I guess I was not clear.

The subform will be used as an optional input form. I was going to use it
for comments. IF a comment is entered, I need to add a key that will be
comprised of data from the main form and the date/time the comment was
entered. I was thinking the subform needs to be linked somehow to the main
form but there is no key unless a comment is entered. How (or is it needed)
to link the two forms together? Clear as mud?
 
The subform will be used as an optional input form. I was going to use it
for comments. IF a comment is entered, I need to add a key that will be
comprised of data from the main form and the date/time the comment was
entered. I was thinking the subform needs to be linked somehow to the main
form but there is no key unless a comment is entered. How (or is it needed)
to link the two forms together? Clear as mud?
Click to expand...

That's exactly what Steve was suggesting. The Master/Child Link Field of the
subform does precisely that - takes the Primary Key value defined in the
Master Link field, the link to the mainform's table, and inserts it into the
related foreign key field using the Child Link Field. You do not need to have
a pre-existing child record; typing the first keystroke in the comments field
causes the linking ID to be automatically entered. If you have a date/time
field in the comments table with a default value of Now() it will similarly be
automatically filled in when the record is created.

John W. Vinson [MVP]
 
Steve / John,

Awesome! If it were in my limited power, I would pay each a million bucks!

Kent
 
OK, that works exactly how I wanted it to work. Now I have another question.
I have an option group in the main form that when any option is clicked, I
want the option chosen and the userid to be propogated in the comments table.
I had it working when everything was in the main form but now I cannot get
the syntax correct using VB code. How do you reference the subform when you
are in the main form?

Thanks for your kindness and ability to help!
 
OK, that works exactly how I wanted it to work. Now I have another question.
I have an option group in the main form that when any option is clicked, I
want the option chosen and the userid to be propogated in the comments table.
I had it working when everything was in the main form but now I cannot get
the syntax correct using VB code. How do you reference the subform when you
are in the main form?
Click to expand...

Don't do it that way!

IF - and it's a big if, storing data redundantly is pretty much *always* a bad
idea - you want both the ID and the option group value propagated to the
subform, just include both in the Master/Child link fields, separated by
semicolons.

Why do you feel that you need to store the option group value in both the main
table and the child table? What would happen if one or the other were edited,
so that they were different?

John W. Vinson [MVP]
 
John,

Thank you for the info. I haven't tried it yet but shall very quickly.

What I am doing is creating a historical table of comments. For Instance,
let's say agent A looks at this record and selects the option A. The next
day Agent B looks at the same record but now inventory is available and
selects option B. If I want to see all the comments tied to a single record,
could be 0 or 100, I need the agent who selected an option and the option
they selected along with the comment. Hope this makes it clear. If this
does not make sense, please let me know.
 
John,
I forgot to state I am not saving it on the Main Table, Just the Comments
table. I am using the Option Group as a trigger to put the signon and the
option code to the comments table. I have this information showing on the
main form but not being stored.

Hope this helps
 
John,

Thank you for the info. I haven't tried it yet but shall very quickly.

What I am doing is creating a historical table of comments. For Instance,
let's say agent A looks at this record and selects the option A. The next
day Agent B looks at the same record but now inventory is available and
selects option B. If I want to see all the comments tied to a single record,
could be 0 or 100, I need the agent who selected an option and the option
they selected along with the comment. Hope this makes it clear. If this
does not make sense, please let me know.
Click to expand...

In that case (given your added comment) put a Number field in the table upon
which the subform is based - Option let's say; and set the Master Link Field
to

AgentID;optOption

and the Child Link Field to

AgentID;Option

where optOption is the name of the option group control on the mainform, and
Option is the name of the field in the subform's recordsource.

John W. Vinson [MVP]
 
Back
Top