More Subform Questions...

[Coleen]

I have a main form that I run from a query joined to three tables (to get
the related ID's to use in my form) Next I have a subform that is linked on
the Mainform by the Parent/child fields Emission_ID. This field is an
autonumber field in the Emissions table, and is related to the Test Vehicle
table as a number field using a look-up. The entry into the subform works
just fine, but it is NOT storing the Emission Id in the Test Vehicle table
when you enter a row in the subform. I'm not sure how else to handle this.
Any suggestions?

TIA,

Coleen

 

[strive4peace]

Is Emission_ID defined to be a Long Integer in the Test
Vehicle table?

Is Emission_ID ON the subform? It's visible property can be
False.

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[Coleen]

Yes to both...That is why I am SO confused!

Thanks for any help you can give me

 

[strive4peace]

Hi Coleen,

what is the NAME property for that control on both the
mainform and the subform?

What are the LinkMasterFields and LinkChildFields properties
for the subform control? Is there more than one controlname
specified for each one? Do all values in these properties
match up to the Name property for the corresponding controls
on each respective form?

What Indexes are set in the Test Vehicle table?

What is the RowSource for the Test Vehicle subform?

.... if it is based on a query (instead of the table
directly)... when you make a query from the subform
RowSource (if it is not saved already), can you add a record
to it? What value does Emmission_ID get, if any?

Is Emmission_ID visible in the subform while you are
developing so you can examine its values?

Is there a ValidationRule set for Emmission_ID either at the
form or table level?

Is Emmission_ID defined to be a lookup field in the table
design? If so, make this a textbox display. If it is a
combobox on your subform, change this to textbox. If the
value is filled through the Link Fields, the user has no
need to pick or change anything in this field.

I have more questions if you haven't found the solution

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[Coleen]

Hi Crystal, thanks for responding.

In answer to your questions, the Name property for the Main form is "Engine
swap data entry form" the Name Property for the subform is Emission subform"

The LinkMasterFields and LinkChildFields are Vehicle_ID for both. I have
changed it though - it was linked on the Emission_ID on both Child &
Parent - the reason is that originally when I set this up when I linked it
on the Vehicle_ID the subform did not scroll through the records in
conjunction with the main form using the Vehicle_ID, but it did using the
Emission_ID.

I did find the problem though (I think)...I had the Vehicle ID listed in the
Emission table AND the Emission_ID in the Vehicle table - so it was
redundant. I don't know exactly why it would not populate the Emission_ID
in the Vehicle table, but I absolutely could not get it to populate no
matter what I did using the subform. I solved the problem by completely
re-creating the form using the form wizard to join the three tables
(Vehicle, Registration and Emission) - AFTER I removed the Emission_ID from
the Vehicle table. Then the Vehicle_ID in the Emission table populated
correctly. Why it wouldn't using the subform is beyond me.

The Indexes in the Vehicle table were set so that the Emission_ID was
required, no dupes (It is a one-to-one relationship) There will never be
the same vehicle with more than one Emission Device (this isn't a smog check
table, it is for emission devices on assembled vehicles). the Row/Source
for the Subform was the Emission table. I originally did have it set up on
a query, but realized that the query included everything in the table and
was not linked to any other tables so I changed it to use the table
directly. Yes, I could add records to it (when I used the Emission_ID as
the link, but not when I used the Vehicle_ID.) Emission_ID in the Vehicle
table was never getting populated, and neither was the Vehicle_ID field in
the Emission table.

When I changed it to use a single form instead of a sub-form, the Vehicle_ID
in the Emission table was getting populated. That is how I caught that I
had the Emission_ID redundantly stored in the Vehicle table - if I have the
Vehicle_ID in the Emission table, I don't need the Emission_ID in the
Vehicle table, since I can always refer to the Emission table to get the
Vehicle_ID related to the Emission record in that table.

I did "Almost" solve the problem by setting the value of the Vehicle_ID
field in the Subform to =[Forms]![ENGINE SWAP DATA ENTRY FORM]![Vehicle_ID]
so that it would get the value of the Vehicle_ID from the main form. I
could see that it was being passed, but it was not saving it in the table -
which is why at that point I recreated the whole form as a single form.
Luckily I am very fast at formatting so it only took a couple of hours to
re-create.

Yes, there was a validation rule for the Emission_ID in the Vehicle table -
it was a number (Long Integer), required, indexed with no dupes. Yes it was
originally set up as a look-up field. I think that is where all my problems
started. I know better than to use look-ups in a table, but I changed it to
a look-up after the first try to see if I could get it to populate that
way - no luck! Now I know better - DON'T use look-ups in tables!

Thanks so very much for helping me with this - I did get it resolved, but
NOT by using a subform. I would really be interested in learning how to
correctly link the subform so that it will populate the foreign key in the
main table next time.

Thanks!

Coleen

 

[strive4peace]

Hi Coleen,

You're welcome I am glad you got it resolved. One thing
to remember is that forms just show us what is stored in
tables -- they do not store data themselves. The most
important part of a database to get right is the way that
the information is structured (broken down into tables) and
how those tables relate to each other.

Personally, this is how I see it:

One table needs to come first -- that would be the Vehicle
table. The Emission test table would then be a child table
(since you first need a vehicle on which to do a test), have
its own autonumberID (EmissionID) and also store the
VehicleID as a foreign key. I would not make the assumption
that there will NEVER be two emission records for one
vehicle...there will come a day it may happen...

If you ALSO have the EmissionID in the Vehicle table, this
is not right. Besides, we decided that the Vehicle table
was the parent table -- so putting a childID in the parent
table is pointless.

I believe the issues you actually had were with data
structure and relationships, not the form

Lookup fields in the table design do wreak all kinds of
havoc...best to avoid them

"learning how to correctly link the subform so that it will
populate the foreign key in the main table next time."

-- you can't do it that way ... you need to populate the
foreign key in the CHILD table...can a child come BEFORE its
parents? No, it cannot

Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

Hi Crystal, thanks for responding.

In answer to your questions, the Name property for the Main form is "Engine
swap data entry form" the Name Property for the subform is Emission subform"

The LinkMasterFields and LinkChildFields are Vehicle_ID for both. I have
changed it though - it was linked on the Emission_ID on both Child &
Parent - the reason is that originally when I set this up when I linked it
on the Vehicle_ID the subform did not scroll through the records in
conjunction with the main form using the Vehicle_ID, but it did using the
Emission_ID.

I did find the problem though (I think)...I had the Vehicle ID listed in the
Emission table AND the Emission_ID in the Vehicle table - so it was
redundant. I don't know exactly why it would not populate the Emission_ID
in the Vehicle table, but I absolutely could not get it to populate no
matter what I did using the subform. I solved the problem by completely
re-creating the form using the form wizard to join the three tables
(Vehicle, Registration and Emission) - AFTER I removed the Emission_ID from
the Vehicle table. Then the Vehicle_ID in the Emission table populated
correctly. Why it wouldn't using the subform is beyond me.

The Indexes in the Vehicle table were set so that the Emission_ID was
required, no dupes (It is a one-to-one relationship) There will never be
the same vehicle with more than one Emission Device (this isn't a smog check
table, it is for emission devices on assembled vehicles). the Row/Source
for the Subform was the Emission table. I originally did have it set up on
a query, but realized that the query included everything in the table and
was not linked to any other tables so I changed it to use the table
directly. Yes, I could add records to it (when I used the Emission_ID as
the link, but not when I used the Vehicle_ID.) Emission_ID in the Vehicle
table was never getting populated, and neither was the Vehicle_ID field in
the Emission table.

When I changed it to use a single form instead of a sub-form, the Vehicle_ID
in the Emission table was getting populated. That is how I caught that I
had the Emission_ID redundantly stored in the Vehicle table - if I have the
Vehicle_ID in the Emission table, I don't need the Emission_ID in the
Vehicle table, since I can always refer to the Emission table to get the
Vehicle_ID related to the Emission record in that table.

I did "Almost" solve the problem by setting the value of the Vehicle_ID
field in the Subform to =[Forms]![ENGINE SWAP DATA ENTRY FORM]![Vehicle_ID]
so that it would get the value of the Vehicle_ID from the main form. I
could see that it was being passed, but it was not saving it in the table -
which is why at that point I recreated the whole form as a single form.
Luckily I am very fast at formatting so it only took a couple of hours to
re-create.

Yes, there was a validation rule for the Emission_ID in the Vehicle table -
it was a number (Long Integer), required, indexed with no dupes. Yes it was
originally set up as a look-up field. I think that is where all my problems
started. I know better than to use look-ups in a table, but I changed it to
a look-up after the first try to see if I could get it to populate that
way - no luck! Now I know better - DON'T use look-ups in tables!

Thanks so very much for helping me with this - I did get it resolved, but
NOT by using a subform. I would really be interested in learning how to
correctly link the subform so that it will populate the foreign key in the
main table next time.

Thanks!

Coleen

Hi Coleen,

what is the NAME property for that control on both the
mainform and the subform?

What are the LinkMasterFields and LinkChildFields properties
for the subform control? Is there more than one controlname
specified for each one? Do all values in these properties
match up to the Name property for the corresponding controls
on each respective form?

What Indexes are set in the Test Vehicle table?

What is the RowSource for the Test Vehicle subform?

... if it is based on a query (instead of the table
directly)... when you make a query from the subform
RowSource (if it is not saved already), can you add a record
to it? What value does Emmission_ID get, if any?

Is Emmission_ID visible in the subform while you are
developing so you can examine its values?

Is there a ValidationRule set for Emmission_ID either at the
form or table level?

Is Emmission_ID defined to be a lookup field in the table
design? If so, make this a textbox display. If it is a
combobox on your subform, change this to textbox. If the
value is filled through the Link Fields, the user has no
need to pick or change anything in this field.

I have more questions if you haven't found the solution

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day


linked