General discussion

Locked

Display Current Records on Access Form

By Susu ·
How do I refresh the records displayed on a form programatically?
For example, if someone else changes the underlying data while I have the form open, what code do I use to force the form to display the most current data?

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Display Current Records on Access Form

by achp In reply to Display Current Records o ...

Use the form's Requery method, eg.:

Private Sub Refresh_Click
Form.Requery
' or:
SubForm.Form.Requery
End Sub

Collapse -

Display Current Records on Access Form

by Susu In reply to Display Current Records o ...

I have tried every combination of Requery and Refresh that I can think of. The current record returns to the first record, but the values in the fields do not change.

My underlying query is a crosstab; could that make a difference?

Collapse -

Display Current Records on Access Form

by Bob Sellman In reply to Display Current Records o ...

I honestly don't think that there is a good solution for your problem. Every time you open the form or force it to requery, it will get the most current data; however, what you need is for "you" (the application at your computer) to be notified when someone else writes changed or new data.

Forcing a periodic requery can create a lot of network traffic and slow the application down a lot. Here are some ideas you might try:

1. Include a column in each record (of the table that you want to refresh) a date/time stamp that is updated every time someone adds or writes to a record. When the record is highlighted by you, it is read from the network and updated if the date/time stamps don't match.

2. Make a separate table with 2 columns: the name of every table to be refreshed and the last date/time stamp when the table was updated. Periodically (as you feel is appropriate) read that table's data in the backgroup and compare to the current values (keep them in a public array). Update a form using requery or recalc only if the date/time stamps indicates it should be done. The idea of this is that the control table will be very small and create little network traffic and take only a little time, so you only do the "big" requery when necessary.

Collapse -

Display Current Records on Access Form

by Bob Sellman In reply to Display Current Records o ...

I forgot to mention the obvious for method #2:
Whenever data in a table is changed or a record is added the application must also update the date/time stamp for that table's record in the "control" table. That way, when you read the control table it will indicate the last time anyone updated it or added to it.

Also, be sure when using date/time stamps that the date/time is taken from the network server so you are sure everyone is working from the same clock.

Collapse -

Display Current Records on Access Form

by Susu In reply to Display Current Records o ...

Thank you for the info. I'm sure it will be useful to someone, but it is not what I am trying to do at all. I just want the user to be able to click a button and have the data on the form refreshed.

Collapse -

Display Current Records on Access Form

by Bob Sellman In reply to Display Current Records o ...

If you are going to put a button on the form
for the user to click and get the new data,
with a crosstab query as the source you will
need to do the following when the user clicks
the button:

DoCmd.Close 'close the form
DoCmd.OpenForm "yourformname" 'open the form
again

If the form uses any recordsets, the requery
or recalc methods will definitely not work.

Collapse -

Display Current Records on Access Form

by Susu In reply to Display Current Records o ...

Well, I guess thats it then. Although it's not quite what I wanted, I guess it's the only way. Thank you.

Collapse -

Display Current Records on Access Form

by Susu In reply to Display Current Records o ...

This question was closed by the author

Back to Web Development Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums