Whether or not first field is filled in in the extra (addition) row at bottom of datasheet

  • Thread starter Thread starter aeschylus
  • Start date Start date
A

aeschylus

I have a main form, for person.
I have two subforms.
Subform1 is for person_to_allergy.
Subform2 is for person_to_preference.

I have tables, person, allerge, preference, person_to_allergy, and
person_to_preference; so I'm treating person:preference as M:M
(many-to-many), and I'm treating person:allergy as M:M.

#1)
In the person_to_allergy subform, when I open a person who currently
has one allergy (one row in the person_to_allergy table), I see one
row filled in, and the last row blank, in datasheet mode. This blank
row is where I would add more data.

BUT

#2)
In the person_to_preference subform, when I open a person who
currently has one preference (one row in the person_to_preference
table), I see one row filled in, and the last row has the current
person's name filled in (in the person_id field--it uses Lookup to
name), but the rest is blank. This semi-blank row is where I would add
more data.

I like the first behavior better (entire row blank), because as soon
as I start typing in the new row in the person_to_allergy subform, it
anyway fills in that first field (person_id, using name as lookup). I
just think the all blank row looks prettier than the partly blank row
of the second case.


I cannot figure out what controls whether or not it fills in the key
field of a subform in that last/extra/addition row?

I looked around the Form/settings of those two subforms, but didn't
see any obvious difference.
 
You should make sure that the Link Master and Link Child properties of each
subform control are set to fill in the related Person ID. Delete or hide
the Person ID control on the two subforms - they're redundant and
irrelevant. You're seeing the person name filled in because you're using a
Lookup in the table or query for that second subform. Lookups are not a
good idea -- see: http://www.mvps.org/access/lookupfields.htm

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/johnviescas
 
John Viescas said:
You should make sure that the Link Master and Link Child properties of each
subform control are set to fill in the related Person ID. Delete or hide
the Person ID control on the two subforms - they're redundant and
irrelevant. You're seeing the person name filled in because you're using a
Lookup in the table or query for that second subform. Lookups are not a
good idea -- see: http://www.mvps.org/access/lookupfields.htm
Click to expand...

The Link Master & Link Child properties are all four set to person_id.

Ugh, not using lookups will be a pain, because all my ids are GUIDs (so I
can do replication), and looking at, eg, the person_to_allergy table is
horrible without lookups--the table is just:

person_to_allergy_id: autonumber GUID
person_id: GUID
allergy_id: GUID
(and some metadata such as last editor (GUID) & last edit date)

so it is just three columns of GUIDs, and it is impossible to debug
anything without lookups.

How can I remove the Lookup Tables and still be able to see anything
in the raw data, or would you suggest I build views (er, queries)
showing the actual data (of course I actually have those) and only
look at those and never look at the actual tables again?

Thanks for the info and the link!
 
John Viescas said:
You should make sure that the Link Master and Link Child properties of each
subform control are set to fill in the related Person ID. Delete or hide
the Person ID control on the two subforms - they're redundant and
irrelevant. You're seeing the person name filled in because you're using a
Lookup in the table or query for that second subform. Lookups are not a
good idea -- see: http://www.mvps.org/access/lookupfields.htm
Click to expand...
re: #6: Lookup fields mask what is really happening, and hide good
relational methodology from the user.


Well, I guess that is the core of my problem. What is really happening
is that I have a person_to_allergy many-to-many link table, linking
GUIDs (which are those long alphanumeric strings such as appear
in HKEY_CLASSES_ROOT), and most users can't understand that, and
really don't want to see that! I can't really appreciate an idea of
showing GUIDs to a user...
 
You answered your own question - use a query that includes the "lookup"
tables and display whatever you want to see in another column.
Alternatively, you could change your lookup to display the GUID but also
provide the related info in the drop-down list.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/johnviescas
 
There's no reason to ever show the GUID. Lookups are OK in a form to help
the user select the correct related value, but they should never be defined
in the base table. In your case, the user can see the related person in the
outermost form, so lookups are redundant in the subforms.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/johnviescas
 
Back
Top