some tables work, some don't 1

[solun]

I went to test my big project with a user yesterday and everything was broke!!

Okay, not everything, just the ability to link several tables by one ID and add data...which is pretty darn crucial.

Table 1: Loans
Table 2: LoansMortgages
Table 3: LoansRealEstateMortgages
Table 4: LoansREMtgCollMtg
Tbale 5: LoansTruthinLending

Tables 2-5 have one-to-one relationships with table 1.
I put them all together in a query together to be able to read/write data with a form. I used Join2 ( The form is definitely not the problem.

The query should allow me to add and edit data to each of the tables, right? for tables 3, 4, & 5 I get the error "You cannot add or change a record because a related record is required the table "Loans"." but not for table 2.

Of course, loans already has a related record.

SELECT Loans.LoanID, Loans.AuditID, Loans.osiCustomerName, Loans.osiLoanNumber, Loans.osiDateLoanClose, Loans.osiAmt, Loans.osiRate, Loans.osiTerm, Loans.osiLoanTypeID, Loans.CreditReport, Loans.CreditReportDate, Loans.CreditReportScore, Loans.CreditReportY1, Loans.CreditReportY2, Loans.Note, Loans.NoteAmt, Loans.NoteRate, Loans.NoteTerm, Loans.NoteOfficer, Loans.NoteCosign, Loans.NoteTypeID, Loans.VoluntaryDeductionForm, Loans.LifeIns, Loans.LifeInsSigned, Loans.AHIns, Loans.AHInsSigned, Loans.RateToPolicy, Loans.RateToPolicyShould, Loans.LoanNotes, Loans.Finished, LoansMortgage.Mortgage, LoansMortgage.MtgType, LoansMortgage.MtgAmt, LoansMortgage.MtgRecord, LoansMortgage.LTV, LoansMortgage.LTVtoPolicy, LoansMortgage.Rescission, LoansMortgage.RescissionType, LoansMortgage.RescissionSign, LoansMortgage.RescissionStartDate, LoansMortgage.RescissionEndDate, sysLoanTypes.LoanType, LoansTruthInLending.TILDiscl, LoansTruthInLending.TILDisclSign, LoansTruthInLending.TILDisclDate, LoansTruthInLending.TILAPR, LoansTruthInLending.TILAPRCorrect, LoansTruthInLending.TILFinChg, LoansTruthInLending.TILFinChgCorrect, LoansTruthInLending.TILTotalPay, LoansTruthInLending.TILTotalPayCorrect
FROM ((sysLoanTypes INNER JOIN Loans ON sysLoanTypes.LoanTypeID = Loans.osiLoanTypeID) LEFT JOIN LoansTruthInLending ON Loans.LoanID = LoansTruthInLending.LoanID) LEFT JOIN LoansMortgage ON Loans.LoanID = LoansMortgage.LoanID
WHERE (((Loans.osiLoanTypeID)=1));

So, what's going on???

I've checked the field definitions for LoanID on each table. they're identical. I ran documenter for each of these tables and there are only one point of difference between Table 2 and Tables 3-5: Under Table Indexes, Loan ID, Unique is "True" for Table 2, "False" for 3-5. I don't know how to change that or if it's actually the problem.

(I've also done the usual MS Access tricks of Compact & Repair, create new db & import all the parts, rewrote the query, etc.)

Thank you!!!

Heidi I. Jones
Ridgerunner Consulting
Questions Answered. Problems Solved.

http://www.ridgerunnerconsulting.com

 

[PHV]

If you want a 1:1 relationship you have to make LoanID unique (ignoring null).

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

 

[solun]

Okay, so I'm right that the problem is in the Unique thing, right? I don't know how to change it... (I don't think I'm being dense!)

In the table design view, there's no option for setting "Unique".

Congrats on TipMaster of the week, PH!!

Heidi

Heidi I. Jones
Ridgerunner Consulting
Questions Answered. Problems Solved.

http://www.ridgerunnerconsulting.com

 

[PHV]

Unique is the same as not allowing duplicates.

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

 

[dhookom]

I would never try to join more than 2 tables together and attempt to edit the records. I would use your main table as the record source for the main form and then add subforms for the related tables.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[willir]

PHV Congradulations on another Tipmaster award.

Heidi

Review the following link (that was originally provided by PHV...
Harnessing the Power of Updatable Queries

It gives a pretty good back ground on queries that can be updated.

You have four tables in the query...
Loans
LoansMortgage
sysLoanTypes
LoansTruthInLending

I assume you are trying to update Loans, and the other information is for information.

The only value for sysLoantypes is LoanTypes.

On a form,
- Base the form from the table Loans, frmLoans
- Make LoanTypes a combo box that "hits" sysLoanTypes
- Make a form based on LoansMortgage and add it to the frmLoans
- Make a form based on LoansTruthInLending and add it to the frmLoans

Richard

 

[solun]

willir:

Actually, the Loans, LoansMortgage, LoansTruthinLending and LoansRealEstateMortgage tables are being updated, the sysLoanTypes is for info and has already been assigned at that point. The link you provided seems to indicate that i can do this, that the Jet engine allows it. However, advice from dhookom is that this isn't the best method. It feels clean and logical to me - and subforms look so messy!

PH: "Unique is the same as not allowing duplicates. "

Yes, I recognize that. However, I still don't know -how- to change it to unique=true. I need the literal how-to on that, please.

So, any other advice?

Thanks,

Heidi



Heidi I. Jones
Ridgerunner Consulting
Questions Answered. Problems Solved.

http://www.ridgerunnerconsulting.com

 

[solun]

found the index unique thing. still testing.


Heidi I. Jones
Ridgerunner Consulting
Questions Answered. Problems Solved.

http://www.ridgerunnerconsulting.com

 

[solun]

It all works now!! Thank you! But feel free to answer the remaining best practice questions.

Heidi I. Jones
Ridgerunner Consulting
Questions Answered. Problems Solved.

http://www.ridgerunnerconsulting.com