Hi,
I've been trying to nut this out for days with no luck (doesn't help that I program very rarely)
I am receiving a runtime error 021. no current record. I can confirm that the record exists.
I am using a form, that when opened is set to default as a new record. There are four fields. The first two are establishment and MRN (medical Record Number), which are what the code is using to track a record. None of these are set to be a key as they need to be duplicated individually, but not as a pair.
Ideally the user enters the establishment, then enters an MRN. If the MRN and establishment already exist together, then the system should automatically bring up the other details so they can edit the record. If they don't exist as a pair, the user continues to add the details as if it was a new record.
I based my code on what scriverb used in thread702-833357. I have also tried a few other thread suggestions with no luck. Any help is greatly appreciated. Code is below.
NOTE the debug points to the line: Me.Bookmark = RecordsetClone.Bookmark
thanks
Brendan
Private Sub MRN_AfterUpdate()
Dim vMRN As String, vEstID As String
If Not IsNull(Me![MRN]) Then
If IsNull(DLookup("[MRN]", "[file_tracking]", "[MRN] = '" & Forms![File_tracking_frm]![MRN] & "' and [EstablishmentID] ='" & Forms![File_tracking_frm]![establishmentid] & "'")) Then
'add record code
Me![location_code].SetFocus
Else
vMRN = Me.[MRN]
vEstID = Me.[establishmentid]
DoCmd.SetWarnings False
' DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
Me.RecordsetClone.FindFirst "[MRN] = '" & vMRN & "' and [establishmentID] = '" & vEstID & "'"
MsgBox vMRN & " and " & vEstID
Me.Bookmark = RecordsetClone.Bookmark
Me![location_code].SetFocus
End If
Else
Me![MRN].SetFocus
End If
I've been trying to nut this out for days with no luck (doesn't help that I program very rarely)
I am receiving a runtime error 021. no current record. I can confirm that the record exists.
I am using a form, that when opened is set to default as a new record. There are four fields. The first two are establishment and MRN (medical Record Number), which are what the code is using to track a record. None of these are set to be a key as they need to be duplicated individually, but not as a pair.
Ideally the user enters the establishment, then enters an MRN. If the MRN and establishment already exist together, then the system should automatically bring up the other details so they can edit the record. If they don't exist as a pair, the user continues to add the details as if it was a new record.
I based my code on what scriverb used in thread702-833357. I have also tried a few other thread suggestions with no luck. Any help is greatly appreciated. Code is below.
NOTE the debug points to the line: Me.Bookmark = RecordsetClone.Bookmark
thanks
Brendan
Private Sub MRN_AfterUpdate()
Dim vMRN As String, vEstID As String
If Not IsNull(Me![MRN]) Then
If IsNull(DLookup("[MRN]", "[file_tracking]", "[MRN] = '" & Forms![File_tracking_frm]![MRN] & "' and [EstablishmentID] ='" & Forms![File_tracking_frm]![establishmentid] & "'")) Then
'add record code
Me![location_code].SetFocus
Else
vMRN = Me.[MRN]
vEstID = Me.[establishmentid]
DoCmd.SetWarnings False
' DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
Me.RecordsetClone.FindFirst "[MRN] = '" & vMRN & "' and [establishmentID] = '" & vEstID & "'"
MsgBox vMRN & " and " & vEstID
Me.Bookmark = RecordsetClone.Bookmark
Me![location_code].SetFocus
End If
Else
Me![MRN].SetFocus
End If