Help with code, prevent dup's via form.

R

Randy

I copied this code off the internet and revised it slightly to work with
preventing duplicate certificate nubers via form. Works great. I'm trying
to revise it again to work on my employee time sheet database. I need it to
prevent duplicate dates for the same employee. I need some way to add [EID]
(employee ID) to the code so that only one employee per date can be entered
in the database. Somthing like: [Current_Date] & [EID] Thanks...Randy

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

Set rsc = Me.RecordsetClone

SID = Me.CurrentDate.Value
stLinkCriteria = "[Current_Date]=" & "'" & SID & "'"

'Check IDRa table for duplicate date for same employee
If DCount("Current_Date", "IDRa", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "WARNING! Duplicate Entry. This Employee " _
& SID & " has already been entered for this date." _

'Go to record of original employee date worked

End If

Set rsc = Nothing
End Sub
 
T

tina

is the [Current_Date] field in the table a Date/Time data type? and is the
user entering a date value in the Me!CurrentDate control? if so, then the
expression in your posted code should be

Dim SID As Date

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

to test for the employee ID also, try

stLinkCriteria = "[Current_Date] = #" & SID _
& "# And [EID] = " & Me.EIDControlName

the above assumes that EID is a number value. if it's Text data type, then
try

stLinkCriteria = "[Current_Date] = #" & SID _
& "# And [EID] = '" & Me.EIDControlName & "'"

either way, don't forget to include

Cancel = True

in the code, before the Undo or other actions.

another way to handle this issue is to simply add a unique index to the
table, composed of the Current_Date and EID fields. when a dup index is
entered, an error will be generated on the form. you can trap the error, as

Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3022 Then
Response = acDataErrContinue
MsgBox "Your message here."
Me.Undo
End If

End Sub

hth
 
R

Randy

Thank you Tina, I went with the index approach...Randy
tina said:
is the [Current_Date] field in the table a Date/Time data type? and is the
user entering a date value in the Me!CurrentDate control? if so, then the
expression in your posted code should be

Dim SID As Date

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

to test for the employee ID also, try

stLinkCriteria = "[Current_Date] = #" & SID _
& "# And [EID] = " & Me.EIDControlName

the above assumes that EID is a number value. if it's Text data type, then
try

stLinkCriteria = "[Current_Date] = #" & SID _
& "# And [EID] = '" & Me.EIDControlName & "'"

either way, don't forget to include

Cancel = True

in the code, before the Undo or other actions.

another way to handle this issue is to simply add a unique index to the
table, composed of the Current_Date and EID fields. when a dup index is
entered, an error will be generated on the form. you can trap the error,
as

Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3022 Then
Response = acDataErrContinue
MsgBox "Your message here."
Me.Undo
End If

End Sub

hth


Randy said:
I copied this code off the internet and revised it slightly to work with
preventing duplicate certificate nubers via form. Works great. I'm trying
to revise it again to work on my employee time sheet database. I need it to
prevent duplicate dates for the same employee. I need some way to add [EID]
(employee ID) to the code so that only one employee per date can be entered
in the database. Somthing like: [Current_Date] & [EID] Thanks...Randy

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

Set rsc = Me.RecordsetClone

SID = Me.CurrentDate.Value
stLinkCriteria = "[Current_Date]=" & "'" & SID & "'"

'Check IDRa table for duplicate date for same employee
If DCount("Current_Date", "IDRa", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "WARNING! Duplicate Entry. This Employee " _
& SID & " has already been entered for this date." _

'Go to record of original employee date worked

End If

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

tina

you're welcome :)


Randy said:
Thank you Tina, I went with the index approach...Randy
tina said:
is the [Current_Date] field in the table a Date/Time data type? and is the
user entering a date value in the Me!CurrentDate control? if so, then the
expression in your posted code should be

Dim SID As Date

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

to test for the employee ID also, try

stLinkCriteria = "[Current_Date] = #" & SID _
& "# And [EID] = " & Me.EIDControlName

the above assumes that EID is a number value. if it's Text data type, then
try

stLinkCriteria = "[Current_Date] = #" & SID _
& "# And [EID] = '" & Me.EIDControlName & "'"

either way, don't forget to include

Cancel = True

in the code, before the Undo or other actions.

another way to handle this issue is to simply add a unique index to the
table, composed of the Current_Date and EID fields. when a dup index is
entered, an error will be generated on the form. you can trap the error,
as

Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3022 Then
Response = acDataErrContinue
MsgBox "Your message here."
Me.Undo
End If

End Sub

hth


Randy said:
I copied this code off the internet and revised it slightly to work with
preventing duplicate certificate nubers via form. Works great. I'm trying
to revise it again to work on my employee time sheet database. I need
Click to expand...
it
to
prevent duplicate dates for the same employee. I need some way to add [EID]
(employee ID) to the code so that only one employee per date can be entered
in the database. Somthing like: [Current_Date] & [EID] Thanks...Randy

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

Set rsc = Me.RecordsetClone

SID = Me.CurrentDate.Value
stLinkCriteria = "[Current_Date]=" & "'" & SID & "'"

'Check IDRa table for duplicate date for same employee
If DCount("Current_Date", "IDRa", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "WARNING! Duplicate Entry. This Employee " _
& SID & " has already been entered for this date." _

'Go to record of original employee date worked

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

Run time error 2001 3
Help with code to prevent Dups. 2
Merge two codes together? 1
Preventing Duplicates from being entered 3
Preventing duplicate entries 10
Edit code to use integer instead of text 1
String problem 1
Problem with code to catch duplicate Primay Key 22

Top