Refresh Table - #Deleted Issue

[Guest]

I have a temporary table which after one enters information, they push a
button which is suppose to run a query to move the data to a permanent table,
delete the record from the temp table, and then refresh the form - but my
form shows #Deleted in all the fields and I can't figure out what else to
do. I need the temp table in order to find a next number. Here's the code on
my button to move, delete & refresh the form. Thanks in advance for the
help!!! Jani

Private Sub cmdSaveThisRecord_Click()
On Error GoTo Err_cmdSaveThisRecord_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

stDocName = "macTempDataToSQl"
DoCmd.RunMacro stDocName

Me.Refresh


Exit_cmdSaveThisRecord_Click:
Exit Sub

Err_cmdSaveThisRecord_Click:
MsgBox Err.Description
Resume Exit_cmdSaveThisRecord_Click

End Sub

 

[Guest]

I dont know what version of Access you are using but you shoukld no longer
use that method of deleting records; use 'DoCmd.RunCommand acCmdSaveRecord'
instead. Also, you should avoid using macros if at all possible; use VB
instead.
I suspect you are still positioned on the deleted record, that is why it is
displaying #deleted.
The whole way you are generating numbers sounds very strange. It does not
make sense to save data in a temporary table then move it to a permanent
table. But maybe I dont know the whole story.

Dorian

 

[Guest]

Dorian - thanks for the quick reply - I am on a time crunch (as almost
everyone is these days). So I don't know VB but attempted to do it and the
following code does move the record from the temp file into the permanent one
but I still get the #Deleted in the cells and a message that says "The
Microsoft Jet database engine stopped the process because you and another
user are attempting to change the same data at the same time." There is no
other db open. Need some help and appreciate it very much. Here's the code:
Jani

Private Sub cmdSaveThisRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click

DoCmd.RunCommand acCmdSaveRecord

stDocName = "qryAppendBudgetData"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "qryResetTblBudgetDataTemp"
DoCmd.OpenQuery stDocName, acNormal, acEdit

DoCmd.RunCommand acCmdDeleteRecord

Exit_cmdSaveRecord_Click:
Exit Sub

Err_cmdSaveRecord_Click:
MsgBox Err.Description
Resume Exit_cmdSaveRecord_Click

End Sub

 

[J. Goddard]

I think you want to use the me.requery method, not the me.refresh method.

John