Enter Parameter Value when it's already declared...?

[BonnieG]

I'm trying to update a SQL table using a form button, and getting an "Enter Parameter Value" textbox prompt when the value is already declared. Not sure what is wrong. I know the value is valid as I'm returning it in a MsgBox.

Here is my code.

Private Sub cmd_data_merge_Click()
finish = [finish].Value
conv = DDEInitiate("EXCEL", "123 Staff List.xls")
For x = 4 To finish
cell_employee_number = "R" & x & "C9"
cell_surname = "R" & x & "C8"
cell_forename = "R" & x & "C7"
cell_job_role = "R" & x & "C11"
cell_department = "R" & x & "C4"
cell_email = "R" & x & "C17"
 
new_employee_number = CStr(DDERequest(conv, cell_employee_number))
new_surname = CStr(DDERequest(conv, cell_surname))
new_forename = CStr(DDERequest(conv, cell_forename))
new_job_role = CStr(DDERequest(conv, cell_job_role))
new_department = CStr(DDERequest(conv, cell_department))
new_email = CStr(DDERequest(conv, cell_email))
 
trimmed_employee_number = Trim(Replace(new_employee_number, vbCrLf, ""))
trimmed_surname = UCase(Trim(Replace(new_surname, vbCrLf, "")))
trimmed_forename = UCase(Trim(Replace(new_forename, vbCrLf, "")))
trimmed_job_role = Trim(Replace(new_job_role, vbCrLf, ""))
trimmed_department = Mid(Trim(Replace(new_department, vbCrLf, "")), 12)
trimmed_email = LCase(Trim(Replace(new_email, vbCrLf, "")))
trimmed_organisation = "Hull and East Yorkshire Hospitals NHS Trust"
trimmed_manager_verification_timestamp = Date
 
If trimmed_department Like "*Widows/Widower*" Then
 
Else
 
    MsgBox trimmed_employee_number
 
    SQL1 = "UPDATE dbo_users SET dbo_users.forename = trimmed_forename WHERE (((dbo_users.employee_number)=trimmed_employee_number));"
    SQL2 = "UPDATE dbo_users SET dbo_users.surname = trimmed_surname WHERE (((dbo_users.employee_number)=trimmed_employee_number));"
 
    DoCmd.RunSQL SQL1
    DoCmd.RunSQL SQL2
 
End If
Next x
end_script:
DDETerminate conv
End Sub

Feel like I'm probably missing something really obvious. Any ideas?

 

[pr2-eugin]

Concatenate the Variables do not use them just inside the statements. like.

someSQL = "INSERT INTO someTable (someField) VALUES (" & variableName & ")"

Not,

someSQL = "INSERT INTO someTable (someField) VALUES (variableName)"

 

[BonnieG]

Thanks Paul, that's worked. Much appreciated.