subform / form

[Guest]

I have a continuous subform with the field AD1 and I want it to have the
same value as a field ITEM1 on the parent form for all the subform records
associated with a record on the parent form. The HELP topic for Parent
Property states " You can enter this expression in a bound control on the
subform.

=Parent!OrderID " Where OrderID is on the Parent form.

Where do I enter this ? If I type " =Parent!ITEM1 " into the textbox on the
subform it becomes unbound and I get the message "Out of Memory" when I open
the main form. And I want the textbox to remain bound.

I tried using VB and got the Out of Memory again.

 

[Guest]

Hi, Rich.

Perhaps I'm missing something, but if you're adding detail records on a
subform and want the foreign key tying all the subform records to the main
form's underlying table, you don't need any code nor any formula for the
Parent at all. In fact, you can and should get rid of the control that holds
the foreign key from the subform.

If the subform is linked to the mainform through the LinkMasterField and
LinkChildField properties of the subform, the foreign key is added to each
subform record by Access.

Hope that helps.
Sprinks

 

[Guest]

Hi Sprinks,
Thanks for replying. I've tried using the Master/Child linking for those
fields but if a change is made in the parent form to Item1 then a new subform
record is created with that new value Item1 being placed in AD1 in a new
subform record. This leaves the original value of AD1 as an orphan record in
the subform table. The only way to view it is to change Item1 back. I have
other fields linking the main form to the subform.
I want to have the values change in the corresponding subform textboxes
anytime the main form combobox is changed. For each mainform record there
could be as many as 51 subform records. The forms work correctly together now
except if a wrong entry was selected for Item1 (main form) or the user wants
to change it then all the records in the subform that were already entered
are lost.
Is there a way to go into the underlying subform table and change all the
entries of AD1 for only those records where maybe a field with the PrimaryID
of the parent record is common ?
This seems like it would be a common problem with Main / Sub Forms. Maybe I
have something set up wrong. Someone tried to answer this for me a long
while back but I still have had no luck figuring what I am doing wrong. I
think it's because I need the subform to be continuous so there are many
associated records created.

 

[Guest]

Also, the primary key for the subform is made up of 3 fields. Date,
fgnAuthorID, WorkerID. Each record on the main form links the Date and
fgnAuthorID to the subform. The WorkerID is what makes each subform record
unique.

 

[Guest]

Hi, Rich.

My apologies, but I'm having trouble understanding your problem. What would
like to have happen if a change is made in the parent form to Item1? Do you
want the records that were attached to the old value of Item1 in the subform
to change their value to the new value of Item1? If so, you can set Cascade
Update in the Relationships window.

I'm a firm believer in keeping things as simple as possible (probably
revealing my own limitations as a programmer), and so I avoid compound
primary keys like the plague. I just haven't come across a case where a
compound key has a benefit over a simple AutoNumber key. If you are using
one to avoid duplicates of the combination of fields currently set as the
primary key, there's another way to do that--you can add a multi-field No
Duplicates index in Table design view.

Sorry I couldn't be more helpful. If this didn't answer your question, I
suggest you repost. Good luck.

Sprinks

 

[Guest]

Thanks for your interest. Yes, I do want the values to change without
creating new subforms when the main form Item# is changed. There are
actually Item1 to Item6 in one record. I'm violating the normalization rules
I read about but there will be so many records generated if I have one for
each Item and worker. I have test forms with the compound keys and single
primary keys but didn't try the AutoNumber yet. I will look for the Cascade
Update between the main and subform. Linking the parent/child fields for
Item1 thru 6 works great in populating the subform. The Item# is actually a
foreign primary key for a list of activities so in that respect I'm obeying
the normalization rules. The only place that I think I'm being redundant is
that I want the values stored once in the main form and they really need to
be with each subform record. I have a previous post on 10/6 that has more
about my setup.
I'll let you know how I do with the Cascade Update. Thanks again for your
interest.
Rich J

 

[Guest]

I have the solution for this now. It came from another posting dated 12/7
about a checkbox value on the main form being passed to all records in a
continuous subform. Thanks everyone.