Relationship and forms

[Guest]

I have a new database that has tblRentalAgreement, tblCorpBusiness and
tblMajesticExpressRenters. All have primary keys that are autonumbers.
TblCorpBusiness and tblMajesticExpressCustomer have one-to-many relationships
with the tblRentalAgreement.

When I am setting up my frmRentalAgreement that is based on
qryRentalAgreement (which incorporated all three tables)... I can't add a new
record because of the one to many relationship I have established with
tblCorpBusiness and the tblMajesticExpressCustomer (both have PK and
tblRentalAgreement has the FK)... I get the error:

The Microsoft jet databse engine cannot find a record in the table
'tblMajesticExpressCustomer', with matching fields in
'tblRentalAgreeement.MajesticExpressId'.

I set up the tblCorpBusiness for repeat businesses that utilized our
services, and tblMajesticExpressRenters for repeat individuals that rent cars
from us. HOWEVER, if the renter is not associated with a Business that field
will need to be left blank, and visa versa.

Please let me know if I am not utilizing the relationships right... Thanks,
m.

 

[John W. Vinson]

When I am setting up my frmRentalAgreement that is based on
qryRentalAgreement (which incorporated all three tables)...

That's your problem. It's only very rarely possible to create One Grand Master
Query and use it to update all the tables, and even if it's possible it's
rarely a good idea!

Instead, use a Form based on the table you want to update - Rental Agreements
here - and probably just use Combo Boxes based on the other two tables to
insert the appropriate value. If (as is likely) you have one to many
relationships (e.g. repeat customers, a Customer table related one to many to
an Agreements table), you'll probably want to use a Form based on the "one"
side with a Subform based on the "many".

John W. Vinson [MVP]

 

[Guest]

Hey John, thanks for responding.

So, instead of basing the form on a query, you are saying to just base it on
the table? Or can I base it on a query that would only include the
tblRentalAgreements? Do I benefit to base the form on a query vs. table?

I do understand using the combo boxes... and I was thinking that all I would
do is a combo box and choose a CorpBusiness based on the tblCorpBusiness.
Which is what I believe you were talking about.

BUT here is a question about relationships: do I have to have a relationship
between the tblRentalAgreement and tblCorpBusiness? --- There is a chance
that a renter won't be a CorpBusiness, so that field may be left empty (and I
am not sure if this creates a problem if I have a one to many relationship
set up). And it probably won't matter if this is a combo box... I think I am
answering my own question, but any guidance is truly appreciated.

Thanks again, m.

 

[John W. Vinson]

Hey John, thanks for responding.

So, instead of basing the form on a query, you are saying to just base it on
the table? Or can I base it on a query that would only include the
tblRentalAgreements? Do I benefit to base the form on a query vs. table?

The advantage of a Query is that you can present the records in a suitable
sort order. If you use a Table directly, it will show the results in whatever
order the Access query optimizer finds convenient.

I do understand using the combo boxes... and I was thinking that all I would
do is a combo box and choose a CorpBusiness based on the tblCorpBusiness.
Which is what I believe you were talking about. x
BUT here is a question about relationships: do I have to have a relationship
between the tblRentalAgreement and tblCorpBusiness? --- There is a chance
that a renter won't be a CorpBusiness, so that field may be left empty (and I
am not sure if this creates a problem if I have a one to many relationship
set up). And it probably won't matter if this is a combo box... I think I am
answering my own question, but any guidance is truly appreciated.

A Relationship will prevent you or a user from entering a nonexistant
CorpBusiness ID, and will create indexes which will make the joins and queries
run faster. It will not inhibit you from creating a Rental Agreement with a
NULL CorpBusiness field.

John W. Vinson [MVP]