create and run a update query totally in code

[kimi2001]

Hi,

I am trying to create and run a update query totally in code in ACCESS 2000 but despite reading postings in here along the same lines I dont seem to be able to get it to work - maybe I am missing something.

This is the code I am using and the SQL:

------------Code start------------------
Private Sub LibraryNo_AfterUpdate()
Dim strsql As String
DoCmd.SetWarnings False

strsql = (UPDATE tblRecordings SET tblRecordings.MediaFormatID = 3
WHERE (((tblRecordings.MediaFormatID) Is Null) AND ((tblRecordings.LibraryNo) Like "CD-S *&quot);

DoCmd.RunSQL (strsql)

DoCmd.SetWarnings True
End Sub
----------------code end----------------------

Now if I just create a normal update query, it all works fine but its just when I do it as pure code that it wont.

Access turns the following into red text in the VBA Debug window:
strsql = (UPDATE tblRecordings SET tblRecordings.MediaFormatID = 3
WHERE (((tblRecordings.MediaFormatID) Is Null) AND ((tblRecordings.LibraryNo) Like "CD-S *&quot);

Any guidence, suggestions or help would be great.

Thank you,

Kimi

 

[JimHorton]

Kim,
You need to enclose the strsql in double quotes, and any embedded double-quotes must be converted to double-double-quotes or single-quotes:
strsql = "(UPDATE tblRecordings SET tblRecordings.MediaFormatID = 3
WHERE (((tblRecordings.MediaFormatID) Is Null) AND ((tblRecordings.LibraryNo) Like ""CD-S *"&quot);"

--Jim

 

[Event2020]

Hi Jim,

Many thanks for the reply, I really appreciate it.

I tried your suggestion and Access 2000 is giving the following error:
Runtime error '3319':
Syntax error in union query.

I dont understand this as all I did to get the SQL was create a normal update query (that works fine) and then used the SQL statement from that.

Any further suggestions ?

Kimi.

 

[JimHorton]

Kim,
The statement works for me...check your parenthesis, or use below:
strsql = "UPDATE tblRecordings SET tblRecordings.MediaFormatID = 3 WHERE tblRecordings.MediaFormatID Is Null AND tblRecordings.LibraryNo Like ""CD-S *"""

If the string is on more than one line, you need to use line-continuation character, the "_" (not shown above--the above statement should be on one line if you paste it into Access--but this formum's formatting may put a string of spaces--which you can remove if needed).
Access has an annoying habit of over-parenthesizing, and that causes trouble. The above statement uses no parenthesis and should work for you. (Assuming mediaformatID is numeric...)
--Jim

 

[kimi2001]

Jim,

You are a complete star, I checked my code after your last posting and it now works great, Thank you.

I do not know if you or anyone else reading this can help me one stage further as I now need to add some conditions.
The following code does not work and I have tried several other ways that my limited Access Knowledge allows but I hope it gives you a idea of what I am trying to do.

--------------code start--------------------
Private Sub Form_AfterInsert()
Dim strsql As String
DoCmd.SetWarnings False

If tblRecordings.MediaFormatID Is Null AND tblRecordings.LibraryNo Like "CD-S *" Then
strsql = "UPDATE tblRecordings SET tblRecordings.MediaFormatID = 3

ElseIf tblRecordings.MediaFormatID Is Null AND tblRecordings.LibraryNo Like "CD-A *" Then
strsql = "UPDATE tblRecordings SET tblRecordings.MediaFormatID = 2

ElseIf tblRecordings.MediaFormatID Is Null AND tblRecordings.LibraryNo Like "CD-C *" Then
strsql = "UPDATE tblRecordings SET tblRecordings.MediaFormatID = 1

DoCmd.RunSQL (strsql)
DoCmd.SetWarnings True

End If
End Sub
----------------------code end-------------------

Thank you again for your kind help so far, I really appreciate it.

Regards,

kimi

 

[JimHorton]

kimi,
What are you trying to do here in the AfterInsert? I'm guessing that you just want to update one field in one record, the newly inserted one (as opposed to updating all that satisfy the libraryNo). If this is the case (update the current record's mediaformaid field), then you could use an AfterUpdate event in the LibraryNo, setting the texbox value of the mediaformatID accordingly.

If that's not the case, then your above sql's need a Where Clause and closing quotes, plus you're referring to the table, and not the form, so replace tblRecordings.MediaFormatID with Me.mediaformatID.
--Jim