Duplicate Values

G

Guest

I have a form that the after update makes sure not to duplicate the
FileName field. For some reason I can not figure out how to open a different
form if there is a duplicate. I know I need to change this part of the code,

rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

but I'm not sure what to change it to. The form I want to open if there is
a duplicate entry is called "ERABatchFilesViewForm", and I need it to open
with the same stLinkCriteria ([FileName]) as the code below shows.

Private Sub FileName_AfterUpdate()
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.FileName.Value
stLinkCriteria = "[FileName]=" & "'" & SID & "'"

If DCount("FileName", "EraBatchFiles", stLinkCriteria) > 0 Then
Me.Undo
MsgBox " WARNING! File Number " _
& SID & " already exists." _
& vbCr & vbCr & "You will now been taken to that record. Make sure
that the file is not a duplicate by checking the file size.", vbInformation _
, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub
 
G

Guest

Use the BeforeUpdate event instead, so you can stop the upate before it happen.

Try

Private Sub FileName_BeforeUpdate()
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.FileName.Value
stLinkCriteria = "[FileName]=" & "'" & SID & "'"

If DCount("*", "EraBatchFiles", stLinkCriteria) > 0 Then
MsgBox " WARNING! File Number " _
& SID & " already exists." _
& vbCr & vbCr & "You will now been taken to that record. Make sure
that the file is not a duplicate by checking the file size.", vbInformation _
, "Duplicate Information"
Docmd.OpenForm "ERABatchFilesViewForm" , , , stLinkCriteria
Cancel = True 'Stop the save
'rsc.FindFirst stLinkCriteria
'Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub
 
G

Guest

Sorry, should have "Cancel As Integer" in the brackets

Private Sub FileName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.FileName.Value
stLinkCriteria = "[FileName]=" & "'" & SID & "'"

If DCount("*", "EraBatchFiles", stLinkCriteria) > 0 Then
MsgBox " WARNING! File Number " _
& SID & " already exists." _
& vbCr & vbCr & "You will now been taken to that record. Make sure
that the file is not a duplicate by checking the file size.", vbInformation _
, "Duplicate Information"
Docmd.OpenForm "ERABatchFilesViewForm" , , , stLinkCriteria
Cancel = True 'Stop the save
'rsc.FindFirst stLinkCriteria
'Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub



--
Good Luck
BS"D


Ofer Cohen said:
Use the BeforeUpdate event instead, so you can stop the upate before it happen.

Try

Private Sub FileName_BeforeUpdate()
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.FileName.Value
stLinkCriteria = "[FileName]=" & "'" & SID & "'"

If DCount("*", "EraBatchFiles", stLinkCriteria) > 0 Then
MsgBox " WARNING! File Number " _
& SID & " already exists." _
& vbCr & vbCr & "You will now been taken to that record. Make sure
that the file is not a duplicate by checking the file size.", vbInformation _
, "Duplicate Information"
Docmd.OpenForm "ERABatchFilesViewForm" , , , stLinkCriteria
Cancel = True 'Stop the save
'rsc.FindFirst stLinkCriteria
'Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub

--
Good Luck
BS"D


Ryan said:
I have a form that the after update makes sure not to duplicate the
FileName field. For some reason I can not figure out how to open a different
form if there is a duplicate. I know I need to change this part of the code,

rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

but I'm not sure what to change it to. The form I want to open if there is
a duplicate entry is called "ERABatchFilesViewForm", and I need it to open
with the same stLinkCriteria ([FileName]) as the code below shows.

Private Sub FileName_AfterUpdate()
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.FileName.Value
stLinkCriteria = "[FileName]=" & "'" & SID & "'"

If DCount("FileName", "EraBatchFiles", stLinkCriteria) > 0 Then
Me.Undo
MsgBox " WARNING! File Number " _
& SID & " already exists." _
& vbCr & vbCr & "You will now been taken to that record. Make sure
that the file is not a duplicate by checking the file size.", vbInformation _
, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub
Click to expand...
Click to expand...
 
G

Guest

Your code works great. Thank you for your help.

Ofer Cohen said:
Sorry, should have "Cancel As Integer" in the brackets

Private Sub FileName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.FileName.Value
stLinkCriteria = "[FileName]=" & "'" & SID & "'"

If DCount("*", "EraBatchFiles", stLinkCriteria) > 0 Then
MsgBox " WARNING! File Number " _
& SID & " already exists." _
& vbCr & vbCr & "You will now been taken to that record. Make sure
that the file is not a duplicate by checking the file size.", vbInformation _
, "Duplicate Information"
Docmd.OpenForm "ERABatchFilesViewForm" , , , stLinkCriteria
Cancel = True 'Stop the save
'rsc.FindFirst stLinkCriteria
'Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub



--
Good Luck
BS"D


Ofer Cohen said:
Use the BeforeUpdate event instead, so you can stop the upate before it happen.

Try

Private Sub FileName_BeforeUpdate()
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.FileName.Value
stLinkCriteria = "[FileName]=" & "'" & SID & "'"

If DCount("*", "EraBatchFiles", stLinkCriteria) > 0 Then
MsgBox " WARNING! File Number " _
& SID & " already exists." _
& vbCr & vbCr & "You will now been taken to that record. Make sure
that the file is not a duplicate by checking the file size.", vbInformation _
, "Duplicate Information"
Docmd.OpenForm "ERABatchFilesViewForm" , , , stLinkCriteria
Cancel = True 'Stop the save
'rsc.FindFirst stLinkCriteria
'Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub

--
Good Luck
BS"D


Ryan said:
I have a form that the after update makes sure not to duplicate the
FileName field. For some reason I can not figure out how to open a different
form if there is a duplicate. I know I need to change this part of the code,

rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

but I'm not sure what to change it to. The form I want to open if there is
a duplicate entry is called "ERABatchFilesViewForm", and I need it to open
with the same stLinkCriteria ([FileName]) as the code below shows.

Private Sub FileName_AfterUpdate()
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.FileName.Value
stLinkCriteria = "[FileName]=" & "'" & SID & "'"

If DCount("FileName", "EraBatchFiles", stLinkCriteria) > 0 Then
Me.Undo
MsgBox " WARNING! File Number " _
& SID & " already exists." _
& vbCr & vbCr & "You will now been taken to that record. Make sure
that the file is not a duplicate by checking the file size.", vbInformation _
, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

If duplicate, open a different form 1
Preventing Duplicates from being entered 3
Checking multiple fields for Duplicate 18
Problem with code to catch duplicate Primay Key 22
Data Type Mismatch in criteria 2
Find the closest Matched Record 1
DCount LookUp Issue 5
Combo Box Not In List Help 9

Top