SQL UPDATE String Issue

[CharlesWhiteman]

I keep getting "Data Type Mismatch" Error on this code. If I reference the txtbox directly Access always prompts me for the value. If I reference the value as a string I get the datatype mismatch error. Apart form that it works! Been trying to solve for a few hours so any advice greatfully received. Heres the code:

'Update Address Details To Primary Data
Dim varPK As String
varPK = Me.txtPrimaryDataID
Dim strSQLUpdate As String
Dim varAddress1 As String
varAddress1 = Me.txtAddress1
Dim varAddress2 As String
varAddress2 = Me.txtAddress2
Dim VarCity As String
VarCity = Me.txtCity
Dim varCounty As String
varCounty = Me.txtCounty
Dim varPostCode As String
varPostCode = Me.txtPostCode
strSQLUpdate = "UPDATE tblPrimaryData SET Address1 = '" & [varAddress1] & "', Address2 = '" & [varAddress2] & "',City = '" & [VarCity] & "', County = '" & [varCounty] & "', PostCode = '" & [varPostCode] & "' WHERE PrimaryDataID = '" & [varPK] & "';"
DoCmd.RunSQL strSQLUpdate

 

[georgedwilkinson]

My assumption is that you are getting the error when you're building the string. You should not put a VB variable (varPK) name is square brackets.

Also, if the data type of PrimaryDataID is a number, you need to remove the single quotes (apostrophes) from around varPK.