Updating table records when form is open - save/drop changes

[Hindsey]

I have a public module that I wrote that updates one field in all the records
in a table. This module can be run from a couple of different forms that may
be opened depending on what the user is doing.

When I have a particular form open that is tied to the same table that is
being updated, I call that module after a combo box is updated. The
AfterUpdate runs the module, updates all the records in the table. When I go
to chang record on the form, or to close the form, I get the error saying
that the record has been changed by another user since you started editing
it. I can Save, Copy to Clipboard, or Drop Changes.

Does anyone have any recommendations on the best way to get around this
problem?

 

[a a r o n . k e m p f]

move to SQL Server.

it just works; and if it doesn't you have control over it. for example
WITH (NOLOCK)

Accesss wasn't designed-- from the ground up-- to be used by multiple
users.

-Aaron

 

[BruceM]

Ignore Aaron. He's a troll who infests this group from time to time with
one answer to every question.

I would question why you need to update every record upon modifying a combo
box selection on one record. Without knowing some specifics of the code or
of your database's structure it is difficult to be specific. It could be
that a requery is all you need. It could even be that SQL Server makes
sense, but it won't solve design or execution problems, if that is what you
have going on.

 

[Hindsey]

I have a field that is the Order for a truck to be dispatched. Whatever truck
has [OrderNumber] of 1 will be at the top of my list. On the dispatch screen,
when I put a truck back in service, it runs the module and sets that truck's
[OrderNumber] to the highest number in the list, and then runs through the
rest of the records and re-orders them beginning at one.

At the same time, if I change the crew on that truck, I want it to be put at
the bottom of the list too. The dropdown for "Seat 1" selects a person from
my personnel table. After that (or any of the other 6 seats) are updated, I
want it to be put at the bottom of the list, and move all the others up.

I hope this answers the question -

 

[Hindsey]

I have a field that is the Order for a truck to be dispatched. Whatever truck
has [OrderNumber] of 1 will be at the top of my list. On the dispatch screen,
when I put a truck back in service, it runs the module and sets that truck's
[OrderNumber] to the highest number in the list, and then runs through the
rest of the records and re-orders them beginning at one.

At the same time, if I change the crew on that truck, I want it to be put at
the bottom of the list too. The dropdown for "Seat 1" selects a person from
my personnel table. After that (or any of the other 6 seats) are updated, I
want it to be put at the bottom of the list, and move all the others up.

I hope this answers the question -

 

[BruceM]

I hope this isn't a multi-user application, or at least that you don't have
several people reordering the records.

If the order is arbitrary you may have no real choice but to renumber as you
have done. If your code performs the renumbering as intended, have you
tried a requery? Without seeing your code it is difficult to offer specific
suggestions.

Could you order the records by inserting a time stamp when the record is
modified, or something like that? If you could change one of the seats
(whatever that means) on two records, which one is above the other in the
ranking?

I have a field that is the Order for a truck to be dispatched. Whatever
truck
has [OrderNumber] of 1 will be at the top of my list. On the dispatch
screen,
when I put a truck back in service, it runs the module and sets that
truck's
[OrderNumber] to the highest number in the list, and then runs through the
rest of the records and re-orders them beginning at one.

At the same time, if I change the crew on that truck, I want it to be put
at
the bottom of the list too. The dropdown for "Seat 1" selects a person
from
my personnel table. After that (or any of the other 6 seats) are updated,
I
want it to be put at the bottom of the list, and move all the others up.

I hope this answers the question -

Ignore Aaron. He's a troll who infests this group from time to time with
one answer to every question.

I would question why you need to update every record upon modifying a
combo
box selection on one record. Without knowing some specifics of the code
or
of your database's structure it is difficult to be specific. It could be
that a requery is all you need. It could even be that SQL Server makes
sense, but it won't solve design or execution problems, if that is what
you
have going on.

 

[Hindsey]

The problem with the timestamp idea is that the records do need to be
re-ordered manually from time to time.

It will be a multi-user environment, but the odds of more than one person
doing a re-order at the same time are pretty slim.

I tried having the afterupdate run the code, and then requery the form
(Forms!frmApparatus.Requery). When the requery runs, I get the same error
that another use has modified the record since you've made changes.

The code to cycle through and update the records is this:

'assign the modified apparatus to be 99, a higher number than will ever be
used
CurrentDb.Execute "UPDATE tabApparatus SET OrderNumber = '99' WHERE
Apparatus = '" & fApp & "'"
Dim rstConn As ADODB.Connection
Dim rst As New ADODB.Recordset
Set rstConn = CurrentProject.Connection
rstSQL = "SELECT * FROM tabApparatus ORDER BY OrderNumber"
rst.Open rstSQL, rstConn , adOpenDynamic, adLockOptimistic
If rst.EOF = False Then
rst.MoveLast
rst.MoveFirst
varCount = DCount("[Apparatus]", "tabApparatus")
For i = 1 To varCount
rst.Fields("OrderNumber") = i
rst.Update
rst.MoveNext
Next i
End If
rst.Close
Set rstConn = Nothing

Any more thoughts?

I hope this isn't a multi-user application, or at least that you don't have
several people reordering the records.

If the order is arbitrary you may have no real choice but to renumber as you
have done. If your code performs the renumbering as intended, have you
tried a requery? Without seeing your code it is difficult to offer specific
suggestions.

Could you order the records by inserting a time stamp when the record is
modified, or something like that? If you could change one of the seats
(whatever that means) on two records, which one is above the other in the
ranking?

I have a field that is the Order for a truck to be dispatched. Whatever
truck
has [OrderNumber] of 1 will be at the top of my list. On the dispatch
screen,
when I put a truck back in service, it runs the module and sets that
truck's
[OrderNumber] to the highest number in the list, and then runs through the
rest of the records and re-orders them beginning at one.

At the same time, if I change the crew on that truck, I want it to be put
at
the bottom of the list too. The dropdown for "Seat 1" selects a person
from
my personnel table. After that (or any of the other 6 seats) are updated,
I
want it to be put at the bottom of the list, and move all the others up.

I hope this answers the question -

Ignore Aaron. He's a troll who infests this group from time to time with
one answer to every question.

I would question why you need to update every record upon modifying a
combo
box selection on one record. Without knowing some specifics of the code
or
of your database's structure it is difficult to be specific. It could be
that a requery is all you need. It could even be that SQL Server makes
sense, but it won't solve design or execution problems, if that is what
you
have going on.

I have a public module that I wrote that updates one field in all the
records
in a table. This module can be run from a couple of different forms
that
may
be opened depending on what the user is doing.

When I have a particular form open that is tied to the same table that
is
being updated, I call that module after a combo box is updated. The
AfterUpdate runs the module, updates all the records in the table. When
I
go
to chang record on the form, or to close the form, I get the error
saying
that the record has been changed by another user since you started
editing
it. I can Save, Copy to Clipboard, or Drop Changes.

Does anyone have any recommendations on the best way to get around this
problem?

 

[a a r o n . k e m p f]

yah, because Access doesn't support multiple users (relaibly)

I would hope that if you had multiple users, you would just move to
SQL Server for sure

-Aaron

I hope this isn't a multi-user application, or at least that you don't have
several people reordering the records.

If the order is arbitrary you may have no real choice but to renumber as you
have done.  If your code performs the renumbering as intended, have you
tried a requery?  Without seeing your code it is difficult to offer specific
suggestions.

Could you order the records by inserting a time stamp when the record is
modified, or something like that?  If you could change one of the seats
(whatever that means) on two records, which one is above the other in the
ranking?

I have a field that is the Order for a truck to be dispatched. Whatever
truck
has [OrderNumber] of 1 will be at the top of my list. On the dispatch
screen,
when I put a truck back in service, it runs the module and sets that
truck's
[OrderNumber] to the highest number in the list, and then runs through the
rest of the records and re-orders them beginning at one.

At the same time, if I change the crew on that truck, I want it to be put
at
the bottom of the list too. The dropdown for "Seat 1" selects a person
from
my personnel table. After that (or any of the other 6 seats) are updated,
I
want it to be put at the bottom of the list, and move all the others up..

I hope this answers the question -

"BruceM" wrote:

 

[a a r o n . k e m p f]

one answer to every question:

a) it just works
b) realistic security
c) code portability
d) performance
e) scalability
f) enterprise level tools

yes i have one answer to every question.
If you care enough to build a database-- use an _ENGINE_ with a
future.

SQL Server has it. It is the worlds most popular database.

Access doesnt' have a future; it just got replaced by XML.

-Aaron

 

[BruceM]

As usual, you missed the point in your headlong advocacy of SQL Server. No
matter the system, having multiple users reorder the records will likely
lead to confusion, especially in a situation where the order apparently
determines which truck is to be sent out first.

message

yah, because Access doesn't support multiple users (relaibly)

I would hope that if you had multiple users, you would just move to
SQL Server for sure

-Aaron

I hope this isn't a multi-user application, or at least that you don't
have
several people reordering the records.

If the order is arbitrary you may have no real choice but to renumber as
you
have done. If your code performs the renumbering as intended, have you
tried a requery? Without seeing your code it is difficult to offer
specific
suggestions.

Could you order the records by inserting a time stamp when the record is
modified, or something like that? If you could change one of the seats
(whatever that means) on two records, which one is above the other in the
ranking?

I have a field that is the Order for a truck to be dispatched. Whatever
truck
has [OrderNumber] of 1 will be at the top of my list. On the dispatch
screen,
when I put a truck back in service, it runs the module and sets that
truck's
[OrderNumber] to the highest number in the list, and then runs through
the
rest of the records and re-orders them beginning at one.

At the same time, if I change the crew on that truck, I want it to be
put
at
the bottom of the list too. The dropdown for "Seat 1" selects a person
from
my personnel table. After that (or any of the other 6 seats) are
updated,
I
want it to be put at the bottom of the list, and move all the others up.

I hope this answers the question -

"BruceM" wrote:

 

[BruceM]

Access was replaced by XML??? This is an odd tangent, even for you.

Anyhow, the OP did ignore you, so maybe that part of the posting had some
effect. Or maybe it was because the question was asked in an Access group,
and your answer was not about Access.

message
one answer to every question:

a) it just works
b) realistic security
c) code portability
d) performance
e) scalability
f) enterprise level tools

yes i have one answer to every question.
If you care enough to build a database-- use an _ENGINE_ with a
future.

SQL Server has it. It is the worlds most popular database.

Access doesnt' have a future; it just got replaced by XML.

-Aaron

 

[a a r o n . k e m p f]

my answer is Access.

Access Data Projects.

only a retard would use Access as a database-- I mean SQL Server is
free and faster and easier, in every regard.

-Aaron