Record Deleted Error

[Leesha]

Hi,
I have a form that is attached to a table "tblRentCalculatorCOVID19", that only contains 1 row of data. The form is used to calculate data by ID. The user clicks a button that runs the various calculations in cells on the form. Some cells are linked to items in "tblRentCalculatorCOVID19" and others are unlinked and just perform calculations.

Each time the ID number is changed the user clicks on the refresh button. The code deletes the data in tblRentCalculatorCOVID19 and appends the new data to the table. When this is complete, the form should requery however each time that the code gets to the code me.requery I get an error that the record has been deleted. I've tried saving the record before the requery and I've tried naming the form to requery but still get the same error.

This is the code I'm using:

DoCmd.OpenQuery "qryDeleteRentCalculatorCOVID19"
DoCmd.OpenQuery "qryAppendRentCalculatorCOVID19"

DoCmd.OpenQuery "qryDeleteRentCalculatorSalesTemp"
DoCmd.OpenQuery "qryAppendRentCalculatorSalesTemp"
DoCmd.OpenQuery "qryupdaterentcalulatorCOVID19"

'Forms!frmrentcalculator.Requery

Me.Requery

'DoCmd.OpenForm "frmRentCalculator"

I noticed that the code works fine if the user simply refreshes the data for the ID, however if they clicked the code to run the calculations and then they refresh the date for the new ID, that is when the error that the record has been deleted comes up.

Is there a way around this?
Thanks,
Leesha

[burrina]

I believe you need to use (add) an update query to change the value(s) to null
HTH

[HansV]

It might be better to update the existing record instead of deleting it and appending a new one.

[CData]

while I don't totally follow your posted sequence of events - - I think all you need to do is close and reopen the form...it will happen so quickly the user won't be affected and may be around the requery method.

[Leesha]

Hi,
I tried the update to null query vs the delete query but that didn't work and it resulted in adding a new row of data vs keeping the table at 1 row of data.
Leesha

[burrina]

I would decompile your code and then recompile and of course make a copy of your database. Other than that we need to see some sample db without any confidential info.