Doesn't 1:1 automatically generate record in foreign key table?

[Nick Mirro]

2 tables with 1:1 relationship are joined by the same primary key, in this
case visitID. This field is created (autonumber) in tblVisits. The foreign
key table, tblPayments has visitID as its primary key.

After creating a new record in tblVisits, I open a form, which I assume
should create a new record in tblPayments (with any new data added). This
doesn't work though. I get this error:

"You cannot add a record (to tblPayments) because a related record is
required in table 'tblVisits.'

Shouldn't a new record be created automatically in tblPayments (even though
it isn't autonumber)? There is another table (tblBillingInfo) which has
this exact relationship with tblVisits and new records are created when I
add data to its form.


primary key type

tblVisits visitID autonumber
tblPayments visitID foreign key
tblBillingInfo visitID foreign key


Relationships:

http://home.comcast.net/~nickmirro/images/Temporary/Relationships.pdf

 

[Rick Brandt]

Relationships do not automatically create records. The closest behavior to this is
if you use a mainform/subform combination to make entries in two tables with a
one-to-many relationship. In that scenario the MasterLink/ChildLink properties of
the subform control will automatically cause the foreign key of the child records to
pick up the value from the main form without the developer or the user having to do
anything to make it happen. While the foreign key value is automatically assigned,
the record still has to be created explicitly.

Relationships can cause related records to be automatically *deleted* by using
cascade delete in the relationship properties, but they are never automatically
created.

 

[Nick Mirro]

Thank you for helping with this. These are 1:1's with no subform involved.

I open the form in question to a specific record by linking visitID. There
are fields from tblPayments available (but I guess no record existing to put
the data in). I assumed by entering data, the record would be created by
tblPayments inheriting "visitID" from tblVisits.

Question:

How do I get a new record in tblPayments with the same visitID Primary Key
that I opened the form with?

Nick

 

[Van T. Dinh]

--
HTH
Van T. Dinh
MVP (Access)

No. They are related but still separate Tables.

With regards to tblVisits + tblBillingInfo, your Form is probably based on a
Query combining the 2 Tables and when you added a "row" on the Form, you
actually add 1 Record to each Table.

You can use the Form_AfterInsert Event of the Form you use to add Record
into tblVisits to add a related (dummy/nearly blank) Record into the
tblPayments by code if you wish to.

However, I would be wary of the One-to-One relationship. There are not many
One-to-One relationships in most database applications.

HTH
Van T. Dinh
MVP (Access)

 

[Nick Mirro]

Thank you Van. I hate to plead ignorance, but would you mind showing me how
to code that? I'm seriously lacking in that area. Othewise I can post to
formscoding.

I remember reading somewhere that patient billing software is more likely to
use 1:1, to help break data it into manageably sized chunks - my motivation.

http://home.comcast.net/~nickmirro/images/Temporary/Relationships.pdf


Nick

 

[Van T. Dinh]

In the Form_AfterInsert Event, use something like (not tested):

DBEngine(0)(0).Execute "INSERT INTO tblPayments (VisitID) " & _
" VALUES (" & Me.txtVisitID & ")", dbFailOnError

Check Access Help on the Append Query / Insert Into... SQL statement. Check
Access VB Help on the Execute Method of the Database Object.

You know relationships for your Tables best but I visited a doctor once and
I got 4 bills (1 for the consultation where the doctor took some sample
tissues, one for the pathology to do some tests, one for the specialist to
look at the pathology results and the last one for the original doctor to
interpret the specialist's report).

OTOH, I might decide I don't have enough money (you know Access developers
don't usually have enough money!) and there for make a number of
part-payments to each bill. I didn't but it is a possible scenario and you
may need to cater for this!