Re: More Subform Questions...
- From: strive4peace <strive4peace2006@xxxxxxxxx>
- Date: Sat, 13 May 2006 04:59:45 -0400
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 ;)
Coleen wrote:
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" <strive4peace2006@xxxxxxxxx> wrote in message
news:%23Y2RYNSdGHA.1856@xxxxxxxxxxxxxxxxxxxxxxx
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 wrote:
Yes to both...That is why I am SO confused!
Thanks for any help you can give me :-)
"strive4peace" <strive4peace2006@xxxxxxxxx> wrote in message
news:el$TWqMdGHA.4892@xxxxxxxxxxxxxxxxxxxxxxx
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 wrote:
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
- References:
- More Subform Questions...
- From: Coleen
- Re: More Subform Questions...
- From: strive4peace
- Re: More Subform Questions...
- From: Coleen
- Re: More Subform Questions...
- From: strive4peace
- Re: More Subform Questions...
- From: Coleen
- More Subform Questions...
- Prev by Date: Re: I need help from an expert
- Next by Date: Re: How do I turn on a filter in a subform?
- Previous by thread: Re: More Subform Questions...
- Next by thread: Search form not work suddenly.
- Index(es):
Relevant Pages
|
