Outer Join, cannot update issue

[dskillingstad]

I would really appreciate someone's help on this, or at least point me
in the right direction....

I'm working on a permit database that contains 12 tables, and rather
than list all of the tables, I'll just list a few, as the links are the
same for all tables. These tables are:
tblPermitMain
tblApplicant
tblContractor
tblEngineer
tblProperty
tblInspections

All of these tables are joined (outer join) to the PermitMain table by
ID fields. I have a query which grabs all of the fields from all of
the tables, and then I have a form which is based on this query.
Here's what I'd like to do...

On this form, we will input the PermiMain info (PermitNumber,
PermitType, etc.), Applicant info (Fname, Lname, etc), Property info
(ParcelNumber, Address, etc), and a subform with Inspection info (date,
notes, etc). Not every record will have a Contractor, or Engineer,
however they may at a later time, which we need to include for a
specific permit. Based on the query below, if I input information in
text boxes for each of the tables above, the information is saved,
however, if I input information for say, PermitMain, Applicant, &
Property, exit that record or close the form, and then open the form
and try to say, add the Contractor or Engineer info, I get the
following error in the status bar at the bottom of the Access window:
"Cannot enter value into blank field on 'one' side of outer join."

Basically, if I enter at least one field of information for each table
above, the record saves. If I enter information in only certain tables
(PermitMain, Property), exit the form , open the form and then attempt
to add information to say the Contractor or Engineer tables, I get the
error. I can edit fields that already contain information, just not
add.

I'm stumped!! Anyways, here's the SQL for the query:

SELECT tblPermitMain.*,
tblProperty.*,
tblApplicant.*,
tblConstContractor.*,
tblConstArchitect.*,
tblConstEngineer.*,
tblConstPlumbing.*,
tblConstMechanical.*,
tblPermitPlumbing.*,
tblPermitMechanical.*
FROM tblPermitPlumbing
RIGHT JOIN (tblPermitMechanical RIGHT JOIN
(tblConstPlumbing RIGHT JOIN
(tblConstMechanical RIGHT JOIN
(tblConstContractor RIGHT JOIN
(tblConstArchitect RIGHT JOIN
(tblConstEngineer RIGHT JOIN
(tblProperty RIGHT JOIN
(tblApplicant RIGHT JOIN tblPermitMain ON
tblApplicant.ApplicantID = tblPermitMain.ApplicantID)
ON tblProperty.PropertyID = tblPermitMain.PropertyID)
ON tblConstEngineer.EngineerID =
tblPermitMain.EngineerID)
ON tblConstArchitect.ArchitectID =
tblPermitMain.ArchitectID)
ON tblConstContractor.ContractorID =
tblPermitMain.ContractorID)
ON tblConstMechanical.MechanicalID =
tblPermitMain.MechanicalID)
ON tblConstPlumbing.PlumberID = tblPermitMain.PlumberID)
ON tblPermitMechanical.PermitMechanicalID =
tblPermitMain.PermitMechanicalID)
ON tblPermitPlumbing.PermitPlumbingID = tblPermitMain.PermitPlumbingID;
Thank you for any help you may give!

dskillingstad

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Don't use ONE BIG QUERY for a form. Use one query that loads/reads one
table per form. Then for each dependent table, make a subform on the
main form. In your case, this would mean the main form would be for the
PermitMain, then subforms, each, for Applicant, Contractor, Engineer,
Property and Inspections.

Read the Access Help articles on subforms for info about why to use
subforms. Help articles hierarchy:

Forms
Creating subforms
About subforms

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkuRCIechKqOuFEgEQKv3ACfTu9WvYXYR9oaTxQtb29HyQ 5c9voAoOBf
3/LW33neChu7o0FxxsMELfhl
=73iD
-----END PGP SIGNATURE-----
ds***********@gmail.com wrote:

I would really appreciate someone's help on this, or at least point me
in the right direction....

I'm working on a permit database that contains 12 tables, and rather
than list all of the tables, I'll just list a few, as the links are the
same for all tables. These tables are:
tblPermitMain
tblApplicant
tblContractor
tblEngineer
tblProperty
tblInspections

All of these tables are joined (outer join) to the PermitMain table by
ID fields. I have a query which grabs all of the fields from all of
the tables, and then I have a form which is based on this query.
Here's what I'd like to do...

< SNIP >

[dskillingstad]

Thanks for the help. I'll give that a try.

Thanks again,
dskillingstad

MGFoster wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Don't use ONE BIG QUERY for a form. Use one query that loads/reads one table per form. Then for each dependent table, make a subform on the
main form. In your case, this would mean the main form would be for the PermitMain, then subforms, each, for Applicant, Contractor, Engineer,
Property and Inspections.

Read the Access Help articles on subforms for info about why to use
subforms. Help articles hierarchy:

Forms
Creating subforms
About subforms

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkuRCIechKqOuFEgEQKv3ACfTu9WvYXYR9oaTxQtb29HyQ 5c9voAoOBf
3/LW33neChu7o0FxxsMELfhl
=73iD
-----END PGP SIGNATURE-----
ds***********@gmail.com wrote:

I would really appreciate someone's help on this, or at least point me in the right direction....

I'm working on a permit database that contains 12 tables, and rather than list all of the tables, I'll just list a few, as the links are the same for all tables. These tables are:
tblPermitMain
tblApplicant
tblContractor
tblEngineer
tblProperty
tblInspections

All of these tables are joined (outer join) to the PermitMain table by ID fields. I have a query which grabs all of the fields from all of the tables, and then I have a form which is based on this query.
Here's what I'd like to do...

< SNIP >