One-to-one relationships with DAO

[bwion]

I have a table that is in a one-to-one relationship with another table. I use
the one-to-one relationship to store the comments (memo data-type) of the
other table, so I can enforce record-level locking. For example:

tblTransactions:
TransactionID - 1
CustomerID
InventoryID
Quant

tblTransactionComments:
TransactionID - 1
TransactionComments

My question is how can I use DAO to save a users input on a form to these
tables? If the comment field were in the tblTransaction it would be something
like this:

Set rstWrite = dbs.OpenRecordset("tblTransaction", dbOpenDynaset)
rstWrite.AddNew
rstWrite!CustomerID = lngVar
rstWrite!InventoryID = lngVar2
rstWrite!Quant = intVar
rstWrite!Comments = Me.txtComments
rstWrite.Update
rstWrite = Nothing

Or something to that effect. But since I am dealing with a one-to-one
relationship with tblTransactionComments and storing the comments in that
table, I cannot run "rstWrite.Update" without throwing an error (because I am
enforcing referential integrity) because there isn't a matching record in the
tblTransactionComments. What would be the best and more efficient way to save
the information? Is there a way to save the TransactionID to the
tblTransactions and tblTransactionComments at the same time? Any help is
appreciated! Thanks.

Ben

 

[Allen Browne]

Just use a form bound to tblTransactions, with a subform bound to
tblTransactionComments.

The subform will be in Form view, without navigation buttons etc, and Cycle
set to just this one record. The subform's LinkMasterFields and
LinkChildFields will be TransactionID.

The user may not even realize that the TransactionComments box is in a
subform. But as soon as they click in there, Access saves the main record,
and is ready to accept the related record.

 

[bwion]

Hey Allen,

Thanks for the advice.

I used transactions as a hypothetical example. I actually have the form
saving to multiple tables based on a user selection of an option group. For
example, if the user selects option 1 and then clicks the save record command
button I have it save the pertinent information to table 1. If the user then
selects option 2, some of the controls on the form hide, label captions
change, etc. and then when the user hits the save record command button it
saves the pertinent information to table 2 (different subjects, so they got
different tables and I didn't think the change was too huge to justify
separate forms) . I don't actually want to save the record until the user
hits that command button because I have a lot of data validation code that
runs before it saves the record. I am using the access form mainly as a
placeholder for data and then using VB code for data validation and DAO for
the link to the actual database. Any other ideas?

Thanks a bunch,

Ben

 

[Allen Browne]

You could use an unbound text box for the 'comments', and then use the
AfterUpdate event procedure of the *form* to execute an action query
statement in accord with the user's desires. The action query could be an
append (for a new comment), a delete (if the comment were cleared), or an
update (if an existing comment were modified.) You would also need to use
the form's Current event to populate the unbound text box.

I can't see your scenario, but I can't imagine that scenario is really
justified. Unless you are subclassing, there might be a simpler way to
design the tables.

 

[bwion]

Hey Allen,

Thanks for the tips. The table structure is justified (unfortunately, I
would much rather like simpler tables too, but thats what you get with a
company that doesn't think through processes completely before instating
them).
I figured it out. I ended up just making a query with those two tables inner
joined that way once it created an autonumber ID in "tblTransactions," it
also wrote that ID to "tblTransactionComments." Once again, thanks for your
help. All those tips will get stored away for other projects and new
developments.

Ben