By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,105 Members | 2,620 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,105 IT Pros & Developers. It's quick & easy.

Saving and Updating Records

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Use Requery instead of Refresh.

--
MGFoster:::mgf
Oakland, CA (USA)

CSDunn wrote:
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


Nov 12 '05 #2

P: n/a
I changed the code for the onClick event of the button "Show Update to
this Record" as follows:

Dim varBookMark As Variant
varBookMark = Me.Recordset.bookmark
Me.Requery
Me.Recordset.bookmark = varBookMark

If I left the macro for the button 'Save and Show New Records' as
follows:

RunCommand - SaveRecord
RunCommand - RefreshPage
RunCommand - RecordsGoToLast

I still have the same problem. If I change the Macro as follows:

RunCommand - SaveRecord
Requery - Text65
RunCommand - RecordsGoToLast

I get the following message after I click 'Save and Show New Records':
"You can't use the ApplyFilter action on this Window". I have tried to
apply the Requery to the subform object itself, a bound control, and an
unbound control.

Why would I get this message when I attempt to Requery?

Thanks again!

CSDunn

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3

P: n/a
I don't know. It's one of those "I'd have to see it to understand why
it is acting like it does." That wasn't a request for your application :-).

--
MGFoster:::mgf
Oakland, CA (USA)

Chris Dunn wrote:
I changed the code for the onClick event of the button "Show Update to
this Record" as follows:

Dim varBookMark As Variant
varBookMark = Me.Recordset.bookmark
Me.Requery
Me.Recordset.bookmark = varBookMark

If I left the macro for the button 'Save and Show New Records' as
follows:

RunCommand - SaveRecord
RunCommand - RefreshPage
RunCommand - RecordsGoToLast

I still have the same problem. If I change the Macro as follows:

RunCommand - SaveRecord
Requery - Text65
RunCommand - RecordsGoToLast

I get the following message after I click 'Save and Show New Records':
"You can't use the ApplyFilter action on this Window". I have tried to
apply the Requery to the subform object itself, a bound control, and an
unbound control.

Why would I get this message when I attempt to Requery?

Thanks again!

CSDunn

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.