It is hard to make out just what is happening, but one thing that doesn't
sound right is that the form and subform are linked by the primary keys of
their source tables. It should probably be the prmary key of the main table
and the foreign key (linking field) of the child table.
I can glean a few things from your description, but not enough to give me a
real picture. A description of the table structure and relationships would
help. For instance:
tblIndividual
IndividualID (primary key)
FirstName
LastName
etc.
There is no need to post every field, but rather just the ones that are
parts of the realtionships and a few others to give an idea of the data
being stored in the table.
It is almost always best if individuals, companies, and other entities are
identified by an unchanging number rather than a name. If a company or
individual name changes you probably want the data accumulated under the old
name to carry over to the new name. This field (typically a number) is
stored in the related table. If you have a combo box based on tblIndividual
that is located on a form based on tblComplaintSubjects,
tblComplaintSubjects needs a place to store that ID number. Those two
tables, then, are related by that field. To do that you can use the
Relationships window.
tblComplaintSubject
CS_ID (primary key)
IndividualID (link to tblIndividual)
ComplaintText
I'll leave it at that for now.
Tony Williams said:
Hi Bruce. The control txtmemnbr is also a calculated field and doesn't
exist
in any table.
What I've got is a table to hold the names of officers of an assocaition
(tblindividual) and a table that holds details of complaints
(tblcomplaints)
and a table that holds the names and companies that are the subject of the
complaints (tblcomplaintssubjects). The names of tblcomplaintssubjects are
derived from a combobox on my subform which is based on tblindividuals.
the
mainform and subform are based on tblcomplaints and tablecomplaintsubjects
and are linked using the primary keys of each table. I know that sounds
complicated but at the time a user is inputting the complaint they need
the
combobox to identify the officers name, so we don't get spelling mistakes
and
once they've chosen the name that then populates other fields from the
various columns of the combobox.
Hope yopu can gleam something from that. having typed it I realise how
complicated it is and am wondering whether there is a better way of doing
all
of this
Thanks
Tony
BruceM said:
For the report, why not just use a query that includes tblindividual and
whatever table contains txtmemnbr? For that matter, is there a reason
you
can't do that in the form? The domain lookup function seems like a lot
of
overhead.
I have a form that has a control (txtco2) that has a calculated value
from
this
=IIf([txtcat]="Full",DLookUp("[txtbusinessname]","[tblindividual]","[txtmemnumber]="
&
[Forms]![frmmain]![Subform1]![subformcomplaints].[Form]![txtmemnbr]),"
")
I have a report that is based on a different unconnected table where I
want
to include a control that has the same value as txtco2. There are no
fields
in either my tblindividual or the table on which the report is based
that
I
could link the fields.
I have tried putting a control on my form called txtbusiness2 and in
the
Before update event of txtco2 put
Me.txtbusiness2=Me.txtco2
but that doesn't work.
I know I'm not supposed to store calculated fields (I never normally
do!)
but is there no way I can take the value of txtco2 and store it in my
other
field?
Thanks
Tony
