update query on click not working

[HimAgain]

I am having trouble running a query with vba. I can click the query on the left had side and it works perfectly. But when i try to run the query using a button on a form it does not work and gives the error "Object variable or With block variable not set".

more detail of the form
What i have is a multiple items form that loads of a date range query. On that form users place a check mark next to each record they want the query to affect and then type in an unbound text box what they want update the records with. Again, i can open the form, make my selections, type in my change into the unbound box, and run the query by clicking on it on the left just fine. I just cant seem to get the button to work.


Below is the SQL view of the query i am attempting to run and below that is the vba code i am using to execute the query.

Can anyone please tell me what i am doing wrong and how to fix it?

UPDATE [C&D data] SET [C&D data].[Rel By] = [Forms]![CD_relinquish_qry_frm]![relby]
WHERE ((([C&D data].update)=True));

Private Sub updatetbl_btn_Click()
Dim dbs As DAO.Database, strupdate As String

strupdate = "UPDATE [C&D data] SET [C&D data].[Rel By] = [Forms]![CD_relinquish_qry_frm]![relby] " & vbCrLf & _
"WHERE ((([C&D data].update)=True));"

dbs.Execute strupdate

End Sub

 

[CJ_London]

strupdate = "UPDATE [C&D data] SET [C&D data].[Rel By] = [Forms]![CD_relinquish_qry_frm]![relby] " & vbCrLf & _
"WHERE ((([C&D data].update)=True));"

what you are trying to do with this code is update the Relby field with a value '[Forms..." when what you actually want is the value in that field

strupdate = "UPDATE [C&D data] SET [C&D data].[Rel By] = [Forms]![CD_relinquish_qry_frm]![relby] " & vbCrLf & _
"WHERE ((([C&D data].update)=True));"

you also don't need the vbcrlf and using characters in a field or table name such as & will cause you grief down the line with inexplicable errors - field and table names should only consist of letters and numbers, no spaces or other characters expect a _ if you really must


assuming the value is a number, try

strupdate = "UPDATE [C&D data] SET [C&D data].[Rel By] =" &  [Forms]![CD_relinquish_qry_frm]![relby] & " WHERE ((([C&D data].update)=True));"

if it is text

strupdate = "UPDATE [C&D data] SET [C&D data].[Rel By] ='" &  [Forms]![CD_relinquish_qry_frm]![relby] & "' WHERE ((([C&D data].update)=True));"

 

[HimAgain]

Thanks for the reply. Yeah i figured out using symbols in table names and such was a bad idea and plan on changing it soon.

I tried your code out but it still gives me the same error "Object variable or With block variable not set".
oh and it is text. After i get it working i'm going to add another text field to edit and a date field. figured i would take it one step at a time though.

 

[CJ_London]

which version?

 

[JHB]

You need to set the dbs variable, (you've declared it but not set it to anything).

Set dbs = CurrentDb

 

[HimAgain]

You need to set the dbs variable, (you've declared it but not set it to anything).

Set dbs = CurrentDb

That fixed it. Thank you everyone that helped.

 

[MarkK]

If you have a saved query that already functions correctly, you don't have to re-construct that SQL in VBA, you can just run your saved query. Consider code like...

Private Sub updatetbl_btn_Click()
    CurrentDb.QueryDefs("YourSavedQueryName").Execute
End Sub

hth
Mark

 

[HimAgain]

If you have a saved query that already functions correctly, you don't have to re-construct that SQL in VBA, you can just run your saved query. Consider code like...

Private Sub updatetbl_btn_Click()
    CurrentDb.QueryDefs("YourSavedQueryName").Execute
End Sub

hth
Mark

That's what i was going to end up doing if i couldn't get this to work, but this was just one of those things i was banging my head against the wall to figure out.