Getting Error 2046 when run under access 2007 but works in 2010

[ryetee]

I have the following code which works under access 2010
Me.Refresh
DoCmd.SetWarnings False
Me.AllowDeletions = True
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
Me.AllowDeletions = False

DoCmd.SetWarnings True


Recently a user who only has access 2007 gets error 2046 on the line

DoCmd.RunCommand acCmdDeleteRecord

This is a production error so haven't investigated/googled anything yet. Any help appreciated

 

[MarkK]

I would modify that code regardless, because there is no explicit indication of what object that code acts on. You can see, I'm sure, that if the current object doesn't allow deletions, then the line of code the performs the deletion will fail. But to me, that is very risky code. I would also always provide a confirmation popup for deletions.

 

[ryetee]

I would modify that code regardless, because there is no explicit indication of what object that code acts on. You can see, I'm sure, that if the current object doesn't allow deletions, then the line of code the performs the deletion will fail. But to me, that is very risky code. I would also always provide a confirmation popup for deletions.

The user clicks on a button next to the displayed record. There is actually a message asking him (twice) if he wants to go ahead with the delete.

This code has been working fine with version 2010 but throws up the error above with access 2007. If I can get away with a simple fix in line with the existing code it will be easier to deploy.

 

[MarkK]

You cannot make that code reliable. The object against which you are running your SelectRecord and your DeleteRecord is ambiguous. You should cross your fingers and knock on wood before running DoCmd.RunCommand, because the target of the command is outside of your control. Most of the time it will do what you hope, but if and when it doesn't, like now, you have no recourse. So the method is not reliable. Sorry if that is not what you wanted to hear.

 

[ryetee]

You cannot make that code reliable. The object against which you are running your SelectRecord and your DeleteRecord is ambiguous. You should cross your fingers and knock on wood before running DoCmd.RunCommand, because the target of the command is outside of your control. Most of the time it will do what you hope, but if and when it doesn't, like now, you have no recourse. So the method is not reliable. Sorry if that is not what you wanted to hear.

So do I need to build and then run select and delete bits of sql?
The only reason I'm loathe to change it is not because I don't want to but the way it's been designed and built is fragile to say the least. If I change the VBA I think I may have to change other bits and bobs as well. I was hoping to get a quick fix in for the user before suggesting I rewrite this part of the system

 

[MarkK]

You could try the delete method of the recordset, but you're still leveraging elements of the user interface to do that...

Me.Refresh
Me.AllowDeletions = True
Me.Recordset.Delete
Me.AllowDeletions = False

I would be more likely to find the ID of the row and execute a query, like...

CurrentDb.Execute "DELETE FROM table WHERE rowID = " & me.RowID

...which operates directly on the table, and so has the fewest dependencies on other objects. As a result, we don't have to suppress warnings or allowdeletions or anything.
Hope this helps,

 

[ryetee]

You could try the delete method of the recordset, but you're still leveraging elements of the user interface to do that...

Me.Refresh
Me.AllowDeletions = True
Me.Recordset.Delete
Me.AllowDeletions = False

I would be more likely to find the ID of the row and execute a query, like...

CurrentDb.Execute "DELETE FROM table WHERE rowID = " & me.RowID

...which operates directly on the table, and so has the fewest dependencies on other objects. As a result, we don't have to suppress warnings or allowdeletions or anything.
Hope this helps,

Great!! Many thanks.
The "Me.Recordset.Delete" has got me over the hump and given me some time and some breathing space to do this correctly

 

[Galaxiom]

CurrentDb.Execute "DELETE FROM table WHERE rowID = " & me.RowID

Execute should always include the second parameter to that an error is thrown if there are any problems.

CurrentDb.Execute "DELETE FROM table WHERE rowID = " & me.RowID, dbFailOnError

Otherwise any errors are silently ignored.

 

[ryetee]

You could try the delete method of the recordset, but you're still leveraging elements of the user interface to do that...

Me.Refresh
Me.AllowDeletions = True
Me.Recordset.Delete
Me.AllowDeletions = False

I would be more likely to find the ID of the row and execute a query, like...

CurrentDb.Execute "DELETE FROM table WHERE rowID = " & me.RowID

...which operates directly on the table, and so has the fewest dependencies on other objects. As a result, we don't have to suppress warnings or allowdeletions or anything.
Hope this helps,

Actually the "Me.Recordset.Delete" solution worked when I first fixed the problem on the rogue machine but then user was getting error 3021 so I've gone straight to "DELETE FROM table WHERE rowID = " & me.RowID[/CODE]
"

Not sure why I was getting the 3021 error but not had a problem (yet!!) with the straight delete.

 

[MarkK]

Make sure you add the dbFailOnError parameter to the CurrentDb.Execute method as per Galaxiom's post. DAO does not raise its errors to VBA by default.

 

[ryetee]

Make sure you add the dbFailOnError parameter to the CurrentDb.Execute method as per Galaxiom's post. DAO does not raise its errors to VBA by default.

Done that!