VBA code syntac doubt "Before Insert"

[ramindya]

There are three tables in SQL server - Patient, TableA and TableB.
Table A has ID which is primary key, visit number and MRN ( foreign key ).The fields are displayed in a subfrmTableA embedded in the Mainfrm
Patient has MRN as the Primary Key. The fields are displayed in Mainfrm
TableB has ID which is primary key, visit number and MRN ( foreign key ) and someother fields. The fields are displayed in a form frmTableB which is called by a button click event from subfrmTableA

The following code increments the Visitnumber for each patient in the TableA and I want the same visitnumber to be tranfered in the visitnumber field of TableB.



What is the VBA code for that ? This code works perfectly but only inserts in TableA.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.VisitNumber = Nz(DMax("[VisitNumber]", "dbo_tblScheduling", "[MRN] =" & [Forms]![frmMainBase]![MRN]) + 1, 1)
End Sub

[June7]

Saving to a table that is not part of the form's RecordSource requires UPDATE or INSERT sql action, depending whether the record already exists in the table.

Sounds like data structure is not optimized. Why two tables A and B?