Saving and Updating Records

  • Thread starter Thread starter CSDunn
  • Start date Start date
C

CSDunn

Hello,
I have an Access 2000 Project in which the data comes from a SQL Server 2000
database, and multiple users need to be able to see new records as each user
adds records. The users also need to be aware of updates as they are made to
current records. The data the users are looking at is presented in a subform
that has the Default View property set to 'Single Form'.

In order for each user to see the update to the current record as each user
is looking at the current record, I have implemented the following code for
the onClick event of a button labeled 'Show Update to this Record':

dim bookmark as string
bookmark = me.bookmark
me.refresh
me.bookmark = bookmark

With this code, a user can make a change to a record, then another user on
the same record can click the 'Show Update to this Record' button (which
runs the above code), and then that other user can immediately see the
update that was made to the record by the other user.

All users also need to see new records as they are added by other users. My
first attempt at this has been a with a Macro assigned to the onClick event
of a second button labeled 'Save and Show New Records'. The macro looks like
this:

RunCommand - SaveRecord
RunCommand - RefreshPage
RunCommand - RecordsGoToLast

When a user adds a record, and then clicks the 'Save and Show New Records'
button, the other users can click the same button and see the new records
added. Another user can update the new record, and the other users can see
the new update to the new record by clicking the 'Show Update to this
Record' without navigating away from that record.

However, I am having a problem with the following sceanrio:

1. User 'A' adds record 1 and clicks 'Save and Show New Records'.
2. User 'B' clicks 'Save and Show New Records' to see record 1.
3. User 'B' makes a change to record 1.
4. User 'A' clicks 'Show Update to this Record' and can see the update to
record 1.
5. User 'A' adds record 2 and clicks 'Save and Show New Records'.
6. User 'B' clicks 'Save and Show New Records' and can see record 2.
7. User 'B' goes back to record 1 clicks 'Show Update to this Record' to see
if anything has changed with record 1.
8. User 'A' adds record 3 and clicks 'Save and Show New Records'.
9. User 'B' clicks 'Save and Show New Records' to see record 3, but even
though the navigation bar indicates there is a third record, user 'B' cannot
navigate to it without closing the entire form and reopening it.

The bottom line is that I need suggestions that will make new records
available to all users as they are added by any other user, and make updates
available to all users in the same way.

Please let me know if you have any ideas on this.

Thanks for your help!

CSDunn
 
Hi,


Neither a keyset, neither a dynaset can see records added by other users
(including VBA code as "another" user than the one in front of the
keyboard), unless you requery it, which is similar to closing and reopenning
it, Use Me.Requery to requery the form. Note that the bookmarks can't be
kept, so, if you need to navigate back to a record, save its primary key
value, requery, move back to the record with its primary key equal to the
value you saved:


Dim x as Long
x=Me.PrimaryKey
Me.Requery
With Me.recordsetClone
.FindFirst "PrimaryKey=" & x
If .NoMatch Then
Else
Me.Bookmark=.Bookmark
End If
End With



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top