Update a Table fields with Form/vba

Question

I am trying to update a table (tbl_Out_WeightDim), please find below my code and error message also.

Private Sub Command140_Click()

strSQL = "UPDATE tbl_Out_WeightDim " & _

" SET serviceflow=" & Me.ServiceFlow & ", Note1='" & Me.Note1 & "'" & _

", Note2='" & Me.Note2 & "', Note3='" & Me.Note3 & "'" & _

" WHERE Codelist1=" & .Codelist & ";"

Debug.Print strSQL

CurrentDb.Execute strSQL

End Sub

Answer 1

I see several problems here.

1. As Karl spotted, the .Codelist is an unqualified reference. And would probably cause the error. As Daniel said, the Debug.Print should place the generated SQL statement into the Immediate Window. Copy and paste from there into SQL View and try to view the results, it should highlight the error.
2. But why are you doing it this way? Using a SQL statement to update a table indicates using an unbound form. While there are valid reasons for using an unbound form, I don't see any of those reasons reflected in your code. One of the great advantages of Access is that it handles the I/O between your forms and tables.
3. Whenever you have fields with names like Note1, Note2 etc. you have a repeating group which violates Normalization rules. This may indicate other problems with your table designs.
4. Accepting the default name for controls is not a good idea.

More info about what the app does and why you are doing it this way may help us help you with your app.

Answer 2

Hi,

> .Codelist

Are you sure that the problem isn't this unqualified looking reference?

Servus
Karl
****************
Access Forever News DevCon
Access-Entwickler-Konferenz AEK

Answer 3

Check the name of your control

What's the output of the SQL statement from you Debug.Print?

Since you have a Debug.Print of your SQL statement, have you tried copying it and creating a query of it in SQL View and then switching back into design mode to see what gets returned as the problem.