I have a EOF error in vba code and not sure why.

[Troy R]

Private Sub Add_new_button_Click()

DoCmd.SetWarnings False

Dim SQL As String
Dim fName As String
Dim lName As String
Dim fGroup As String
Dim Shift As String
Dim mNum As String
Dim hNum As String
Dim email As String
Dim position As String
Dim Notes As String

If IsNull([Staff First Name]) = True Then
MsgBox "*Staff First Name REQUIRED*"
Else
fName = [Staff First Name]

If IsNull([Staff Last Name]) = True Then
MsgBox "*Staff Last Name REQUIRED*"
Else
lName = [Staff Last Name]

If IsNull([Staff Email]) = True Then
MsgBox "*Staff Email REQUIRED*"
Else
email = [Staff Email]

If IsNull([Staff Position]) = True Then
position = "null"
Else
position = [Staff Position]

If IsNull([Facillity Group]) = True Then
MsgBox "*The Program Is REQUIRED*"
Else
fGroup = [Facillity Group]

If IsNull([Notes]) = True Then
Notes = "null"
Else
Notes = [Notes]

If IsNull([Staff Mobile Number]) = True Then
mNum = "null"
Else
mNum = [Staff Mobile Number]

If IsNull([Staff Home Number]) = True Then
hNum = "null"
Else
hNum = [Staff Home Number]

'The part in question starts from here-->
SQL = "[Staff First Name]='" & fName & "' AND [Staff Last Name]='" & lName & "' & _
[Staff Mobile Number]= " & mNum & " And [Staff Home Number] = '" & hNum & "' & _
[Staff Email] = '" & email & "' And [Staff Position] = '" & position & "' & _
[Facillity Group] = '" & fGroup & "' And [Notes] ='" & Notes & "' "
'to here--->
If (DCount("*", "Staff", SQL) > 0) Then
MsgBox "Duplicate Information! Staff already in System."
Else
SQL = "Insert INTO Staff([Staff First Name], [Staff Last Name], [Staff Mobile Number], [Staff Home Number], [Staff Email], [Staff Position], [Facility Group], [Notes])" & _
"VALUES ('" & fName & "' , '" & lName & "', '" & mNum & "','" & hNum & "', '" & email & "', '" & position & "','" & fGroup & "', '" & Notes & "')"


DoCmd.RunSQL SQL

MsgBox "Insert of Staff Complete"
DoCmd.GoToRecord , [Staff Form], acNewRec

End If
End If
End If
End If
End If
End If
End If
End If
End Sub

[twinnyfo]

Troy,

First, a reminder to include your code within the Code tags, please.

Second, where in your code is the EOF error tripping?

Thanks!

[zmbd]

First: You must use the [code][/code] tags.
http://bytes.com/topic/access/answer...-posting-forum

Second: Where in the code does your error happen... we simply can not compile your code on our PC and run it.

Third:

'The part in question starts from here-->
SQL = "[Staff First Name]='" & fName & "' AND [Staff Last Name]='" & lName & "' & _
[Staff Mobile Number]= " & mNum & " And [Staff Home Number] = '" & hNum & "' & _
[Staff Email] = '" & email & "' And [Staff Position] = '" & position & "' & _
[Facillity Group] = '" & fGroup & "' And [Notes] ='" & Notes & "' "
'to here--->

Please post the resulting string...
Add a "Stop" command following this string.
When the execution stops, in the immediate (debug) window type: "?SQL" (without the quotes) Post back the string.

Fourth: My guess is that your failing on the move to the new record... change that move to aclast.

-z