Error Using StrString With SQL INSERT INTO

[CharlesWhiteman]

Each time the code is executed Access asks for the value of each field rather than using the StrStrings. In debug the StrStings show the correct values but dont seem to transmit through to the SQL INSERT INTO part - as below

Dim strSQL As String
Dim StrCompanyCode As String
Dim StrCallReference As String
Dim StrCallDate As String
Dim StrDepartment As String
Dim StrPerson As String
Dim StrCallNote As String
Dim StrResult As String


StrCompanyCode = Forms.frmdatabase.CompanyCode
StrCallReference = DMax("[Call Reference]", "TblCalls") + 1
StrCallDate = Now()
StrDepartment = "Sales"
StrPerson = CurrentUser()
StrCallNote = "Relationship Change"
StrResult = "Note"

Works Fine down to Here

strSQL = "INSERT INTO TblCalls (CompanyCode, [Call Reference], [Call Date], Department, Person, [Call note], Result)"
strSQL = strSQL & " VALUES(StrCompanyCode, StrCallReference, StrCallDate, StrDepartment, StrPerson, StrCallNote, StrResult)"


DoCmd.RunSQL strSQL

 

[CharlesWhiteman]

Found the answer as follows: Change the value's:


strSQL = "INSERT INTO TblCalls (CompanyCode, [Call Reference], [Call Date], Department, Person, [Call note], Result)"
strSQL = strSQL & " VALUES(' " & [strcompanycode] & " ', ' " & [strcallreference] & " ', ' " & [strcalldate] & " ', ' " & [strdepartment] & " ', ' " & [strperson] & " ', ' " & [strCallnote] & " ', ' " & [strResult] & " ')"

DoCmd.RunSQL strSQL

 

[CharlesWhiteman]

Can anyone tell me why, after the code executes, in the table, there is an extra space before each record detail?

 

[CharlesWhiteman]

Only where the field type is text does the extra space before the record detail happen

 

[ecawilkinson]

Charles,
you have added the spaces yourself, consider:

strSQL = strSQL & " VALUES(' " & [strcompanycode] & " ', ' " & [strcallreference] & " ', ' " & [strcalldate] & " ', ' " & [strdepartment] & " ', ' " & [strperson] & " ', ' " & [strCallnote] & " ', ' " & [strResult] & " ')"

change it to:

strSQL = strSQL & " VALUES('" & [strcompanycode] & "', '" & [strcallreference] & "', '" & [strcalldate] & "', '" & [strdepartment] & "', '" & [strperson] & "', '" & [strCallnote] & "', '" & [strResult] & "')"

you can see that I have removed the spaces you put in after each opening ' and before each closing '

HTH,
Chris

 

[CharlesWhiteman]

sure, i can see that now. Thanks for the spotting and advice.