Am using the below borrowed code to check for duplicates The Main Menu has a Command button that opens the form to a New record and the Code Fires but of course I get a No Current record Error 3021 , If the form is opened showing ALL records the Cose works just fine and I am taken to the Record. Is there a way to open the Form (it is recognizing the Duplicate) when the acformAdd button is used in place of viewing ALL records?



Thank You

Is SID text .. or a date ???
You have it dim'd as a String .. then are using octothorps as delimters on down in the code.

RDH

Edited by: R. Hicks on Thu Jul 5 21:53:48 EDT 2007.

Thank You Ricky

Sorry for the delay thought I had responded earlier. Anyway Yes It is a date and I have changed accordingly in the Dim'd line but same result errors out on this line

Me.Bookmark = rsc.Bookmark

Ricky,

Yes it is a date, I have changed the Dimd line accordingly however still the same result. Errors out on this line

Me.Bookmark = rsc.Bookmark

Something screwy going on here, I look at the post and my reply is not there so I repost with a reply and the original reply is there ??? Strange

Edited by: jmcwk on Thu Jul 5 22:16:27 EDT 2007.

John -

Try:

stLinkCriteria = "[chartdate] = #" & SID & "#"

hth,
Jack

John these are my thoughts...

Because you are using acFormAdd when opening the form you are not going to see previously entered records. Therefore when you run your RecordsetClone for your form it would not allow you to find a previous record.

This is why when you open the form showing all records your code works.

If this proves to be in fact the case I would try the following steps.
1. Write your code first to find out if the value (i.e your date I believe) exists by looking at the "Record Source" (table or query) based on your form.
2. If your value exist then undo the value and change the form from being data entry then go to the relevant record.


Steps


Dane

Dane,

Something like this? Assuming as you suggest the User Is opening a New Blank record


Private Sub ChartDate_BeforeUpdate(Cancel As Integer)
'*********************************
'Code sample courtesy of srfreeman
'*********************************

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

Set rsc = Me.RecordsetClone

SID = Me.ChartDate.Value
stLinkCriteria = "[chartdate]=" & "#" & SID & "#"

'Check StudentDetails table for duplicate StudentNumber
If DCount("chartdate", "tblcharts", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning This Chart Date " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", vbInformation _
, "Duplicate Information"
'Go to record of original Chart Date
Me.form.dataentry=false
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub

Jack,

Sorry for the delay Same result using stLinkCriteria = "[chartdate] = #" & SID & "#"
Dane explains exactly the whys as I figured it to be, I just can not or have not figured out yet how to code to handle looking for the Duplicate from a New (stand alone) Blank Record.

Looks good except for the criteria. Jack mentioned it earlier maybe you forgot to include it. Test all aspects and see if it works.


I just made some changes,put the Set rsc = Me.RecordsetClone after the Me.form.dataentry
Dane

Edited by: dallr on Fri Jul 6 0:11:23 EDT 2007.

Dane,

Works perfectly I really appreciate that !

Thank You

John, No problem Glad to assist.

Dane