Hello I have a felling that this is a rather simple fix but I am very new to VB coding and I have exhausted all that I can think of to fix this error.
This Database refers to keeping track of some Ex-Offender information for my agency. I have a table that contains all the demographic information on the individual; they have a unique ID call a SOID number. This number field has to be typed in manually and cannot be a autonumber. So what I wanted to do is if an employee tries to enter a SOID number of an individual that already exists in the system I want to display a mesg box informing them of the duplicate, delete the duplicate information they entered then transfer them to the existing record with the matching SOID. I know the code worked on another database that I created but that particular one was much simpler and didn't involve relating the SOID number to multiple tables. How do I define which table the 'rsc.FindFirst' statement? Thanks in advance for the help, below is the error mesg that I get and the corresponding code that I am using. The text highlighted in red is what the debug function says there’s something wrong with.
Run-Time error '3079'
The specified field 'SOID_Number' could refer to more than one table listed in the FROM clause of your SQL Statement.
Code:
Private Sub SOID_Number_BeforeUpdate(Cancel As Integer)
Dim SID As Long
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me![ExOffender_Info.SOID_Number]
stLinkCriteria = "[SOID_Number]=" & SID
'Check Ex Offender Table for duplicate
If DCount("ExOffender_Info.SOID_Number", "ExOffender_Info", stLinkCriteria) > 0 Then
'Undo duplicate entry
Cancel = True
Me.Undo
'Message box warning of duplication
MsgBox "WARNING!! the SOID Number " _
& SID & " already exists in the system." _
& vbCr & vbCr & "Your current entry will be deleted and you will be transfered to the matching SOID Number you entered.", _
vbInformation, "Duplicate Information"
'Go to record of original SOID'
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End Sub