Data Entry: automatically fill in Join Point Fields in Various Tables 1

[RobotMush]

I am creating a Database with multiple tables that I have joined via one to many. My problem is that when entering Data I am needing to have the join points record the same data in the joined fields for each record. What I am getting is the following error.
"Cannot add record(s; join key of table 'tblName' not in record set"
Below is the sqr view of the query I use for the Data Entry Form.

SELECT
tblName.Name_ID,
tblName.strLastName AS tblName_strLastName,
tblName.strFirstName,
tblName.strTitle,
tblChurch.strChurchName AS tblName_strChurchName,
tblName.dteDOB,
tblDept.Dept_ID AS tblName_Dept_ID,
tblPhone.Ph_ID AS tblPhone_Ph_ID,
tblName.strLastName AS tblPhone_strLastName,
tblPhone.intAreaCode,
tblPhone.strPhoneNum,
tblPhone.intExtension,
tblAddress.Add_ID,
tblPhone.Ph_ID AS tblAddress_Ph_ID,
[tblName_strLastName] AS tblAddress_strLastName,
tblAddress.strAddress,
tblAddress.[strAddress-Ste,Apt,Unt],
tblAddress.strCity,
tblAddress.strState,
tblAddress.strZip,
tblChurch.Ch_ID,
tblChurch.strChurchName AS tblChurch_strChurchName,
tblChurch.strMinister,
tblDistrict.strDistrict AS tblChurch_strDistrict,
tblDept.Dept_ID AS tblDept_Dept_ID,
tblDept.strMusic,
tblDept.strSingle,
tblDistrict.Dist_ID,
tblChurch.strChurchName AS tblDistrict_strChurchName,
tblDistrict.strDistrict AS tblDistrict_strDistrict
FROM
tblPhone INNER JOIN
((tblDistrict INNER JOIN
(tblDept INNER JOIN
(tblChurch INNER JOIN tblName ON tblChurch.strChurchName = tblName.strChurchName)
ON tblDept.Dept_ID = tblName.Dept_ID)
ON tblDistrict.strDistrict = tblChurch.strDistrict)
INNER JOIN tblAddress
ON tblName.strLastName = tblAddress.strLastName)
ON tblPhone.Ph_ID = tblAddress.Ph_ID;

Can anyone help me in figuring out where I am going wrong?

Thank You
RobotMush (Technical User)

 

[Frink]

Hallo,

When populating Joined tables, you have to create the record in the one side of the one-to-many before you do the many side.

- Frink

 

[RobotMush]

Question Frink, would this allow me, as I am entering in new data, to have the fields automatically filled in for all the table fields I.E. tblName_strAddress also be added to tblAddress_strAddress? with tblAddress_strAddress being the one to many tblName_strAddress.

I know that you can already have data in the one to be added to the many, but how do you get the tables filled out with only one new entry in the field?

RobotMush (Technical User)

 

[PHV]

Have a look here:

http://r937.com/relational.html

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RobotMush]

Thank you, I hope it does.

I'll let y'all know

RobotMush (Technical User)

 

[Frink]

Hallo,

If you base the form on a query with the linked tables, and include the strAddress from the one-side and other fields from the many-side, when you create a new record it should populate both tables.

One thing which strikes me about your structure is the use of what I assume is a postal address as a key field.
It's probably rude of me, as I don't know what you are trying to achieve, but postal addresses can be written in several different ways and mean the same thing:
29 Acacia Avenue
29 Acacia Av
29 Acacia Av.
29 Acacia Avenue,
So it's too general, but also as two people may live at the same address, it's too specific too.
In a church you may have parents and adult offspring living in the same house, therefore surname and address will be the same, but you would probably want to treat them separately.
People are difficult to add to a database, as names are not unique, nor are addresses. Generally Full Name and DOB is quite a good identifier, although potentially you could get duplicates here too. You could include address as well, but still you could potentially get two people with the same name, born on the same day, living at the same address. It depends how big your db will be. For 10 entries it would probably be ok. For 10 million entries it probably wouldn't.

I suppose what I'm saying is make sure you've thought about your table structure long and hard, before worrying about forms. Then you won't waste time and effort later.

Feel free to continue this thread if you want any more help,

- Frink