Hi, Kelly.
I'm trying to have an open query automatically update after new records are
added to the table.
Queries are not capable of this. Query objects are static in that they can
only display the data set as it was when it was retrieved from the database.
However, a form object is capable of displaying a datasheet grid that looks
like a query object, but can requery the database as needed, format the
displayed data, handle events, automate logical steps, et cetera.
To do so, create a new form and set its Record Source Property to the name
of the query you created. Set the form's Default View to "Datasheet" and
copy and paste the following code in the form's module:
' * * * * Start Code * * * *
Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrHandler
Me.TimerInterval = 30000
Exit Sub
ErrHandler:
MsgBox "Erro in Form_Open( ) in" & vbCrLf & Me.Name & _
" form." & vbCrLf & vbCrLf & _
"Error #:" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub
Private Sub Form_Timer()
On Error GoTo ErrHandler
Me.Requery
Exit Sub
ErrHandler:
MsgBox "Erro in Form_Timer( ) in" & vbCrLf & Me.Name & _
" form." & vbCrLf & vbCrLf & _
"Error #:" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub
' * * * * End Code * * * *
Make sure that the form's On Timer Property is set to [Event Procedure] and
the form's Timer Property is set to 30000. Set the form's Caption property
to something descriptive (or cleverly disguise the fact that it's a form by
using your query's title bar caption). Save the form, compile the code and
open the form in Form View. The form will requery the database every 30
seconds and show any new records, remove any deleted records, and update
existing records. You may set the timer to any other time you'd like, but
remember that the time is defined in milliseconds.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)
- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.
Kelly said:
I'm trying to have an open query automatically update after new records are
added to the table.
I created the query for a specific user. I then have the user open the query
on their pc. When I add new records to the table from another pc, the open
query does not automatically refresh to show the new information without
doing a actual refresh on the query.
I need to find a way to have this open query update automatically as soon as
new information is added to the table to reflect the changes.
Help...
Access 2003
