Sometimes can't save with Wizard's Save button

[Jordan]

I need to replace the DoMenuItem command below:

DoCmd.DoMenuItem _
acFormBar, _
acRecordsMenu, _
acSaveRecord, , _
acMenuVer70

With something that will update the record with all the fields that have
changed. Maybe a loop or somthing that will cycle through all the controls
on the form that have a control source.

The reason is the DoCmd.DoMenuItem method is causing all Access (2003)
clients to get locked out of certain blocks of records and I can't seem to
find any answer why.

The reason I want something that will loop through the controls is because I
am using the DoCmd routine as a part of a function that all my data entry
forms call. For example I have a form that has ReturnNumber, Customer,
Priority, etc and another that has TuningNumber, Customer, Status, Rev, etc.
so I can't build hard coded SQL queries for each.

 

[Minton M]

I need to replace the DoMenuItem command below:

DoCmd.DoMenuItem _
acFormBar, _
acRecordsMenu, _
acSaveRecord, , _
acMenuVer70

With something that will update the record with all the fields that have
changed. Maybe a loop or somthing that will cycle through all the controls
on the form that have a control source.

The reason is the DoCmd.DoMenuItem method is causing all Access (2003)
clients to get locked out of certain blocks of records and I can't seem to
find any answer why.

The reason I want something that will loop through the controls is because I
am using the DoCmd routine as a part of a function that all my data entry
forms call. For example I have a form that has ReturnNumber, Customer,
Priority, etc and another that has TuningNumber, Customer, Status, Rev, etc.
so I can't build hard coded SQL queries for each.

Just my two cents, but DoMenuItem is the WORK OF THE DEVIL. Mostly,
you can find alternatives, especially for things like save, delete and
undo, but since the Access menus changes religiously with every
version - and it's a PITA to understand the syntax anyway - it seems
like an incredibly bad idea to use them.

I'm not clear from your post why DoCmd is so neccesary but it's almost
never necessary. If you can provide a little more detail, I'll be
happy to help.

-- James

 

[Linq Adams via AccessMonster.com]

There's absolutely no reason to loop thru controls, simply to save a record
that has changed.You would only do this if you were runnng some kind of data
validation code, not simply to save all changed controls.

The two standard way of saving changing to a record are either

doCmd.RunCommand acCmdSaveRecord

or:

If Me.Dirty Then Me.Dirty = False

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com

 

[Jordan]

The reason I was going to loop through the controls was to build a SQL
statement to run. I know of the acCmdSaveRecord option, but I did not think
that it would be significantly different than

 

[Jordan]

Sorry, hit the send button to early.

The reason I was going to loop through the controls was to build a SQL
statement to run. I know of the acCmdSaveRecord option, but I did not think
that it would be significantly different than DoMenuItem since it is an
Access command rather than an SQL connection through ADO.

To answer Minton's question on what I am doing, I am just using a form and
subform as an order entry system. The header's data source is an Order
Master table while the subform links to an Order Details table. The header
and subform are linked by Order Number.

I have some code on the forms to keep users from changing data by mistake by
forcing them to turn on an edit button to let them edit and hitting the save
button to save the record and lock the form back up.

This has worked very well for many years but for some reason that I can't
place only the Access uses sporatically cannot update certain (not all but
just certain) records until all Access users log out. I have other systems
that use straight SQL commands that can still access the records and I can
go to the SQL 2000 server itself and edit the exact records with no problem,
but when it gets in this mode all Access users can't modify certain records.

I have looked on the SQL server for locks and blocks, but none show. I have
tried to see if someone else was editing the same record or page and I have
never found someone doing that. Even when I try see if I can edit the same
record with two clients I can do it and I get the "Record has Changed"
message as I expected so even editing the same record does not appear to be
causing the lockout of only Access users. It only happens during the saving
by using the DoMenuItem command. Do you think the acCmdSaveRecord will
operate any differently?