prevent Dublicate ID by Date()

hardyise

Registered User.
Local time
Today, 21:59
Joined
May 26, 2016
Messages
15
i want do not let input id twice in the same Date
the problem is red color i don't know how to solve this issue

Private Sub employee_ID_AfterUpdate()
Dim NewId As String
Dim strLink As String

NewId = Me.employee_ID.Value
strLink = "[employee ID]=" & NewId
If Me.employee_ID = DLookup("[employee ID]", "hatn w roshtn", strLink And "[barwar]=#" & Date&) Then
MsgBox "ffffff"
End If
End Sub
 
try

strLink & " And [barwar]=" & Date())

however confused about Date& - if you mean today, then use as above, if you mean it is the name of a control on your form or something else, please clarify - note that Date is a reserved word and should not be used for the names of fields or controls
 
first of all thanks for answer
then when i type your code the accesss automatically changed to strLink & " And [barwar]=" & Date
when i check it
i have runtime error 13 type mismatch

yes date is today
and [barwar] is field in the same table (hatn w roshtn)
 
the change is OK - that is VBA doing a correction. The error means the Barwar is not a date

Also, your table name has spaces (not a good idea) so you need to use square brackets

"[hatn w roshtn]"
 
now i dont have error but if function not work
for example in table (hatn w roshtn) i have employee ID=3333333 and barwar=30/05/2016
but when i use input form they let me again use 3333333
 
well today is 1st June, not 30th May
 
:) yes you right
but i change my computer date to 30/05/2016
then i change it to 1/06/2016
and i manually in put id=1111111 and barwar=1/06/2016
and then used form input
still the same problem
 
perhaps you barwar field has a time element as well - how did you update it?
 
in form when someone input ID
automatic fill the [barwar] to today
 
automatic fill the [barwar] to today
using what function?

you've dimmed newid as a string - does that mean employee id is defined as text in your table?
 
thanks again my friend for your reply
in previous i used micro SetValues Function in after update
but now i remove it because in micro i can't prevent duplicate id by date
now i am using VBA after update

now the [barwar] field doesn't update automatically
after i solve this problem i can make it fill
like this Me.barwar = date()

employee id defined as number
 
now i am using VBA after update
if this is the form afterupdate event - it should be the beforeupdate event. Alternatively, set the default value for the field in the table to Date()
 
because it is triggered before the update - and you want to add the value before you update, the afterupdate event is triggered after the update., so too late.
 
my friend let's focus on my problem now and try to solve it
after that if again i have problem
you or anyone that like to help others help me :)

my code work perfect by this way :-

Private Sub employee_ID_AfterUpdate()
Dim NewId As String
Dim StLink As String

NewId = Me.employee_ID.Value
StLink = "[employee ID]=" & NewId & ""

If Me.employee_ID = DLookup("[employee ID]", "hatn w roshtn", StLink) Then
MsgBox "ggggg", vbInformation, "dobara"
Me.Undo
End If
End Sub


but when i add date() function as prevent by input the same ID by date
the code is :

Private Sub employee_ID_AfterUpdate()
Dim NewId As String
Dim StLink As String

NewId = Me.employee_ID.Value
StLink = "[employee ID]=" & NewId & ""

If Me.employee_ID = DLookup("[employee ID]", "hatn w roshtn", StLink And "[roshtn]=" & Date) Then
MsgBox "ggggg", vbInformation, "dobara"
Me.Undo
End If
End Sub

i get the error message say
Run time error '13': Type Mismatch
 
i get the error message say
Run time error '13': Type Mismatch
if it is perfect, why are you getting an error? the bit highlighted in red was answered back in post#2.
 
my friend as i say without using the highlighted in red code is work but when i add date function to it
i get the error message 13
i think that solution you gave to me is not work
by writing code like post#2 the whole code is neglected by system and run like nothing happen :banghead:
 
please show the revised code you have used but does not work
 
Be sure to make (EmployeeID, barwar) a key if appropriate. Uniqueness in general should be enforced through a unique constraint/index and not just in a form. There's no harm in adding an extra check into the form as well if you wish, but arguably it might be better to implement the composite key first because it's much simpler to do.
 
CJ_London
this is the code but not work
my Database size is 3.5MB i don't now this size is allowable to share if its ok
i sent it to you if you can check it for me by yourself

Private Sub employee_ID_AfterUpdate()
Dim NewId As String
Dim StLink As String

NewId = Me.employee_ID.Value
StLink = "[employee ID]=" & NewId & ""

If Me.employee_ID = DLookup("[employee ID]", "hatn w roshtn", StLink & " And [barwar]=" & SysDate) Then
MsgBox "ggggg", vbInformation, "dobara"
Me.Undo
End If
 

Users who are viewing this thread

Back
Top Bottom