Refreshing forms - on a networked front end. How?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi guys.

I've created a database which I have split into a front and a back end. The
database is working well but I'm just ironing out the tweaks and niggles.

I have a telephone log which 10 users use to enter details of phone calls,
we each all have an 'inbox' in the database - a form which shows all entries
until they are marked as complete.

If I enter a call on my PC the person who's inbox I sent the call to updates
and the call is there, however this only happens on my PC. How do I get this
to happen to their front end over the network? As at the moment they have to
exit and reload the form in order to view their calls.
 
Richard,

Use a tiomer event on the form to requery itself periodically (very
minute /couple of?).

HTH,
Nikos
 
Can anyone elaborate on how exactly to do this?

Nikos Yannacopoulos said:
Richard,

Use a tiomer event on the form to requery itself periodically (very
minute /couple of?).

HTH,
Nikos
Click to expand...
 
Open your form in design view;
Select the form object (Edit > Select Form) and display the properties
window (View > Properties);
On tab Events, locate the Timer Interval property, and set it's value to
60000 (time unit is 1/1000 sec, so this setting is for every minute);
Put the cursor in the On Timer property, click the little button with
the ellipsis sign that appears on the right, and select Code Builder;
When taken to the VBA window, you wil see the following two lines:
Private Sub Form_Timer()

End Sub
and the cursor in between. Type these lines of code between those two lines:

vCurrent = Me.Bookmark
Me.Requery
Me.Bookmark = vCurrent

Return to form design and save. The job is done.

HTH,
Nikos
 
Hi Nikos, thank you for your reply! That does the job exactly and you even
included code to make sure the current record remains in focus.

One other thing, if there is no record in the form I get an error message
saying there is no record to bookmark. Is there any way around this?

Also sometimes I get the error message not a valid bookmark. This particular
form uses conditional formatting, could this be anything to do with it?
 
Richard,

I don't think this has got anything to do with conditional formatting.
Just precede those three lines of code with:

On Error Resume Next

This will result in lines producing errors to be skipped from execution,
so it wil take care of both.


HTH,
Nikos
 
Hi Nikos, firstly thank you for your help on this and the previous other
times you've helped, it's people like that you that make this community so
useful. So a massive thank you to you.

I've done as you said which has indeed prevented the error messages from
appearing, the only problem now is that the bookmark function is not working
now. After every timed refresh the record goes back to the very first record
which in the context for which I'm applying this code, is an annoying thing.
Can you see any other way around this? I've tried researching the bookmark
error and everything I've found so far mentions corrupt databases, but as far
as I can see mine isn't as I've compacted and reparied many times and
everything else works perfectly. It's frustrating as it's almost there apart
from this small thing!
 
Richard,

I can't imagine why this should be happening. If you want, you are
welcome to mail me your database to have a look at. If you do it, then
please:
* Remove any sensitive data, if any; replace with ficticious.
* Remove the bulk of the data, leaving just some sample data (which
still produce the problem!) if your database is over 1MB in size, after
compacting
* Compact and zip
Sorry about all this, but I don't have a fast connection.

Regards,
Nikos
 
Richard,

In the meantime, it just occurred to me that the requery should not be
allowed while entering new data. To achieve this, the first line in the
timer sub should be:

If Me.NewRecord Then Exit Sub

HTH,
Nikos
 
Hi Nikos, thank you for your help and your kind offer, but my database is way
too big to email. Also I have split it into a front and a back end and the
database is beign used as we speak so I can't really email it to you.

My front end alone is 8mb!

I will pesevere and see if I can figure out the problem.
Thank you anyway my friend.
 
Better yet, make it:

If Me.NewRecord Or Me.Dirty Then Exit Sub

so the requery is also prevented while editing a record.
 
Added that Nikos and that does indeed fix the error message if there are no
records in the inbox, or the only record is a new record.

In my testing of your other code, the error message about invalid bookmark
always occurs when the code gets to the line:

Me.Bookmark = vCurrent

Does that give you any more information or are you still as puzzled as I am?
Do I have to give the bookmarks different names for each different form?
Could that be the problem? IF maybe more than one form is trying to set a
record as vCurrent?
 
Nikos thanks again for your help. I posted this error on another forum and
someone gave me the following code which works perfectly! So if you come
across this problem again, you can re-use the code below.

Dim rst As DAO.Recordset
Dim lngID As Long

lngID = [Tel ID]



Me.Requery

Set rst = Me.RecordsetClone


rst.FindFirst "[Tel ID] = " & lngID
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If

rst.Close

Set rst = Nothing
 
Back
Top