Beforeinsert syntax

[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

[orange]

Duplicate posting is frowned upon.
https://www.accessforums.net/program...ert-21900.html