Access Visual Basic Query Refresh

Question

I have a database that I use to monitor and manage my glucose levels.

I need to modify the data in several fields to eliminate duplicate data and simplify data entry.

I am using several different levels of queries to search existing records using query criteria selected on a form “Search0f”. I would like to use visual basic to close or update a specific query when a combo box selection is changed.

For example, the “Select MealID” on click property opens a specific query. If I want to select a different MealID I must close the open query manually because the open query will not update for the new selection.

I would like to know to correct visual basic syntax to either close the open query before “click” using the combo box’s got focus property or to refresh the records in the open query with the new selection.

Answer 1

Hi Ken

I made datasheet forms with the following visual basic code

Private Sub Form_Open(Cancel As Integer)

      DoCmd.OpenForm "MyFormName", acFormDS

      Forms![ MyFormName].Requery

End Sub

Works great!

Thanks for the help

Answer 2

I take it you are opening the query as a datasheet, and the query references the controls in the form as parameters.  If you were to open a form based on the query, you would then be able to simply requery the form after opening it:

    DoCmd.OpenForm "YourFormName"

    Forms("YourFormName").Requery

If the form is not currently open it will open and return the correct records.  If it is currently open its recordset will be reloaded to show the new set of records. Generally speaking you should not interface with data through a table or query's datasheet, but only through forms or reports. A form can be in datasheet view of course.

Answer 3

It would help to show the code you are using now.

But something like

DoCmd.Close acQuery, "queryname"

DoCmd.OpenQuery "queryname"

Should do it for you.