Subform Data Entry Problems

G

Guest

I have a subform based on a query linked to the Form by "ContactID". The
query works fine when run independent of the subform. I am able to enter all
data fields and where necessary the autonumber function assigns properly.

But when I try to use the subform all records related by my table
"tblADDRESS" do not allow data entry (the fields are not locked). I imagine
the ContactID Master/Child link in some way is interfering with the query in
Form view, but I've tried every variation of join properties within the Query
for the Subform and in the Query for the main Form to find a work around and
have reached an impasse.

Here's the Query code that runs in Query View, but not in Form View:

SELECT tblCONTACTSMULTIPLE.ContactID, tblADDRESS.AddressID,
tblCONTACTSMULTIPLE.ContactsMultipleID, tblCONTACTSMULTIPLE.CompanyID,
tblCONTACTSMULTIPLE.PrimaryContact, tblCOMPANY.CompanyName,
tblCONTACTSMULTIPLE.JobTitleID, tblCONTACTSMULTIPLE.JobTitle,
tblCONTACTSMULTIPLE.OfficePhone, tblCONTACTSMULTIPLE.OfficeExt,
tblCONTACTSMULTIPLE.Fax, tblCONTACTSMULTIPLE.Email2, tblADDRESS.AddressLine1,
tblADDRESS.AddressLine2, tblADDRESS.MailingAddress, tblADDRESS.OnsiteAddress,
tblADDRESS.PrimaryAddress, tblADDRESS.City, tblADDRESS.State, tblADDRESS.Zip,
tblADDRESS.County, tblCONTACTS.FirstName, tblCONTACTS.LastName,
tblCONTACTS.Suffix
FROM tblCONTACTS INNER JOIN ((tblCONTACTSMULTIPLE LEFT JOIN tblCOMPANY ON
tblCONTACTSMULTIPLE.CompanyID = tblCOMPANY.CompanyID) LEFT JOIN tblADDRESS ON
tblCONTACTSMULTIPLE.AddressID = tblADDRESS.AddressID) ON
tblCONTACTS.ContactID = tblCONTACTSMULTIPLE.ContactID
ORDER BY tblCONTACTSMULTIPLE.PrimaryContact, tblCOMPANY.CompanyName;

I also tried to build a different Query to do some of the functions and then
another Query on that Query to finish the rest (Access Help screen suggested)
but that messed up the Master/Child links to the Subforms and even when I
tried to reestablish the links or delete the subforms and reinstall it blew
it up so bad I had to revert to my backup version I keep while I'm
developing. Any suggestions will be greatly appreciated.
 
B

Bob Quintal

=?Utf-8?B?bmdpbmNvbG9yYWRv?=
I have a subform based on a query linked to the Form by
"ContactID". The query works fine when run independent of the
subform. I am able to enter all data fields and where
necessary the autonumber function assigns properly.
Click to expand...

The query example below contains one inner join and two left
joins. I'm very surprised that Access (Jet Engine) lets you edit
this query even in query view.

That said, what is the source of the mainform? if it is one of
the three tables used in your subform query, or a query which
uses one of those tables, you will also get a non-updateable
query.

Perhaps splitting the subform into two or three separate
subforms tied each to a single table you will achieve aeditable
queries.
But when I try to use the subform all records related by my
table "tblADDRESS" do not allow data entry (the fields are not
locked). I imagine the ContactID Master/Child link in some
way is interfering with the query in Form view, but I've tried
every variation of join properties within the Query for the
Subform and in the Query for the main Form to find a work
around and have reached an impasse.

Here's the Query code that runs in Query View, but not in Form
View:

SELECT tblCONTACTSMULTIPLE.ContactID, tblADDRESS.AddressID,
tblCONTACTSMULTIPLE.ContactsMultipleID,
tblCONTACTSMULTIPLE.CompanyID,
tblCONTACTSMULTIPLE.PrimaryContact, tblCOMPANY.CompanyName,
tblCONTACTSMULTIPLE.JobTitleID, tblCONTACTSMULTIPLE.JobTitle,
tblCONTACTSMULTIPLE.OfficePhone,
tblCONTACTSMULTIPLE.OfficeExt, tblCONTACTSMULTIPLE.Fax,
tblCONTACTSMULTIPLE.Email2, tblADDRESS.AddressLine1,
tblADDRESS.AddressLine2, tblADDRESS.MailingAddress,
tblADDRESS.OnsiteAddress, tblADDRESS.PrimaryAddress,
tblADDRESS.City, tblADDRESS.State, tblADDRESS.Zip,
tblADDRESS.County, tblCONTACTS.FirstName,
tblCONTACTS.LastName, tblCONTACTS.Suffix
FROM tblCONTACTS INNER JOIN ((tblCONTACTSMULTIPLE LEFT JOIN
tblCOMPANY ON tblCONTACTSMULTIPLE.CompanyID =
tblCOMPANY.CompanyID) LEFT JOIN tblADDRESS ON
tblCONTACTSMULTIPLE.AddressID = tblADDRESS.AddressID) ON
tblCONTACTS.ContactID = tblCONTACTSMULTIPLE.ContactID ORDER BY
tblCONTACTSMULTIPLE.PrimaryContact, tblCOMPANY.CompanyName;

I also tried to build a different Query to do some of the
functions and then another Query on that Query to finish the
rest (Access Help screen suggested) but that messed up the
Master/Child links to the Subforms and even when I tried to
reestablish the links or delete the subforms and reinstall it
blew it up so bad I had to revert to my backup version I keep
while I'm developing. Any suggestions will be greatly
appreciated.
Click to expand...
 
G

Guest

You nailed it - I had the same table (a bridge table between multiple
tables) in the form and subform. Works beauty now, thanks!!
That said, what is the source of the mainform? if it is one of
the three tables used in your subform query, or a query which
uses one of those tables, you will also get a non-updateable
query.
Click to expand...

Can't explain it, it ran.
 
Top