form closing changes field in all records in table

[robsworld78]

Is this possible?

I have a table with product in it and added a yes/no field. On a form I have a combo box that points to that table for all the product. I've made it so when I select an item in the combo box that field will turn to yes and then the next record it won't show up there because "yes" is filtered out.

Here's the problem I need something that will turn all those records in that table back to "no" when the form is closed so they reappear when the form is opened again.

Can all records in a table have a change applied to them all when the form closes?

 

[Galaxiom]

Just run an update query on the table in the OnClose event procedure.

Use DoCmd.RunSQL or Currentdb.Execute to run the query.

 

[robsworld78]

Thanks! I'll give that a try

 

[robsworld78]

That's awesome it works, thank-you! But, always a but hey. When I close the form it pops a stupid window asking me if I want to do it, I don't need that screen, I always want it to do it. Can it be told so it doesn't need to ask?

 

[robsworld78]

I found a setting in the options that turns off prompting for update queries, I'm all set. Thanks for your help!

 

[Galaxiom]

Turning off the prompting option means it is off for all queries. It is better to turn it off and on as required with VBA.

DoCmd.SetWarnings False
DoCmd.SetWarnings True

When using this it is important to reenable the warnings at the Exit of the procedure to ensure it is turned back on even if there is an error. If you don't do this it remains off and stays off. No warnings for deleting objects too.

The CurrentDB.Execute method does not issue a warning and is often a better choice. However it does not indicate that the update failed either unless you include the second argument, dbFailOnError, so that should always be included.

The advantage of the RunSQL method is its awareness of references to the form while Execute only understands the tables and queries. This is because the RunSQL is processed by Access before sending to the database engine while Execute is sent direct.

 

[robsworld78]

Thanks, I read on a post to change that setting it didn't mention it would do that for everything, code much better and works.

Could you take a peak at my other thread, no one seems to know.

http://www.access-programmers.co.uk/forums/showthread.php?t=212838