Nick Mirro said:
Well the form is bound to a query which calls data from tblVisits,
tblPayments and tblBillingInfo. Both tblPayments and tblBillingInfo
are linked to tblVisits by a 1:1 relationship (strangely).
When I open the form in which billing (not payment) info is first
entered, a new record is automatically generated in tblBillingInfo.
Unfortunately this is not happening in the form that I use to enter
payment info. This "frmPayments" calls up Billing Info just for user
input reference.
frmPayments qryPayments tblPayments
frmBillingInfo qryBillingInfo tblBillingInfo
Both queries link to fields in all 3 tables.
The 1:1 relationships include all records in tblVisits and matching
in the 2 other tables. (cascade update and delete)
http://home.comcast.net/~nickmirro/images/Temporary/Relationships.pdf
form screenshots available : )
I wish all newsgroup querents were so obliging.
Since you've been asking questions in the newsgroups for a while, I
suppose others have already mentioned that your table design is not
normalized -- there are lots of repeating field groups like CPT1, CPT2,
CPT3, DxCode1, DxCode2, DxCode3, DateOfPayment1, DateOfPayment2,
DateOfPayment3, and so on -- and that this will probably cause trouble
for you down the road. I'm not intending to harp on that now, but I
thought I should mention it.
Now, as for the relationships: even in a one-to-one relationship, one
table is primary and the other is secondary (that may not be the
technical term, but it's how I think of it). Generally, if one table
the linking key field in one table is an autonumber, then that table is
primary. If the key is not autonumber in any of the tables, I think
Access decides which is primary based on which table's key field is
dragged to the other to create the relationship in the relationship
window, but I don't remember exactly how that works. At any rate, you
can create a record in the primary table without a matching record in
the secondary table, but not vice versa. After looking at your
relationship diagram, I'm going to assume that tblVisits is primary in
these one-to-one relationships. That means that you can't add a record
to tblPayments or tblBillingInfo without a matching record in tblVisits.
That makes sense, though as I said, I wouldn't have structured my tables
this way.
Now, you can build an updatable query that joins all three tables on
VisitID, and if you set it up right you will be able to update fields
from all three tables. I believe that query must include the VisitID
field from tblVisits, along with whatever other fields you want from the
three tables. The way it will work, in the absence of any special code,
is that
1. You can't add new payment or billing data for a new visit unless
you *first* enter something in one of the fields from tblVisits, so that
a VisitID is created.
2. If you don't "dirty" any of the fields that come from
tblPayments, no record will be created in that table.
3. Similarly, if you don't "dirty" any of the fields that come from
tblBillingInfo, no record will be created in that table.
On the other hand, I don't see any reason to add a dummy record to those
tables. If your form is based on a query that left-joins tblVisits to
each of the other tables, your forms for payments and billing info will
still show a record for every visit, with fields for you to fill in the
info that will go in the other tables.
If you really do feel a need to have a record in tblPayments and
tblBillingInfo to match every record in tblVisits, all you really have
to do is programmatically "dirty" one field from each table in the
form's BeforeUpdate event. For example, you might have code like this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
' Force save of BillingInfo record
Me.CPT1 = Me.CPT1
' Force save of payment record
Me.DateOfPayment1 = Me.DateOfPayment1
End Sub
If you do that, you don't need to use DAO to add records to these
tables. The act of setting these fields to their existing values (even
if those values are Null) is enough to force Access to create matching
records in those tables.
