How to refresh Form on record change? 1

[Katya85S]

An Access database has linked SQL Server tables. SQL Server database gets changed from a web page. The changes in the SQL Server database does not show up on Access form unless user closes and opens the form again. The data needs to be updated while user navigates from record to record. What Event should I use to update data on the form with records navigation?

Thank you all in advance

 

[AnnabelleMarie]

Try the requery method after updating:

frmMyForm.Requery


Hope this helps.

 

[Katya85S]

Thank you for your replay. The problem is I don't know on what event to put the Reqyery method. There is no really updates through the Access form. One user enters information from Web Page, the other user views the entered data from the Access database, moving from one record to another using navigation buttons.

 

[PHV]

Try the Current event procedure of the form.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Katya85S]

No, the navigation buttons stoped working with Me.Requery on Curent Event of the form. YOU can see that some processes are going on, but the first record remains on teh form.

 

[PHV]

Sorry, my bad.

 

[AnnabelleMarie]

Try putting the requery statement in the Form_AfterUpdate event. I hope this works.

 

[Katya85S]

I have already tried it. It doesn't
No new entries are displayed.

 

[AnnabelleMarie]

I'm sorry I couldn't be of more help. :-( Hopefully someone else will be able to help you.

 

[Katya85S]

Not a problem. I appreciate the intension to help very much. Thank you AnnabelleMarie

 

[PHV]

Just a thought:
in the Current event procedure you may requery a cloned recordset and compare the recordcount ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MoLaker]

The reason a requery won't work is because the form will settle on the 1st record of the recordset after requery. Consequently, the record nav buttons appear to take you to the 1st record every time.

One way to accomplish what you are attempting could be to grab a unique identifier key field (you do have one, right?) then perform a requery. Once the requery is performed, go to the record of that key. However, this still leaves the matter of which event to use. If the OnCurrent event is used, it could become cyclic as the action of requerying will trigger the event. It may be you will have to disable all built-in record navigation (including PgUp/PgDn) and place your own custom nav buttons on the form. Then you can fire the requery any time a nav button is clicked. Once requeried, go to the record of the "grabbed" key.

 

[Katya85S]

Thank you guys for sharing your thoughts. I appreciate it allot.
MoLaker, thanks for the idea to build my own navigation buttons to fire Requery method when needed. This was helpful.