Error 2471 In an Event Procedure 2

[quest4]

I have a form, on which I have a command button called Add, which is simply the Add New Record function. I have a procedure in the AfterUpdate event:
Private Sub EmployeeNumber_AfterUpdate()
Dim MyCheckDb1 As String
Dim MyCheckDb2 As String

MyCheckDb1 = DLookup("[EmployeeNumber]", "tblEmployees", _
"[NumberToBeAdded] = [EmployeeNumber]&quot

MyCheckDb2 = DLookup("[EmployeeNumber]", "tblAttArchive", _
"[NumberToBeAdded] = [EmployeeNumber]&quot

If Not IsNull(MyCheckDb1) Or Not IsNull(MyCheckDb2) Then
MsgBox "This Employee Number is already assigned." _
& vbNewLine & vbNewLine & [NumberToBeAdded]
End If

Exit_EmployeeNumber_AfterUpdate:
Exit Sub

Err_EmployeeNumber_AfterUpdate:
MsgBox Err.Description
Resume Exit_EmployeeNumber_AfterUpdate

End Sub
This is just to check to see if the EmployeeNumber that I am entering, already exists. I entered a EmployeNumber which I know exists, I get an error message 2471, The Object doesn't contain the Automation object: "NumberToBeAdded" I got this in a previous thread and I have tried several things to get it to work, but to no avail. Thanks to anyone helping out.

 

[Maquis]

That just means that NumberToBeAdded is not a field in your table.

Double check the spelling of the field. Perhaps it's just a typo. Maq B-)
<insert witty signature here>

 

[quest4]

Maq, thanks for the response. Would that be the name on the form? Thanks for the response.

 

[Maquis]

Nope, read my post. Dlookup references the field in the table.

Check out Access help for more info on the syntax of Dlookup. Maq B-)
<insert witty signature here>

 

[quest4]

OK I got it working, after some strange use of brackets. But after clicking on OK in the message box, instead of going back to the text box, EmployeeNumber, it is going to the next step in the form, NameFirst, with the duplicate EmployeeNumber still there. Is there a way to get it to re-enter the EmployeeNumber? Thanks again for all of the help.

 

[Maquis]

I've had trouble with that as well. you need to set the focus back to the employee number box when you display the error message. Maq B-)
<insert witty signature here>

 

[quest4]

This just keeps getting better and better. I entered a EmployeeNumber, which I know doesn't exist, and I get an error message invalid use of Null. It figures that ithe last thing you do in a dbase screws-up the most, doesn't it? Any ideas on the Null? Thanks again for the help.

PS I have already tbeen to MS help and it was no help at all.

 

[quest4]

Maq, thanks again for the response. Wouldm't that vbNewLine have something to do with where it is going? Thanks again for all of the help.

 

[Maquis]

You didn't say which line gave you the error. If it's the msgbox line then I would suggest not even displaying the employee number in it. After all the user should know what they just typed in.


Maq B-)
<insert witty signature here>

 

[quest4]

Thanks for the response, Maq. The error was an error 94 and the debugger when right to and hi-lighted the entire MyCheckDB1= clause. Maq, you are assuming intelligentence, but this is the PR/HR deptment and believe me they are the least computer literate people. They have to see every detail spelled or they think it is not there, and besides this would be a new employee and all of the data/info the our company has about him. I did try help on vbNewLine and nothing. Thanks again for everything.

 

[CosmoKramer]

quest4,

Here is a partial example of some code I use in a time & attendance system. Maybe some of this can help you....

Dim strMsg1 as String, strFindEmpl as String
......
strMsg1 = "Can't add employee number " & Forms!frmEmplMasterMain!txtEmpID & " , it already exists"
If Not (IsNull(strFindEmpl = DLookup("[empl_num]", "tblMaster", "[empl_num] = '" _
& Forms!frmEmplMasterMain!txtEmpID & "'"))) Then
  MsgBox (strMsg1)
  Forms!frmEmplMasterMain!txtEmpID = ""
  Forms!frmEmplMasterMain!txtEmpID.SetFocus
  Forms!frmEmplMasterMain.Refresh
  GoTo Exit_cmdAddEmp_Click
End If
.....
.....

 

[quest4]

Thanks Cosmo. But I above one is almost working. It does work correctly when I enter a bad EmployeeNumber, but it gives me an &quot;error 94, Invaild use of Null&quot; when I enter a good or valid EmployeeNumber. I did substitue the NumberToBeAdded with the full path of the form's text box name. When a bad EmployeeNumber is enntered the message does pop-up, it would be nice if it went back to EmployeeNumber, but it goes foreward, but then maybe I should be run this on LostFocus instead or maybe instead of vbNewLine use vb PreviousLine. Thanks again for the help.