Button on Form needs VB to fill in something in another Table

[kableland]

I've got a form the has a button that when clicked should send an email (this part works fine) and then on exit, matches a key field and finds it in a table and then makes a yes/no field "yes". I need help figuring out the VB code to make the "yes" part happen, please and thank you.

Ps. this field I want checked in the parent table is here on my form but since the form runs on a query (table/table/query), I can't update anything even when I change the properties to Dynaset (Inconsistent Updates)

Form Name: frmHeaderInfo-Edit
Table Name: tbl1HeaderInfo
Table Field Name shared with Form = PRNumTemp
Table Field Name needing changed to "Yes" = SubmitYesNo

[meratigoerr]

One simple method is to create an update query based on the table that holds the data you want to change. You need two fields. The key will be the first. use criteria that references the key on your form. 2nd field will be your checkbox. update to yes. just add the command to run the query to your vb code and you'll update your yes/no field. Turn the warnings off so that the user is not prompted to up update it everytime they push the button. Very simple vb code

Docmd.setwarnings false
docmd.openquery "UpdateSubmitYesNo"
Docmd.Setwarnings true

[kableland]

Thank you for your time. I created the query as you suggested, but I named it "14qryUpdateSubmitYesNo". I inserted your code (with the correct query name) and it didn't do what I had hoped. I want the record I'm looking at on the form to updated on the query (this DB is for several users, so I was hoping it could be updated behind the scenes), so I need the code to find that specific record and update the "SubmitYesNo" field to "Yes/True"?

[meratigoerr]

it didn't do what I had hoped.

Do you get an error? Ifit is doing something, what is it doing? Can you post the sql for the query along with the code you're using to call it?

[June7]

Alternative to saved Access query, VBA code executes SQL action (SetWarnings not needed):

CurrentDb.Execute "UPDATE tablename Set fieldname=True WHERE ID=" & Me.ID

Otherwise, post the sql statement of your saved query object and code (macro or VBA?).

[kableland]

Your code ran without error, it just took me to the new query you asked me to create, nothing else. I wanted the SubmitYesNo checkbox of the record on the form to be checked as Yes/True in the query, and I was hoping for the code to do this without the user's involvement/behind the scenes. If this is a little confusing, I would say read my initial post one more time and it should make sense.

[kableland]

June 7,

Here is the code from what I interpreted from your response, which I'm sure needs tweaking because I'm getting "Expected: end of statement" and the word "Set" is highlighted.

CurrentDb.Execute "tbl1HeaderInfo" Set me.SubmitYesNo= True Where "PRNumTemp = '" & Me.PRNumTemp & "'"

[June7]

Use my example syntax and replace the generic table and field references in my example with yours. Do not change the quote marks positions.

CurrentDb.Execute "UPDATE tbl1HeaderInfo SET SubmitYesNo=True WHERE PRNumTemp = '" & Me.PRNumTemp & "'"

[meratigoerr]

Your code ran without error, it just took me to the new query you asked me to create, nothing else. I wanted the SubmitYesNo checkbox of the record on the form to be checked as Yes/True in the query, and I was hoping for the code to do this without the user's involvement/behind the scenes. If this is a little confusing, I would say read my initial post one more time and it should make sense.

The query should be an update query, not a select query. An update query would not show you any results, just updated the fields as intended, without input from the user.

Code should look like;

UPDATE tbl1HeaderInfo SET tbl1HeaderInfo.SubmitYesNo = True
WHERE (((tbl1HeaderInfo.PRNumTemp)=[Forms]![frmHeaderInfo-Edit]![PRNumTemp]));

note the 'UPDATE' at the begining and not 'Select'

this is pretty much what June7 suggested as well, except this is a saved query, while his was in VBA.

my other two cents:
Avoid using '-' in form, table, or field names. one problem specifically is when you copy and past it in access it wants to change '[frmHeaderInfo-Edit]' to '[frmHeaderInfo]-[Edit]'