Runtime error in preventing duplicates code

[emilyrogers]

I have this code(below) and when i try and activate it it come up with runtime error- 3021 no current record and when i debug it highlights Me.Bookmark = rsc.Bookmark and i don't know what to do because i am very new to access. Please tell me in very simple terms. The code works until i use it in the switchboard and then the error appears.

Private Sub Document_ID_BeforeUpdate(Cancel As Integer)


Dim SID As String
Dim stLinkCriteria As String


Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me.[Document ID].Value
stLinkCriteria = "[Document ID]=" & "'" & SID & "'"
'Check [ECNs Pending] table for duplicate [Document ID]
If DCount("[Document ID]", "[ECNs Pending]", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Document ID " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", _
vbInformation, "Duplicate Information"
'Go to record of original Document ID
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End Sub

[NoellaG]

Hi Emily,

I never use the build-in switchboard manager but build my own menu's and menuform, but basically a switchboard is a form with buttons that allow the user to navigate along the application. It doesn't have a dataset of its own.
So if you set rsc as a clone of that form, and then filter it even further I would expect that you get an empty dataset as result, so indeed there is no current record to work with.
This code should be used in the data-entry form, not the switchboard.

gr
NG

[emilyrogers]

So how do i implement my code into the switch board. Basically i want an error box to pop-up when a user trys to put in a document id that has already been used and then i want them to be shown the original record.
I can make it work in the tables with the information but i dont know how to link this into the main switch board.

[NoellaG]

Hi emili,

that depends of your forms and the code in it. If you're working from the switchboard you'll probably have to call to the recordsets through ADO, not with the recordsetclone property.

gr
NG

[emilyrogers]

so it should be this?

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As ADO.Recordset

Set rsc = Me.Recordset

SID = Me.[Document ID].Value
stLinkCriteria = "[Document ID]=" & "'" & [SID] & "'"


If DCount("[Document ID]", "[ECNs Pending]", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning [Document ID] " & [SID] & " has already been entered." & vbCr & vbCr & "", vbInformation, "Duplicate Information"
'Go to record of original [Document ID]
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

End If

Set rsc = Nothing

End Sub

[emilyrogers]

With this code i just keep getting the me.bookmark=rsc.bookmark highlighted and saying that there is no current record. I dont know what to do because i have only been using ms access since midday yesterday!


Private Sub Document_ID_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.Recordset

SID = Me.[Document ID].Value
stLinkCriteria = "[Document ID]=" & "'" & [SID] & "'"


If DCount("[Document ID]", "[ECNs Pending]", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning [Document ID] " & [SID] & " has already been entered." & vbCr & vbCr & "", vbInformation, "Duplicate Information"
'Go to record of original [Document ID]
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

End If

Set rsc = Nothing

End Sub

[NoellaG]

Hi,

assuming there is a control on your switchboard named txtDocID that contains the ID of the document that has to be controlled:

Dim rsc As New ADODB.Recordset

rsc.Open "select * from tbl??? where [Document ID]= """ & Me.txtDocID & """", CurrentProject.Connection, adOpenDynamic, adLockReadOnly

With rsc
If .BOF And .EOF Then 'no records
.... do here what has to be done if no records are found
Else
.MoveFirst
.... do here what has to be done with the doubles, like opening the form with the focus on that double
End If
End With
rsc.Close



Set rsc = Nothing

[emilyrogers]

But the whole point of my code is that on entering the document ID they will get a pop-up ONLY if there is already a record with that document ID on the system. I am sorry to be slow but i am very very new to ms access.

[NoellaG]

Hi, then only use:

With rsc
If not(.BOF And .EOF) Then
.MoveFirst
.... do here what has to be done with the doubles, like opening the form with the focus on that double
End If
End With

[emilyrogers]

thank you for your help. now i am getting an error on Me.Bookmark = rs.Bookmark saying object required!
I am so confused as i dont know what any of this means!


Private Sub Document_ID_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As New ADODB.Recordset

rsc.Open "select* from [ECNs Pending] where [Document ID]= """ & Me.[Document ID] & """", CurrentProject.Connection, adOpenDynamic, adLockReadOnly

Set recClone = Me.RecordsetClone

SID = Me.[Document ID].Value

stLinkCriteria = "[Document ID]=" & "'" & SID & "'"
'Check [ECNs Pending] table for duplicate [Document ID]

If DCount("[Document ID]", "[ECNs Pending]", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo

'Message box warning of duplication
MsgBox "Warning Document ID " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", _
vbInformation_, "Duplicate Information"

'Go to record of original Document ID
recClone.FindFirst stLinkCriteria
Me.Bookmark = rs.Bookmark
End If

rsc.Close

Set rs = Nothing

End Sub

[NoellaG]

Emily,

the solution I provided has nothing to do with recordset clones and bookmarks, so you can't use those with ADODB recordsets. ADODB goes directly to the recordsets in the table and disregards the form.
It's difficult to really help without knowing more or seeing your app.

gr
NG