Compile error running update statement (1 Viewer)

[nortonm]

Hi - I think the answer to this will mean much leaner databases for me in future.... In a nutshell, I have a form that looks at an attendance table, and all data can be amended on the form if needs be except the User, as it has a foreign key in the attendance table, but I need to reselect by the full name from a combo. So I created a button that opens a small form, has the combo look at the user table retrieving the UserID field and the FullName field. Then I wanted to run an Update statement from a cmd button on that little form that says:

Private Sub cmdUpdate2_Click()

Update tblEventAttdSU
Set tblEventAttdSU.ServiceUser = [Forms]![frmUpdateSUAttd]![txtSU_ID]
WHERE [Forms]![frmUpdateSUAttd]![txtEvtAttSU_ID] = [tblEventAttdSU].[EvtAttSU_ID]

End Sub

• Table = tblEventAttdSU (record of user service user attendance)
• UserID = ServiceUser (Number - which I want to put into the attendance table from the little form where the event id in the table matches the event id in the form.)
• txtSU_ID = equivalent ServiceUserID on the update userid form)
• EvtAttSU_ID = the Attendance ID in the Attendance table.
• txtEvtAttSU_ID = the Attendance ID in the attendance form.

...Thereby updating the attendance table in the background, then closing the little form and refreshing the attendance form, so showing the changed service user.

The statement fails with 'Compile error: sub or function not defined'. I think this has happened in the past, and I have just done what I have just done now - ie create a query using the access create query function, which works perfectly and is more or less the same code, but SQL is compiled by the graphic query creator. This means I often end up with loads more query objects in my database than is necessary, I should just be able to run this update with a couple of lines of code from the form. Am I not declaring something, missing some other modifying code?

BTW the code from the query is similar:

UPDATE tblEventAttdSU SET tblEventAttdSU.ServiceUser = [Forms]![frmUpdateSUAttd]![txtSU_ID]
WHERE (([Forms]![frmUpdateSUAttd]![txtEvtAttSU_ID]=[tblEventAttdSU].[EvtAttSU_ID]));

Many Thanks for reading this.

 

[sneuberg]

First the query needs to be converted to a string without new line characters like

"Update tblEventAttdSU Set tblEventAttdSU.ServiceUser = [Forms]![frmUpdateSUAttd]![txtSU_ID] WHERE [Forms]![frmUpdateSUAttd]![txtEvtAttSU_ID] = [tblEventAttdSU].[EvtAttSU_ID]"

Then the references need to be concatenate in. Assuming these are numbers this would be.

"Update tblEventAttdSU Set tblEventAttdSU.ServiceUser = " & [Forms]![frmUpdateSUAttd]![txtSU_ID] & " WHERE [tblEventAttdSU].[EvtAttSU_ID] = " & [Forms]![frmUpdateSUAttd]![txtEvtAttSU_ID]

Note that I rearrange the order of the WHERE clause to simplify this.

Then you use either DoCmd.RunSQL or CurrentDB.Execute to run it. With the later you would have.

CurrentDb.Execute "Update tblEventAttdSU Set tblEventAttdSU.ServiceUser = " & [Forms]![frmUpdateSUAttd]![txtSU_ID] & " WHERE [tblEventAttdSU].[EvtAttSU_ID] = " & [Forms]![frmUpdateSUAttd]![txtEvtAttSU_ID], dbFailOnError

You could shorten this by using the relative reference Me in place of [Forms]![frmUpdateSUAttd]. Note dbFailOnError is added so that the statement raises an error if the query fails for thing like primary key violations.

 

[nortonm]

That's just utterly brilliant of you to help me so quickly, and of course it runs like a Rolex. Many Thanks! I can do some serious tidying up now on my other DBs and any new ones.