I have two tables. Both tables have the several fields that make up the
tracking number, which may contain null fields. (Table 2 stores many
replicates of a single type of measurement for each tracking number, while
Table 1 stores several types of measurements with 1 replicate each.)
I have a query for each table that adds calculated fields to correct the
problem I was having with the null fields.
My main form is based on the query for Table 1, and has "live" fields for
data entry. The subform is based on the query for Table 2 and has only 1
field for data entry, but is linked to the main form record using the
calculated fields. There are no fields from the tracking no. on the
subform,
live or calculated, just the one field of Table 2 that contains multiple
replicates... the other fields are used for linking purposes only and are
not
on the form as visible or invisible text boxes.
The subform does its linking properly (although I have to switch to a
different record and back again for a change on the main form to be
reflected
in the subform). The problem is when I try to add a record by entering
data
on the subform, it tries to fill in data for the linked fields (the
tracking
no), but since they're calculated fields, it barks, saying it can't update
them. I need it to fill in the appropriate tracking number (according to
the
main form's record) into the live fields of Table 2, but it's trying to do
that for the linked, calculated fields.
Well, that's the long version, but hopefully it makes it more clear what
I'm
doing.
Thanks for your help,
=Ray=
Douglas J. Steele said:
No, it doesn't really make sense to me.
Do you have both the "live" fields and the "calculated" fields on both
the
form and subform? (the calculated fields don't need to be visible)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Okay, now I have another problem...
I have a subform with one field from another table (Table 2), but with
linked calculation fields (to fix the null problem) to the master form,
but
when I enter data in the subform, it tries to update the calculated
fields
in
Table 2, and gives an error message because it can't update those
fields.
How can I tell it to use the data fields, as represented on the main
form,
when data is entered on the subform?
Hopefully that makes sense.
=Ray=
:
Based your forms on a query that has the fields with and without the
Nz
function.
Use the fields with the Nz function for the
LinkChildFields/LinkMasterFields, but display the fields without the
Nz
function so that you can update them.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Yes, I mean "Null".
I don't actually have a join relationship between the fields, it's
just
set
in the "Llink Child/Master Fields" properties.
I modified the fields as you suggested with the Nz function,
however, I
don't know how to do that in reverse. That is, I have my query where
it
converts the null fields to a ".", but how do I get the form to
still
allow
data entry, converting any null values to a period?
Thanks for your help.
=Ray=
:
When you say "blank", I assume you actually mean "Null". Due to the
special
nature of Null, you cannot join on a field that's Null.
Try using the Nz function in your query to convert the Null values
to
some
value that will never occur naturally in the field, such as
Nz([Field2],
"<<<"), and then join on that field.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
I have a subform with several linked fields (I have a tracking
number
which
is broken into several fields that I need linked). Occassionally,
not
all
of
the linked fields are occupied, but I still want them to pick up
the
record
in the subform. Is there a way to do that?
For clarification, say I have four linked fields for my tracking
number:
123 ABC 456 DEF
If I have a record on my main form with this number:
123 <blank> 456 DEF
I want the subform to pick up records which match all four
fields,
but
instead, it doesn't recognize that the blank fields are still a
match.
If
I
populate those blank fields with anything, it finds them:
123 ... 456 DEF
But I don't want to have do that.
Can anyone help?
Thanks,
=Ray=