=> Updating Forms in a multi-User Environment

  • Thread starter Thread starter Rhonda Fischer
  • Start date Start date
R

Rhonda Fischer

Hello,

When I make changes to a form on my screen these
changes need to immediately be accessible to other
Users. Currently changes on my screen are not
reflected on the screen of others, until they close
this form and reopen it. How can I display changes
instantly while the form remains open on anothers screen?

Any suggestions would be terrific.

Thank you
Rhonda
 
Rhonda Fischer said:
Hello,

When I make changes to a form on my screen these
changes need to immediately be accessible to other
Users. Currently changes on my screen are not
reflected on the screen of others, until they close
this form and reopen it. How can I display changes
instantly while the form remains open on anothers screen?
Click to expand...

Are you talking about design changes to the form or data entry?

In the case of the former, the form is loaded into memory when it is opened and any
changes you would make to the object after it is opened are simply not going to be
seen by other users unless they close and reopen the form. This would only be
possible in Access 97 or older because the newer version don't even allow design
changes unless you're the only one in the file. Even with the older versions it is a
bad idea.

If you're talking about data entry a form refresh will show changes to existing
records without closing and reopening. To see the effects of additions or deletions
you would have to issue a form Requery command. The form's timer event could be set
up to issue either of these commands at regular intervals, but the Requery would also
cause the form to go back to the first record in the table. Not something you would
want to do to a user.

If you're talking about a single record form where everyone is looking at the same
data all the time, a Refresh should cause any changes to appear.
 
Rhonda Fischer said:
Hello,

When I make changes to a form on my screen these
changes need to immediately be accessible to other
Users. Currently changes on my screen are not
reflected on the screen of others, until they close
this form and reopen it. How can I display changes
instantly while the form remains open on anothers screen?

Any suggestions would be terrific.

Thank you
Rhonda
Click to expand...

I don't believe you can. Furthermore, I think it's a very bad idea for you
to be making design changes in a database that is currently open by other
users, and the question implies that you and the other users are sharing a
database file that has not been split into front-end and back-end. This is
a situation that often leads to database corruption.

The common, tried and true approach to sharing an Access database is to
split the database into a front-end database in which all the tables are
linked to a back-end .mdb file that contains only the data. Then each user
gets her own copy of the front-end. You (the designer) make changes to your
copy of the front-end and distribute that to the users when you're done,
replacing their old copies.
 
Hi Rhonda,

The answer is that you can't do this - and to be honest, you shouldn't be
trying to make development changes to a production database. The better
method is to have a split database (Data in backend, application objects in
front-end). Each user has his/her own front-end which is linked to the back
end. Then when you the developer need to make changes, you do so in your own
copy of the front-end and then you distribute the modified front-end to all
users. If they need the modifications immediately, then they must close out,
obtain the modified front-end and reopen the application.

This all sounds like a nuisance but - it solves a lot of problems,
corruption being one of them but there are others as well.

To assist with rolling out new versions of the frontend there are several
utilities that could help you. Tony Toews has one on his website
(http://www.granite.ab.ca/access/autofe.htm). This article on Tony's site
also explains some of the justification for revamping your db structure. FMS
has utility which is named Total Access Startup
(http://www.fmsinc.com/products/startup/index.asp)
 
Dear Rick,

I am referring to data entry and as you said the
requery that I have put in a Timer event does indeed
take the user back to the first entry. How might I
avoid this.

I am working on a database for a transport company of
whom all the users are constantly referring to the one
form open permanently on their screens. Other users
are constantly entering information that needs to be
updated within moments of entry on the screens of other
users, and I'm not quite sure how to do this without the
irritation of returning to the top record?

Do you have any ideas or suggestion?

Thank you kindly
Rhonda
-----Original Message-----
"Rhonda Fischer" <Rhonda.Fischer@Turners-
Click to expand...
Distribution.com> wrote in message
Are you talking about design changes to the form or data entry?

In the case of the former, the form is loaded into memory when it is opened and any
changes you would make to the object after it is opened are simply not going to be
seen by other users unless they close and reopen the form. This would only be
possible in Access 97 or older because the newer version don't even allow design
changes unless you're the only one in the file. Even
Click to expand...
with the older versions it is a
bad idea.

If you're talking about data entry a form refresh will show changes to existing
records without closing and reopening. To see the
Click to expand...
effects of additions or deletions
you would have to issue a form Requery command. The
Click to expand...
form's timer event could be set
up to issue either of these commands at regular
Click to expand...
intervals, but the Requery would also
cause the form to go back to the first record in the
Click to expand...
table. Not something you would
want to do to a user.

If you're talking about a single record form where
Click to expand...
everyone is looking at the same
 
Are you showing all records in a continuous form?

You can get back to the record that had the focus as long as you have a primary
key. You need to store the primary key, then do the requery, then move back to
the record with the primary key.

Assuming you are using DAO, your code might look something like the UNTESTED
AIRCODE below.

Dim lngPK as Long 'Assumes the primary key is an autonumber or a number of type LONG

lngPK = Me.txtRecordID 'Assumes control on form is named txtRecordID
Me.Requery

With Me.recordsetClone
.FindFirst "txtRecordID = " & lngPk
If .NoMatch = False Then
Me.BookMark = .BookMark
End If
End with
 
Hello John,

I am using ADO and trying the following code. The
refresh works fine however repositioning the record
to place last used record at the top doesn't seem to
work. I am using ADO and the following code. I don't
really understand how the bookmark works.

Any suggestions would be terrific.

Thank you kindly
Rhonda




Private Sub Form_Timer()

Dim lngPK As Long

lngPK = CLng(Me.txtID)
DoCmd.Requery '*** Code fine to here

'*** Code doesn't seem to have any effect from here
With Me.Recordset
.FindFirst "ID" = " & lngPK"
If .NoMatch = False Then
Me.Bookmark = .Bookmark '*** not sure what
End If '* this line does
End With

End Sub



-----Original Message-----
Are you showing all records in a continuous form?

You can get back to the record that had the focus as long as you have a primary
key. You need to store the primary key, then do the requery, then move back to
the record with the primary key.

Assuming you are using DAO, your code might look something like the UNTESTED
AIRCODE below.

Dim lngPK as Long 'Assumes the primary key is an
Click to expand...
autonumber or a number of type LONG
 
Is the ID a number or autonumber? Or is it text?

Private Sub Form_Timer()

Dim lngPK As Long

lngPK = CLng(Me.txtID)
DoCmd.Requery '*** Code fine to here

'*** Code doesn't seem to have any effect from here
With Me.RecordsetClone '<<< USE RecordsetClone
.FindFirst "ID" = " & lngPK '<<< REMOVE Quote Mark
If .NoMatch = False Then
Me.Bookmark = .Bookmark 'Set bookmark of recordset
End If 'to bookmark of the clone
End With

End Sub
 
Hello John,

Thank you very much for your help.

The ID is an autonumber with last entry of: 348783
I am collecting this value from a text box on a form.

When the user slides the scroll bar to the bottom
of the page I hope to perform an update without
resetting the first record to the top of the page.
I guess the code I am using will do this once it
is working - on a timer event.

Thank you again for your assistance.
Rhonda

*********************************************
Private Sub Form_Timer()

Dim lngPK As Long

lngPK = CLng(Me.txtID)
DoCmd.Requery

With Me.RecordsetClone '<== *** still no effect
.FindFirst "ID" = " & lngPK" '*** from here
If .NoMatch = False Then '<== when would .NoMatch
Me.Bookmark = .Bookmark 'be called
End If
End With

End Sub
 
Back
Top