v lookup problem
 

Greeting,
I have an excel sheet for inputting employees data. In this sheet there is a
command to open a form to input data and there is a textbox which is employee
id. What I want to do is to check the employee id if it is already excess, a
msgbox shows and tell the user that it is already excess any should be
change. There is a problem with my code but I don't where it is.
Can any body help me please?

Private Sub TextBox3_Change()
If Not IsNull(vLookup("[ID]", "[Data]", "[ID] = " & [TextBox3])) Then

MsgBox "Sorry€¦ the ID is already input please reenter the Id " _
, vbOKOnly + vbCritical, _
"Duplicated ID"

DoCmd.CancelEvent

End If

v lookup problem
 

Are you sure you didn't want to post this question in an Access forum?

That DoCmd doesn't look like excel...

But if you really meant excel...

Option Explicit
Private Sub TextBox3_Change()

dim Res as variant
dim RngToCheck as range

with worksheets("somesheetnamehere")
set rngtocheck = .range("a1",.cells(.rows.count,"A").end(xlup))
end with

res = application.match(textbox3.value, rngtocheck, 0)

if iserror(res) then
'not found
else
'it matched
end if

End Sub

And are you really sure you want this to run with each change to the textbox?



ghost wrote:

Greeting,
I have an excel sheet for inputting employees data. In this sheet there is a
command to open a form to input data and there is a textbox which is employee
id. What I want to do is to check the employee id if it is already excess, a
msgbox shows and tell the user that it is already excess any should be
change. There is a problem with my code but I don't where it is.
Can any body help me please?

Private Sub TextBox3_Change()
If Not IsNull(vLookup("[ID]", "[Data]", "[ID] = " & [TextBox3])) Then

MsgBox "Sorry€¦ the ID is already input please reenter the Id " _
, vbOKOnly + vbCritical, _
"Duplicated ID"

DoCmd.CancelEvent

End If

--

Dave Peterson