Coding form to change multiple records

[Angela]

I have a form that displays one record displaying information about a piece
of equipment. Each similar piece of equipment is marked with a "use order"
which indicates what order they will be installed on the machine. When the
user changes the status from "inventory" to "on machine", I automatically
change the "user order" on that piece of equipment from 1 to null (since it
is now installed). But I also want to change the other equipment that is
marked 2, 3, 4 to 1, 2, 3 to show that they have moved up in the queue.

I used the AfterUpdate event on the Status field to change the User Order of
the current record from 1 to null and that works fine. In the same
AfterUpdate event, I tried to insert code to create a recordset to find the
related equipment records and change their Use Orders, but it didn't work and
the error msg implied that the related records were locked. The database has
"edited record" locking so it appears that a page of records, including the
ones that I want to change, are all locked. I hesitate to change the
record-locking since this is a shared database.

Is there a better way to accomplish this?

Thanks

 

[Jeff Boyce]

Angela

I'm not sure, based on your description, but it sounds like you have a field
in which you store a [UserOrder] value.

And then you have to come up with a way to alter that ("moved up in the
queue")...

Instead of storing (and "re-calculating") a [UserOrder] number, could you
use a query to "calculate" the sequence? Is there some other
factor/field/value in addition to the sequence number that Access could use
to sort the remaining items in their proper order?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Angela]

The "Use Order" is assigned manually by operations personnel and the value
stored in the table (along with details of each piece of equipment). The
equipment being installed is not all identical, and they are used depending

Angela

I'm not sure, based on your description, but it sounds like you have a field
in which you store a [UserOrder] value.

And then you have to come up with a way to alter that ("moved up in the
queue")...

Instead of storing (and "re-calculating") a [UserOrder] number, could you
use a query to "calculate" the sequence? Is there some other
factor/field/value in addition to the sequence number that Access could use
to sort the remaining items in their proper order?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I have a form that displays one record displaying information about a piece
of equipment. Each similar piece of equipment is marked with a "use order"
which indicates what order they will be installed on the machine. When the
user changes the status from "inventory" to "on machine", I automatically
change the "user order" on that piece of equipment from 1 to null (since it
is now installed). But I also want to change the other equipment that is
marked 2, 3, 4 to 1, 2, 3 to show that they have moved up in the queue.

I used the AfterUpdate event on the Status field to change the User Order of
the current record from 1 to null and that works fine. In the same
AfterUpdate event, I tried to insert code to create a recordset to find the
related equipment records and change their Use Orders, but it didn't work and
the error msg implied that the related records were locked. The database has
"edited record" locking so it appears that a page of records, including the
ones that I want to change, are all locked. I hesitate to change the
record-locking since this is a shared database.

Is there a better way to accomplish this?

Thanks

 

[Jeff Boyce]

Angela

Then you'll probably need to come up with a procedure that opens a recordset
of all those "remaining" items and modifies the "sequence" number.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

The "Use Order" is assigned manually by operations personnel and the value
stored in the table (along with details of each piece of equipment). The
equipment being installed is not all identical, and they are used depending
on what is being made on the machine. So no, the use order can't be assigned
any other way.

Angela

I'm not sure, based on your description, but it sounds like you have a field
in which you store a [UserOrder] value.

And then you have to come up with a way to alter that ("moved up in the
queue")...

Instead of storing (and "re-calculating") a [UserOrder] number, could you
use a query to "calculate" the sequence? Is there some other
factor/field/value in addition to the sequence number that Access could use
to sort the remaining items in their proper order?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I have a form that displays one record displaying information about a piece
of equipment. Each similar piece of equipment is marked with a "use order"
which indicates what order they will be installed on the machine.

When
the

user changes the status from "inventory" to "on machine", I automatically
change the "user order" on that piece of equipment from 1 to null

(since
it

is now installed). But I also want to change the other equipment that is
marked 2, 3, 4 to 1, 2, 3 to show that they have moved up in the queue.

I used the AfterUpdate event on the Status field to change the User

Order
of

the current record from 1 to null and that works fine. In the same
AfterUpdate event, I tried to insert code to create a recordset to

find
the

related equipment records and change their Use Orders, but it didn't

work
and

the error msg implied that the related records were locked. The

database
has

"edited record" locking so it appears that a page of records,

including
the

ones that I want to change, are all locked. I hesitate to change the
record-locking since this is a shared database.

Is there a better way to accomplish this?

Thanks

 

[Angela]

Jeff,
I DID write a procedure for opening a recordset and changing the remaining
records "use order". It is triggered by the AfterUpdate event. However,
when it runs I get an error that these records are locked. Presumably this
is because the record is not yet saved when the AfterUpdate event is
triggered, so the surrounding records are locked. (Locked b/c the database
has edited-record locking, which locks a page of records at a time when one
is being edited). This is the problem I am trying to resolve.

I really need these related records modified after the user changes the main
record, so the afterUpdate event is a good place to trigger this. But I
don't know how to get around the record-locking without changing the database
record-locking to None, and that isn't a good solution b/c this is a shared
database. I also tried to forcee the record to Save during the AfterUpdate
event (using DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70) but I get a msg that this menuitem "isn't available now". If I
could force the record to save, then I could edit the related records.

Any help on this would be appreciated.

Angela

Angela

Then you'll probably need to come up with a procedure that opens a recordset
of all those "remaining" items and modifies the "sequence" number.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

The "Use Order" is assigned manually by operations personnel and the value
stored in the table (along with details of each piece of equipment). The
equipment being installed is not all identical, and they are used depending
on what is being made on the machine. So no, the use order can't be assigned
any other way.

Angela

I'm not sure, based on your description, but it sounds like you have a field
in which you store a [UserOrder] value.

And then you have to come up with a way to alter that ("moved up in the
queue")...

Instead of storing (and "re-calculating") a [UserOrder] number, could you
use a query to "calculate" the sequence? Is there some other
factor/field/value in addition to the sequence number that Access could use
to sort the remaining items in their proper order?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


I have a form that displays one record displaying information about a
piece
of equipment. Each similar piece of equipment is marked with a "use
order"
which indicates what order they will be installed on the machine. When
the
user changes the status from "inventory" to "on machine", I automatically
change the "user order" on that piece of equipment from 1 to null (since
it
is now installed). But I also want to change the other equipment that is
marked 2, 3, 4 to 1, 2, 3 to show that they have moved up in the queue.

I used the AfterUpdate event on the Status field to change the User Order
of
the current record from 1 to null and that works fine. In the same
AfterUpdate event, I tried to insert code to create a recordset to find
the
related equipment records and change their Use Orders, but it didn't work
and
the error msg implied that the related records were locked. The database
has
"edited record" locking so it appears that a page of records, including
the
ones that I want to change, are all locked. I hesitate to change the
record-locking since this is a shared database.

Is there a better way to accomplish this?

Thanks

 

[Jeff Boyce]

Angela

It sounds like you've already worked out the solution.

If the records you need are locked, you'll have to change them when they
aren't.

One possibility, as you've discussed, is to change the record-locking for
the database. You might want to look into changing it to allow the update,
then changing it back.

Another possibility might be to "collect" the row IDs of all of the rows
that will be affected, then do the updating on that collection after the
page lock is released.

Or maybe (sorry, no experience here) you could explicitly command Access to
release the page lock when you finish the first update.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Jeff,
I DID write a procedure for opening a recordset and changing the remaining
records "use order". It is triggered by the AfterUpdate event. However,
when it runs I get an error that these records are locked. Presumably this
is because the record is not yet saved when the AfterUpdate event is
triggered, so the surrounding records are locked. (Locked b/c the database
has edited-record locking, which locks a page of records at a time when one
is being edited). This is the problem I am trying to resolve.

I really need these related records modified after the user changes the main
record, so the afterUpdate event is a good place to trigger this. But I
don't know how to get around the record-locking without changing the database
record-locking to None, and that isn't a good solution b/c this is a shared
database. I also tried to forcee the record to Save during the AfterUpdate
event (using DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70) but I get a msg that this menuitem "isn't available now". If I
could force the record to save, then I could edit the related records.

Any help on this would be appreciated.

Angela

Angela

Then you'll probably need to come up with a procedure that opens a recordset
of all those "remaining" items and modifies the "sequence" number.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

The "Use Order" is assigned manually by operations personnel and the value
stored in the table (along with details of each piece of equipment). The
equipment being installed is not all identical, and they are used depending
on what is being made on the machine. So no, the use order can't be assigned
any other way.

:

Angela

I'm not sure, based on your description, but it sounds like you have

a
field

in which you store a [UserOrder] value.

And then you have to come up with a way to alter that ("moved up in the
queue")...

Instead of storing (and "re-calculating") a [UserOrder] number,

could
you

use a query to "calculate" the sequence? Is there some other
factor/field/value in addition to the sequence number that Access

could
use

to sort the remaining items in their proper order?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


I have a form that displays one record displaying information about a
piece
of equipment. Each similar piece of equipment is marked with a "use
order"
which indicates what order they will be installed on the machine. When
the
user changes the status from "inventory" to "on machine", I automatically
change the "user order" on that piece of equipment from 1 to null (since
it
is now installed). But I also want to change the other equipment

that
is

marked 2, 3, 4 to 1, 2, 3 to show that they have moved up in the queue.

I used the AfterUpdate event on the Status field to change the

User
Order

of
the current record from 1 to null and that works fine. In the same
AfterUpdate event, I tried to insert code to create a recordset to find
the
related equipment records and change their Use Orders, but it

didn't
work

and
the error msg implied that the related records were locked. The database
has
"edited record" locking so it appears that a page of records, including
the
ones that I want to change, are all locked. I hesitate to change the
record-locking since this is a shared database.

Is there a better way to accomplish this?

Thanks