Refreshing a form in another session (1 Viewer)

[Thicko]

Hi All,

We have an Access front end with SQL Server backend.

What I want to achieve is when a user in the pharmacy performs an action to say treatment is ready (table updated) the form that appears on the ward (and run on a separate PC) gets refreshed to inform them that treatment is ready.

I did consider using the time interval function but past experience tells me this slows down the system and would prefer a different approach.

I can leave it so users have to manually press the refresh button but that's just going to irritate all.

Any ideas welcome.

 

[CJ_London]

Either use a timer or if your users are using the form, or any other form in the same app you can requery that form to refresh it on any event they are triggering

in one of my apps I have a navigation form so user clicks any option and a message box is requeried

 

[The_Doc_Man]

Understand that Access only does things based on events. If nobody does anything, neither does Access. Therefore, a timer is a first preference most of the time.

HOWEVER, you might experiment with the Access Front-End file. In that file, check the ribbon and find File >> Options >> Client Settings - then scroll down to Advanced and look at the refresh interval. If the information you want is displayed on your forms and usually is blank but perhaps might have room for your notification, the screen update every refresh interval might achieve your purpose. If you have a "master" FE file that you copy to each user station, you can change the refresh interval and then download a new FE from the master copy. That would have the effect of distributing any new settings.

Failing that, if you have other events going on, you could make each event's code include a call to a "notification handler" that would load any new notes into an appropriate place on screen. That might be tedious, but it would do the trick. Just remember, Access doesn't do anything until someone does something - so you will need either a timer or an "other action" handler OR you might get lucky on the refresh interval.

 

[CJ_London]

Problem with refresh is it only refreshes the existing records on the form. If the pharmacy has added a record required to appear on the other form, it won't be displayed on refresh. Only way it might work is if there is just the one record which the user updates rather than adding a new one i.e. record updated, not table updated.

So for example if this form can have multiple records generated by the ward with a 'status' field autopopulated with 'requested', the pharmacy can then change this status to 'ready' - that should show up at the next refresh on the ward computer. The ward can then go collect the prescription and change the status to say 'fulfilled' and requery - which if the filter or criteria is something like "status<>'fulfilled'" should remove it from the list.

If you have a "master" FE file that you copy to each user station, you can change the refresh interval and then download a new FE from the master copy.

Not sure that would work as it is a client setting which I don't believe gets copied across (but happy to be proved wrong). Default is 60 seconds

 

[bastanu]

Maybe something like a custom chat message would work that pops up saying the treatment is available. Here is a sample:http://forestbyte.com/ms-access-utilities/fba-ms-access-custom-chat/

Cheers,

 

[Pat Hartman]

There is no way PC1 can make PC2 do anything. Doc has produced long explanations of why. Leave it to say it is all about Windows, Isolation, and Security. So, no, you cannot do what you requested.

That leaves you with the present method of always refreshing the screen before doing anything OR of relying on a timer event to execute a Me.Requery frequently. How frequently? I don't know. Is 60 seconds enough? Is 5 seconds too fast? Is 2 minutes too slow? This is a medical application. If you will be relying on the timer to show the new data, how frequently are the users going to look at the screen? How dangerous is it if someone goes to the screen and a message is pending but the timer hasn't fired yet so they miss it.

Maybe you want to look into having Access send emails or text messages to the attendants. That seems to be the method in US hospitals.

 

[Thicko]

Thank you all. It was a long shot but appreciate affecting another PC running the same database is a no go.

I'll go with the Interval Timer option and see how it works.

 

[CJ_London]

if you can set your data up as I described, the refresh route is worth a try - see attached db

Open both forms side by side. Enter a new entry in the ward form. After 60 seconds it appears in the pharmacy form - which does have a timer to requery the data - but you might have some other event you can use instead of a timer - event doesn't have to be on the same form.

Now on the pharmacy side they can only change the status from 'Request' to 'Ready'.

Once they have done that, 'Ready' will be displayed within 60 seconds on the ward form. So off they go to to the pharmacy and on their return, change the status to 'fulfilled'. This will not remove the record on the ward form because to do that requires a requery - but easy to add that to the status after update event.

In the meantime the pharmacy form will have been requeried and the record removed.

Timerinterval on the pharmacy form is 60 seconds as is the refresh in file>options>client settings used by the ward form.

This just tests the principle - connect to your sql server, put a copy of the db in the ward and pharmacy and see if it works through

 

[gemma-the-husky]

Just to be clear, you need me.requery rather than me.refresh. one irritation about me.requery is that the form resorts, and places the cursor at the first item, which might be irritating for users

I suppose you could have a timer checking a table for a count of today's items, and when the count changes, automatically requery. Something like that.

 

[MarkK]

If you must requery, you can return the current record pointer to where it was before, like...

Sub SoftRequery(frm As Form)
   Dim pos As Long

   With frm.Recordset
      pos = .AbsolutePosition
      frm.Requery
      .AbsolutePosition = pos
   End With
End Sub

 

[Pat Hartman]

@MarkK What happens if the inserted record is logically in front of the record on which you are positioned when you run the requery? Wouldn't the absolute position be off by 1? I've always done this by using the PK of the current record to reposition the view.

 

[MarkK]

What I meant was...

Sub SoftRequery(frm As Form)
   Dim pps As Single

   With frm.Recordset
      pps = .PercentPosition
      frm.Requery
      .PercentPosition = pps
   End With
End Sub

...which works well if the current row was deleted, thanks for noticing.

 

[Pat Hartman]

Thanks