prevent Dublicate ID by Date() (1 Viewer)

hardyise · Jun 1, 2016

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

CJ_London · Jun 1, 2016

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

hardyise · Jun 1, 2016

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)

CJ_London · Jun 1, 2016

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]"

hardyise · Jun 1, 2016

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

CJ_London · Jun 1, 2016

well today is 1st June, not 30th May

hardyise · Jun 1, 2016

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

CJ_London · Jun 1, 2016

perhaps you barwar field has a time element as well - how did you update it?

hardyise · Jun 1, 2016

in form when someone input ID
automatic fill the [barwar] to today

CJ_London · Jun 2, 2016

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?

hardyise · Jun 2, 2016

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

CJ_London · Jun 2, 2016

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()

hardyise · Jun 2, 2016

why it should be the beforeupdate event?

CJ_London · Jun 2, 2016

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.

hardyise · Jun 4, 2016

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

CJ_London · Jun 4, 2016

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.

hardyise · Jun 6, 2016

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:

CJ_London · Jun 6, 2016

please show the revised code you have used but does not work

ButtonMoon · Jun 6, 2016

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.

hardyise · Jun 6, 2016

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

prevent Dublicate ID by Date() (1 Viewer)

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Registered User.

Similar threads

Users who are viewing this thread