CODEWITHSUNDEEP
ms-accessselectrefreshms-access-2013
Sylmara
Sylmara

Reputation: 27

How to force an Access query datasheet to refresh its data

I am new on access and what I am trying to do is a select with a criteria so I created a query with the wizard and seted the criteria with a text from a form ([Forms]![Form1]![Transacao]) and created a button to run the query at the first time works great but when I type something else and click the button the datas do not refresh. What I have to do to refresh? I've tryed to add refresh on the event click of the button and did not work.

Thanks in advance for your help.

Upvotes: 1

Views: 26168

Answers (3)

tomasm
tomasm

Reputation: 1

If DoCmd.Requery isn't working, another option is to close the query then re-open it. The code below will run without error whether the query is already open or already closed.

DoCmd.Close acQuery, "QueryName", acSaveNo

DoCmd.OpenQuery "QueryName", acViewNormal, acReadOnly

Upvotes: 0

sam hooper
sam hooper

Reputation: 91

I know this question is a bit stale at this point, but since I couldn't find a suitable answer to this question and the above answer didn't work for me (and still hasn't been accepted), I thought I'd offer my solution for those few poor saps still stuck developing applications in Access. My use case was slightly different (changing the underlying SQL of a query, then opening/refreshing it), but the same principle could be applied. The gist is to first check to see if the query is open and close it if it is. Then open it up again.

To do this, paste this code into a VBA module:

Public Function open_or_refresh_query(query_name As String, Optional sql_str As String = "")
' Refresh or open an existing query
' query_name: Name of the query
' sql_str: optional new SQL string if changing the underlying SQL. If not given,
'   the query will open with its existing SQL string

    On Error GoTo err_handler

    Dim qdf As QueryDef
    ' Loop through each query in the DB and find the one of interest by name
    For Each qdf In CurrentDb.QueryDefs
        If qdf.Name = query_name Then
            ' If the query is open, close it
            If SysCmd(acSysCmdGetObjectState, acQuery, query_name) <> 0 Then
                DoCmd.Close acQuery, query_name, acSaveNo
            End If
            Exit For
        End If
    Next qdf

    Set qdf = CurrentDb.QueryDefs(query_name)

    ' Reset the SQL if new SQL string was given
    If Len(sql_str) > 0 Then qdf.sql = sql_str

    ' Close the QueryDef object to release resources
    qdf.Close

    ' Open the query in default datasheet view
    DoCmd.OpenQuery query_name

exit_function:
    Exit Function

err_handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Error"
    Resume exit_function

End Function

At this point you could call this function from any other VBA code in your project. To open/refresh a query from a macro as the OP wanted to do, create a new macro and add a RunCode action with open_or_refresh_query("my_query") in the Function Name field, changing my_query to the name of your query.

Upvotes: 2

C Perkins
C Perkins

Reputation: 3884

In Access, a query is usually opened in a default Datasheet view. This default datasheet is contained in a window (or tab) that is only accessible using Macros or DoCmd in VBA.

Once a query window is open, its data will not necessarily update automatically when new records are added to the underlying table(s). The datasheet needs to be "requeried". (Incidentally, the term "refresh" is usually reserved to mean "redrawing" a window on the screen and has nothing to do with the data. This is especially the case in programming and development environments which deal with data and drawing/painting windows and controls on the screen.)

Here is one way to force a query to update its data (when open in its default datasheet view):

DoCmd.OpenQuery "QueryName"
DoCmd.Requery

Calling OpenQuery should also activate the query window if it is already open. If you find that the windows does not activate, you can also call DoCmd.SelectObject acQuery, "QueryName" before DoCmd.Requery.

The DoCmd methods correspond to Macro actions, so if the query is activated by a Macro, just add the Requery action to the macro after the OpenQuery or SelectObject actions. Leave the Control Name parameter of the Requery action blank to force the entire query to updated.

Upvotes: 6

Related Questions