Need to do a “Refresh” or a “Refresh All” on a select qry after code runs using VBA or macro.

Question

Need to do a “Refresh” or a “Refresh All” on a select qry after code runs using VBA or macro.

I run code that based on what checkboxes on a form is checked. Different records in a table are made available. For later use in a qry based DAO.RecordSet.

If qry was not showing when code runs.  All comes out perfect.

But if qry has run and is showing. And different checkboxes are now picked.

Qry doesn’t show the change in qry’s Datasheet view.  Even though it WAS changed correctly.

Only way I can be sure to have Qry’s Datasheet to SHOW changes is to go to the Ribbon then Home then Records then “Refresh All” or sometimes to “Refresh”. 

Sometimes “Refresh All” doesn’t seem to work but “Refresh” does. I have no idea why.

Haven’t been able to find how to do this in VBA 7.1  win7, 2013 64 bit.

Tried to find how to record a macro in access. I have created a AutoExec maro that uses VBA Functions. No Luck finding how.

RunCode in macro didn’t work so far because couldn’t find what code I needed to have in a function in the macro.

The RunMenuCommand  ONLY SHOWED “Refresh” NOT “Refresh All” and seems I must be able to pick one or other or BOTH.

I did find how to do by using the mouse.

BUT couldn’t get DoCmd.MenuItem or DoCmd.RunCommand  either to work  for “Refresh All” and “Refresh” (the one under “Refresh All”)

Mark J

---

Mark J

Answer 1

Hi Mark. Just thinking out loud... How about closing the query and then reopening it?

Answer 2

Edward,

I wish was so easy for this one.

Problem with that way of dealing with this set of records is, not records returned by just one query.

For this start set of records starts with a query of a table, then based on user input, a subset of action queries (one or more) are run against current set of records, then on records left, a DAO recordSet methods are used to change values in various fields, some fields are used at the end to reduce the set a records even more.

Mark J

---

Mark J

Answer 3

Edward,

Oops. I forgot the last query showed results, as aid to testing results of coding up to this point, and did not change anything.  Even though the query was used next in code.

After I double checked the syntax, I was able to close the query and that made it work.

Mark J

---

Mark J