Runtime Error 2501

T

Tom

Running Access 2003 on multiple machines with the latest updates.

This code is called in a Form by selecting a button. It opens a Table and
changes the Field Names and then saves the Table. It stores the status of
two buttons as well in a Table named Spicer Option Status. This code has
worked flawlessly for a long time, but recently my company has updated to
IBM T410 Machines. On the IBMs, this code NOW halts on the RunCommand
acCmdSave line. Has something changed or are files not installed? I am
stumped.

Have also tried adding DoCmd.RunCommand acCmdSave with no luck.

Can anyone advise what's going on and how to fix it?

TIA!

Private Sub OptSpicerOn_GotFocus()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Spicer Option Status")
rs.MoveFirst
rs.Edit
rs!Setting = "2"
rs.Update
rs.Close
SpicerOpt.Value = "2"
DoCmd.Echo False
DoCmd.OpenTable "Cross Detail - Spicer", acViewDesign, acEdit
SendKeys "{DOWN}", True
SendKeys "Red", True
SendKeys "{DOWN}", True
SendKeys "Spicer", True
RunCommand acCmdSave
RunCommand acCmdClose
DoCmd.Echo True
DoCmd.Echo False
DoCmd.OpenTable "Cross Detail - Spicer", acViewDesign, acEdit
SendKeys "{DOWN}", True
SendKeys "{DOWN}", True
SendKeys "{DOWN}", True
SendKeys "{DOWN}", True
SendKeys "BoxRed", True
SendKeys "{DOWN}", True
SendKeys "Box", True
RunCommand acCmdSave
RunCommand acCmdClose
DoCmd.Echo True
End Sub
 
J

John Spencer

I'm not sure why this is broken, but using SENDKEYS is almost always a bad idea.

Why is it necessary to rename the fields in the table? Can't you use a query
to alias them instead?

SELECT Field1 as Red
, Field2 as Spicer
, Field3
, Field4
, Field5 as BoxRed
, Field6
, Field7 as Box
....
FROM [Cross Detail - Spicer]

If I ***had*** to do this I would use DDL SQL queries to add the new fields to
the table, then populate the fields with an Update query, and then use DDL SQL
query to drop the old fields if necessary.

For instance to add the column RED (assumption RED is a text field), you would
execute SQL statements that looked like the following.
Dim strSQL
strSQL = "Alter Table [Cross Detail - Spicer] ADD Column RED Text(255)"
CurrentDb().Execute strSQL
strSQL = "UPDATE [Cross Detail - Spicer] SET [RED] = [Field1]"
CurrentDB().Execute strSQL
strSQL = "ALTER Table [Cross Detail - Spicer] DROP Field1"
CurrentDB().Execute strSQL


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
T

Tom

John,

The 'Cross Detail - Spicer' Table has the following Field Names left to
right:

Comp Part | Spicer | Red | Re-Order | Box | BoxRed | ProductLine | Brand

The Access application interchanges a Competitive Part to our Part, i.e.
Comp Part to Spicer Part. Sometimes, we need to Downgrade the interchange
to our Economy Part, Red.

The Queries and Reports are written to interchange Comp Part to Spicer, the
primary interchange. So, to interchange the Comp Part to our Red Part, I
came up with the code (with help from the Forums) below to Rename the
Fields so the Queries would work.

If there is a better or more efficient way to accomplish this, I am open.
But, I do need to keep the ability to switch the relationship between Comp
Part and Spicer and Red. The Box and BoxRed also coincide with the Spicer
and Red Fields and the Boxes needed to changeover the product changes.

Thanks

Tom

----------------

I'm not sure why this is broken, but using SENDKEYS is almost always a bad idea.

Why is it necessary to rename the fields in the table? Can't you use a query
to alias them instead?

SELECT Field1 as Red
, Field2 as Spicer
, Field3
, Field4
, Field5 as BoxRed
, Field6
, Field7 as Box
...
FROM [Cross Detail - Spicer]

If I ***had*** to do this I would use DDL SQL queries to add the new fields to
the table, then populate the fields with an Update query, and then use DDL SQL
query to drop the old fields if necessary.

For instance to add the column RED (assumption RED is a text field), you would
execute SQL statements that looked like the following.
Dim strSQL
strSQL = "Alter Table [Cross Detail - Spicer] ADD Column RED Text(255)"
CurrentDb().Execute strSQL
strSQL = "UPDATE [Cross Detail - Spicer] SET [RED] = [Field1]"
CurrentDB().Execute strSQL
strSQL = "ALTER Table [Cross Detail - Spicer] DROP Field1"
CurrentDB().Execute strSQL


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Running Access 2003 on multiple machines with the latest updates.

This code is called in a Form by selecting a button. It opens a Table and
changes the Field Names and then saves the Table. It stores the status of
two buttons as well in a Table named Spicer Option Status. This code has
worked flawlessly for a long time, but recently my company has updated to
IBM T410 Machines. On the IBMs, this code NOW halts on the RunCommand
acCmdSave line. Has something changed or are files not installed? I am
stumped.

Have also tried adding DoCmd.RunCommand acCmdSave with no luck.

Can anyone advise what's going on and how to fix it?

TIA!

Private Sub OptSpicerOn_GotFocus()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Spicer Option Status")
rs.MoveFirst
rs.Edit
rs!Setting = "2"
rs.Update
rs.Close
SpicerOpt.Value = "2"
DoCmd.Echo False
DoCmd.OpenTable "Cross Detail - Spicer", acViewDesign, acEdit
SendKeys "{DOWN}", True
SendKeys "Red", True
SendKeys "{DOWN}", True
SendKeys "Spicer", True
RunCommand acCmdSave
RunCommand acCmdClose
DoCmd.Echo True
DoCmd.Echo False
DoCmd.OpenTable "Cross Detail - Spicer", acViewDesign, acEdit
SendKeys "{DOWN}", True
SendKeys "{DOWN}", True
SendKeys "{DOWN}", True
SendKeys "{DOWN}", True
SendKeys "BoxRed", True
SendKeys "{DOWN}", True
SendKeys "Box", True
RunCommand acCmdSave
RunCommand acCmdClose
DoCmd.Echo True
End Sub
Click to expand...
Click to expand...
 
T

Tom

John,

If I have this code attached to a Selection Button:

Private Sub OptSpicerOn_GotFocus()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Spicer Option Status")
rs.MoveFirst
rs.Edit
rs!Value = "Red"
rs!Setting = "2"
rs.Update
rs.Close
SpicerOpt.Value = "2"
End Sub

How do I get a Query to pick up the Spicer Otion Status.Value of Red from
the Table?

Thx

Tom

----------------------

I'm not sure why this is broken, but using SENDKEYS is almost always a bad idea.

Why is it necessary to rename the fields in the table? Can't you use a query
to alias them instead?

SELECT Field1 as Red
, Field2 as Spicer
, Field3
, Field4
, Field5 as BoxRed
, Field6
, Field7 as Box
...
FROM [Cross Detail - Spicer]

If I ***had*** to do this I would use DDL SQL queries to add the new fields to
the table, then populate the fields with an Update query, and then use DDL SQL
query to drop the old fields if necessary.

For instance to add the column RED (assumption RED is a text field), you would
execute SQL statements that looked like the following.
Dim strSQL
strSQL = "Alter Table [Cross Detail - Spicer] ADD Column RED Text(255)"
CurrentDb().Execute strSQL
strSQL = "UPDATE [Cross Detail - Spicer] SET [RED] = [Field1]"
CurrentDB().Execute strSQL
strSQL = "ALTER Table [Cross Detail - Spicer] DROP Field1"
CurrentDB().Execute strSQL


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Running Access 2003 on multiple machines with the latest updates.

This code is called in a Form by selecting a button. It opens a Table and
changes the Field Names and then saves the Table. It stores the status of
two buttons as well in a Table named Spicer Option Status. This code has
worked flawlessly for a long time, but recently my company has updated to
IBM T410 Machines. On the IBMs, this code NOW halts on the RunCommand
acCmdSave line. Has something changed or are files not installed? I am
stumped.

Have also tried adding DoCmd.RunCommand acCmdSave with no luck.

Can anyone advise what's going on and how to fix it?

TIA!

Private Sub OptSpicerOn_GotFocus()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Spicer Option Status")
rs.MoveFirst
rs.Edit
rs!Setting = "2"
rs.Update
rs.Close
SpicerOpt.Value = "2"
DoCmd.Echo False
DoCmd.OpenTable "Cross Detail - Spicer", acViewDesign, acEdit
SendKeys "{DOWN}", True
SendKeys "Red", True
SendKeys "{DOWN}", True
SendKeys "Spicer", True
RunCommand acCmdSave
RunCommand acCmdClose
DoCmd.Echo True
DoCmd.Echo False
DoCmd.OpenTable "Cross Detail - Spicer", acViewDesign, acEdit
SendKeys "{DOWN}", True
SendKeys "{DOWN}", True
SendKeys "{DOWN}", True
SendKeys "{DOWN}", True
SendKeys "BoxRed", True
SendKeys "{DOWN}", True
SendKeys "Box", True
RunCommand acCmdSave
RunCommand acCmdClose
DoCmd.Echo True
End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

runtime error 2001 4
Need expert coding help with a debugging issue. 12
Runtime Error 3197- ..... Same data at the same time 1
Treeview - Complicated multi recordset problem 0

Top