One to One Relationship

[Zanstemic]

I need some help with how to best approach two tables that are related as a
one to one relationship. I'm really not sure if this is the best approach.

On the main form I have a command button that launches the related form.
This works fine when the related record exists and has the AuthorizationID
matching

Dim AuthID As Long
Dim TripAuthID As String
AuthID = Me.txtAuthorizationID


' this saves the record prior to opening TripReport Form
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

DoCmd.OpenForm "TripReport", , , "[TripReport.AuthorizationID] =
[Forms]![Authorization]![txtAuthorizationID]"


I'm having difficulty when the record does not exist and one needs to be
created. When the TripReport Form loads, I have the following which does not
seem to run:

Dim AuthID As Integer
Dim TripAuthID As Integer

AuthID = [Forms]![Authorization]![txtAuthorizationID]
TripAuthID = Me.AuthorizationID

IsNull(TripAuthID) = True Then
DoCmd.GoToRecord , , acNewRec
TripAuthID = AuthID
Else
End If

End Sub

Any suggestions are appreciated.

 

[John W. Vinson]

I need some help with how to best approach two tables that are related as a
one to one relationship. I'm really not sure if this is the best approach.

Unless you're familiar with the terms "Subclassing" and "Table-based field
level security" it probably isn't.

On the main form I have a command button that launches the related form.
This works fine when the related record exists and has the AuthorizationID
matching

Dim AuthID As Long
Dim TripAuthID As String
AuthID = Me.txtAuthorizationID


' this saves the record prior to opening TripReport Form
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Equivalently with more modern code,

DoCmd.RunCommand acCmdSaveRecord

DoCmd.OpenForm "TripReport", , , "[TripReport.AuthorizationID] =
[Forms]![Authorization]![txtAuthorizationID]"

Two questions:

1. Have you considered using a Subform with AuthorizationID as the
Master/Child Link Field?

2. Why are you using a 1 to 1 *at all*? They're very rarely either necessary
or appropriate. If you can't include all the fields in one table, then you
almost certainly have incorrectly normalized tables; if you can include all
the fields in one table, why not just do so?