update query on click not working (1 Viewer)

HimAgain · Feb 10, 2018

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?

Code:

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

Code:

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 · Feb 10, 2018

Code:

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

Code:

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

Code:

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

HimAgain · Feb 10, 2018

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 · Feb 10, 2018

which version?

Pat Hartman · Feb 11, 2018

Put a stop on the execute statement and print out the string. If you do't see the error, copy the string to the query window and run it from there. Usually you'll get better error messages.

JHB · Feb 11, 2018

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

Code:

Set dbs = CurrentDb

HimAgain · Feb 14, 2018

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

Code:

Set dbs = CurrentDb

That fixed it. Thank you everyone that helped.

MarkK · Feb 14, 2018

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...

Code:

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

hth
Mark

HimAgain · Feb 14, 2018

MarkK said:
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...

Code:

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.

update query on click not working (1 Viewer)

HimAgain

Registered User.

CJ_London

Super Moderator

HimAgain

Registered User.

CJ_London

Super Moderator

Pat Hartman

Super Moderator

JHB

Have been here a while

HimAgain

Registered User.

MarkK

bit cruncher

HimAgain

Registered User.

Similar threads

Users who are viewing this thread