Error 3075 Missing Operator

[Joshua]

Hello,

I am getting the error

"Run-time error '3075':

Syntax error (missing operator) in query expression 'Joshua McLemore'.

The 'Joshua McLemore' is the field lngEmpID I'm trying to insert below.

The error on debug then highlights the first db.Execute strSQL,
dbFailOnError in the code below:

Private Sub cmdBuildSchedule_Click()
Dim datThis As Date
Dim lngEvtID As String
Dim lngLocID As String
Dim lngPartsID As String
Dim lngEmpID As String
Dim lngPosID As String
Dim lngDepId As String
Dim lngTypID As String
Dim varNotes As Variant
Dim strSQL As String
Dim db As DAO.Database
Dim intDOW As Integer 'day of week
Dim intDIM As Integer 'Day in month


If Me.grpRepeats = 2 Then
If Not CheckDates() Then
Exit Sub
End If
End If
If Not CheckTimes() Then
Exit Sub
End If
If IsNull(Me.cboActID) Then
MsgBox "You must select an Event Name.", vbOKOnly + vbInformation,
"Enter Event Name"
Me.cboActID.SetFocus
Me.cboActID.Dropdown
Exit Sub
End If
If IsNull(Me.cboLocID) Then
MsgBox "You must select a Location.", vbOKOnly + vbInformation,
"Enter Location"
Me.cboLocID.SetFocus
Me.cboLocID.Dropdown
Exit Sub
End If

varNotes = Me.txtNotes
lngLocID = Me.cboLocID
lngEvtID = Me.cboActID
lngPartsID = Me.cboPartID
lngEmpID = Me.cboEmpID
lngPosID = Me.cboPosID
lngDepId = Me.cboDepID
lngTypID = Me.cboTypID


Set db = CurrentDb
If Me.grpRepeats = 2 Then 'need to loop through dates
For datThis = Me.txtStartDate To Me.txtEndDate

intDIM = GetDIM(datThis)
intDOW = Weekday(datThis)
If Me("chkDay" & intDIM & intDOW) = True Or _
Me("chkDay0" & intDOW) = True Then
strSQL = "INSERT INTO tblTempSchedDates (" & _
"tscDate, tscEvtID, tscPartsID, tscEmpID, tscLocID,
tscPosID, tscDepID, tscTypID, " & _
"tscStartTime, tscEndTime, tscCallTime, tscOutTime,
tscNotes ) " & _
"Values(#" & datThis & "#," & lngEvtID & "," &
lngPartsID & "," & lngEmpID & "," & lngLocID & "," & lngPosID & "," &
lngDepId & ", " & _
lngTypID & ", #" & Me.txtStartTime & "#, #" & _
Me.txtEndTime & "#, #" & _
Me.txtCallTime & "#, #" & _
Me.txtOutTime & "#," & _
IIf(IsNull(varNotes), "Null", """" & varNotes & """") &
")"
db.Execute strSQL, dbFailOnError
End If
Next
Else 'dates are there, just add the title, notes, times, location,
Activity
strSQL = "Update tblTempSchedDates Set tscEvtID = " & lngEvtID & _
", tscPartsID = " & lngPartsID & ", tscEmpID = " & lngEmpID &
",tscLocID = " & lngLocID & ",tscPosID = " & lngPosID & ",tscDepID = " &
lngDepId & ",tscTypID = " & lngTypID & ",tscStartTime =#" & Me.txtStartTime &
_
"#, tscEndTime = #" & Me.txtEndTime & _
"#, tscCallTime = #" & Me.txtCallTime & _
"#, tscOutTime = #" & Me.txtOutTime & "#"

If Len(varNotes & "") > 0 Then
strSQL = strSQL & ", tscNotes = " & IIf(IsNull(varNotes), Null,
"""" & varNotes & """")
End If
db.Execute strSQL, dbFailOnError
End If


Sorry for giving the whole thing, but I don't have a clue where the problem
is. Any help with this would be greatly appreciated.

 

[John W. Vinson]

Syntax error (missing operator) in query expression 'Joshua McLemore'.

The 'Joshua McLemore' is the field lngEmpID I'm trying to insert below.

The fieldname lngEmpID very strongly suggests that it is a Long Integer
numeric EmployeeID.

The text string 'Joshua McLemore' is not a valid long integer!

My guess is that you're another victim of the infamous Lookup Wizard, which
conceals the actual contents of the table (a numeric ID field) behind a looked
up text string. Is that plausible?

 

[Joshua]

This is plausable indeed!

I had originally had it inserting the employee id number which was a long
integer, but changed this to show the person's name instead of id. I didn't
change the field names out of a hope that the code would still work with a
marginal amount of tweeking.

Any way to easily fix this? Other than doing a lookup combo with employee
name only and no other fields?

Thanks for your help once again, John.

 

[John W. Vinson]

This is plausable indeed!

I had originally had it inserting the employee id number which was a long
integer, but changed this to show the person's name instead of id. I didn't
change the field names out of a hope that the code would still work with a
marginal amount of tweeking.

Any way to easily fix this? Other than doing a lookup combo with employee
name only and no other fields?

Eh?

The USER needs to see the employee name.
The COMPUTER needs to see the ID.

Neither needs to know what the other is looking at.

you'ld use a combo box with both fields, the ID as the bound column, the name
as the first (or only) visible field. The user selects and sees the name; the
ID (the combo's value) gets stored in the table.

 

[Joshua]

I'm sorry, John. I didn't explain myself before.

I need it to show the employee name and store the employee name. I'm adding
a "autofill last" button that will autofill the form with the last entry made
and so if I do that with it storing the employee number, when it autofills
the name it won't work correctly.

But I think I have thought of a way around this. Thank you for all your
help. You MVPs are truly invaluable.

Joshua

 

[John W. Vinson]

I need it to show the employee name and store the employee name. I'm adding
a "autofill last" button that will autofill the form with the last entry made
and so if I do that with it storing the employee number, when it autofills
the name it won't work correctly.

I'm not clear what table you're autofilling the name into!

The employee name should exist *ONLY* in the employee table. You're not
storing it redundantly in a different table, I hope?

 

[Joshua]

I am! But I have good reason, John! I promise!!

No, I understand what you are saying, but we are going to be editing our
schedules using a table filter and so it's hard for the end-user to know who
employee number 4 is instead of just seeing their name. So the query is
pulling the data from the various "source" tables, if you will, and then
dumping them into one "schedule" table.

So that's why I am running into the problems I am. But I solved this by
doing a dlookup on the combo that is selecting the name only and the dlookup
is invisible but providing the query with the number to insert. So, it works.

Thanks again good sir.

Joshua

 

[John W. Vinson]

I am! But I have good reason, John! I promise!!

No, I understand what you are saying, but we are going to be editing our
schedules using a table filter and so it's hard for the end-user to know who
employee number 4 is instead of just seeing their name. So the query is
pulling the data from the various "source" tables, if you will, and then
dumping them into one "schedule" table.

So that's why I am running into the problems I am. But I solved this by
doing a dlookup on the combo that is selecting the name only and the dlookup
is invisible but providing the query with the number to insert. So, it works.

I'm sorry, but I'm going to have to disagree with you here.

You SHOULD NOT be putting the employee name into the schedule table. You're
using a relational database - use it relationally!

The user should never even *see* the schedule table. They should see a Form
based on the schedule table; on that form you can have a combo box *storing*
the employee ID but *displaying* the employee name. The same applies to the
other fields; the schedule table should contain only links to the tables
containing the data.