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