Stumped On INSERT INTO Runtime/Syntax Error?

[CharlesWhiteman]

I am a bit stumped as to why I get a runtime 3134 error on this piece of code.

They are all text values

Dim strUserName As String
strUserName = Forms!FrmPrimaryData.FrmPrimaryDataInstallsSubFrm.Form.txtMacAddress
Dim strIDValue As String
strIDValue = Nz(DMax("[ID]", "radreply"), 0) + 1
Dim strAttribute As String
strAttribute = "Monthly-Transfer-Prepaid"
Dim strOP As String
strOP = ":="
Dim strValue As String
strValue = "100000000"
Dim strSQLAddGig As String
strSQLAddGig = "INSERT INTO radreply (ID, UserName, Attribute, op, Value)"
strSQLAddGig = strSQLAddGig & " VALUES('" & [strIDValue] & "', '" & [strUserName] & "','" & [strAttribute] & "', '" & [strOP] & "', '" & [strValue] & "')"
DoCmd.RunSQL strSQLAddGig

 

[pr2-eugin]

Okay, lets take one at a time..

1. Is ID a Text type or Number.. If number remove the Single quotes..
2. If ID is Number - If it is AutoNumber type do not add that to be inserted, as it will be generated automatically..
3. Is Value (which is a very bad Field name) again is it a Text type or Number.. If number remove the Single quotes
4. Remove all Square brackets from the variables in the INSERT Statement..
5. When referring to SubForm control, it should be.. Forms!FrmPrimaryData.FrmPrimaryDataInstallsSubFrm.Form!txtMacAddress

 

[CharlesWhiteman]

Hi Paul,

I should explain that the table referenced is a linked table and is a MySQL Db which as you have rightly observed is very badly designed. However, I don't have any capability to modify it.

ID is a text field - even though it contains a numeric value
Value is a text field - even though it contains a numeric value

I removed the square brackets and attempted again but same issue.

The reference to the sub form I tested and the correct value is returned

 

[CharlesWhiteman]

Oh yeah, ID is a PK but in the MySQL Db is not a integer type field or auto numbered!

 

[pr2-eugin]

At which point is the error occurring exactly and what is the error description? If ID is a Text field does the Arithmetic operation of DMax+1 work properly? Or DMax in that regards? Have you tried Debugging your error?

 

[CharlesWhiteman]

It was the column called Value which was causing the error. I have solved by not including the value in the INSERT and updating it afterwards.

Value must be a reserved word

Dim strUserName As String
strUserName = Forms!FrmPrimaryData.FrmPrimaryDataInstallsSubFrm.Form!txtMacAddress
Dim strIDValue As String
strIDValue = Nz(DMax("[ID]", "radreply"), 0) + 1
Dim strAttribute As String
strAttribute = "Monthly-Transfer-Prepaid"
Dim strOP As String
strOP = ":="
Dim strValue As String
strValue = "100000000"
Dim strSQLAddGig As String
strSQLAddGig = "INSERT INTO radreply (ID, UserName, Attribute, op)"
strSQLAddGig = strSQLAddGig & " VALUES ('" & strIDValue & "', '" & strUserName & "','" & strAttribute & "', '" & strOP & "')"
DoCmd.RunSQL strSQLAddGig
DoCmd.RunSQL "UPDATE radreply SET [VALUE]='" & strValue & "' WHERE [ID]=" & strIDValue & ";"

 

[pr2-eugin]

Value must be a reserved word

It is.. The Link I provided in Post#2, has a comprehensive list of all reserved words in Access..

Glad you have it sorted.. Good Luck..