INSERT function failing

[beanlucy]

I've been left with a database designed by someone else and an import feature where data is imported from excel then checked for duplicates before being inserted into outtblPeople, is not working. I get the following error when the insert is trying to take place.

The name "Sname" not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

I'm not very familiar with VBA and struggling to find the solution. The data type for Sname in both ljb02.evttblImportTemp abnd outtblPeople is nvarchar.

The code coming up with the error is below. Can anyone help me?



Set rstImportThese = GetRecordsetFromSQL("SELECT * FROM ljb02.evttblImportTemp ", adOpenStatic, adLockReadOnly)
'Loop through the records one by one
Dim intLastID As Integer

While Not rstImportThese.EOF
Select Case Nz(rstImportThese![ImportInstruction], 0)
Case 0
'OK to INSERT to outtblPeople and evttblPupils
'INSERT into outtblPeople and put the last ID inserted into
'evttblPupils

Set rstUpdate = GetRecordsetFromSQL("INSERT INTO outtblPeople (perType, perStatus, perSurname, perFirstName, perTitle, perGender, perDateAdded, perSource, perBatchNum, perOldID) " & _
"VALUES (" & perTypePupil & ", " & outStatusActive & "," & _
Nz(rstImportThese![Sname], " ") & "," & _
Nz(rstImportThese![Fname], " ") & "," & _
Nz(rstImportThese![TITLE], " ") & "," & _
Nz(rstImportThese!GenderID, 180) & _
", GETDATE()," & _
outSchemeEvents & _
"," & intBatchNum & ",rstImportThese![ID]) ", adOpenForwardOnly, adLockOptimistic)

ThrowawayRecordset rstUpdate

intLastID = GetLastID("outtblPeople", "per")
Set rstUpdate = GetRecordsetFromSQL("INSERT INTO evttblPupils (pupPersonID, pupAimHigher, pupWideningParticipation, pupGiftedAndTalented, pupSetNet, pupBatchNum) " & _
"VALUES(" & intLastID & ", " & Nz(rstImportThese![AimHigherBIT], False) & "," & _
Nz(rstImportThese![WPBIT], False) & "," & _
Nz(rstImportThese![GTBIT], False) & "," & _
Nz(rstImportThese![SetNetBIT], False) & "," & _
intBatchNum & ")", adOpenForwardOnly, adLockOptimistic)

ThrowawayRecordset rstUpdate

i_Added = i_Added + 1
Case 1
'Use existing contact
'update evttblPupils, including the BatchNum

Set rstUpdate = GetRecordsetFromSQL("UPDATE evttblPupils " & _
"SET evttblPupils.pupAimHigher = " & Nz(rstImportThese![AimHigherBIT], False) & _
", evttblPupils.pupWideningParticipation = " & Nz(rstImportThese![WPBIT], False) & _
", evttblPupils.pupGiftedAndTalented = " & Nz(rstImportThese![GTBIT], False) & _
", evttblPupils.pupSetNet = " & Nz(rstImportThese![SetNetBIT], False) & _
", evttblPupils.pupBatchNum = " & intBatchNum & _
" WHERE evttblPupils.pupPersonID = " & rstImportThese!perID, adOpenForwardOnly, adLockOptimistic)

ThrowawayRecordset rstUpdate

'Set the batch number and perOldID in outtblPeople (used to do Organisation map thing...)
Set rstUpdate = GetRecordsetFromSQL("UPDATE outtblPeople SET outtblPeople.perBatchNum = " & intBatchNum & _
", outtblPeople.perOldID = " & rstImportThese![Id] & _
" WHERE outtblPeople.perID = " & rstImportThese![perID], adOpenForwardOnly, adLockBatchOptimistic)

ThrowawayRecordset rstUpdate

i_Existing = i_Existing + 1
Case 2
'Do nothing
'This record is not to be imported
End Select
rstImportThese.MoveNext
Wend

 

[MarkK]

On what line does the error occur?

 

[honda882000]

Do you know that this code has worked before? I could be wrong, but I think that whenever you are embedding a string into the SQL code, you still need the apostrophes around it. For example:
"'" & Nz(rstImportThese![Sname], " ") & "'," & _
"'" & Nz(rstImportThese![Fname], " ") & "'," & _
"'" & Nz(rstImportThese![TITLE], " ") & "'," & _

I could be wrong, but I remember getting errors for missing these int the past.

 

[beanlucy]

Thanks - that seems to have got over that part but it is stuck at the next line:

"," & intBatchNum & ",rstImportThese![ID]) ",

I can't work out what needs to go around the rstImportThese string - sorry I'm really not very good at this yet!

 

[honda882000]

It depends on the data type. If it is a number field (integer, double, ..), then you do not need anything around them. If it is a string, you need apostrophe ('). If it is a date field, you will usually need # around it. You can find out what type by checking the table where you are inserting these fields into in design mode.