Requery not good on delete

  • Thread starter Thread starter CJ
  • Start date Start date
C

CJ

Hi Groupies

I have the following code attached to an After Update event so that
when I make a change to a value in a subform, other subs on the parent
update.

DoCmd.Echo False
DoCmd.OpenQuery "qdelWeightCapture"
Me.Parent.Requery
DoCmd.OpenQuery "qappWeightCapture"
Me.Parent.Requery
DoCmd.Echo True

However, if I attach the same code to a Delete event on the subform
although the queries run, the subforms do not update and reflect the
new values after I delete a record?

One subform just shows #Deleted# the other doesn't change at all.
I have confirmed that the queries both run.
 
Try doing a requery on each of the subforms as well as the parent form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
Hi Jeanette, thanks for popping in. Happy Australia Day!!

I changed the code to this:

DoCmd.Echo False
DoCmd.Open Query "qdelWeightCapture"
DoCmd.Open Query "qappWeightCapture"
Me.Parent!fsubWeightIn.Form.Requery
Me.Parent!fsubCurrentTicketCalculations.Form.Requery
Me.Parent.Requery
DoCmd.Echo True

The action queries fire but I get the runtime error, "Record is deleted" and
the code breaks
at the first requery. Plus, my database freezes.
 
CJ

i do the exact same thing with this code..
carefull with the set warnings the first time
Maybe there is an issue with your subform references??

Private Sub ButtonUpdateHomeLocation_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "Q_UpdateHomeLocations"
DoCmd.OpenQuery "Q_ClearTempScan"
DoCmd.Requery "F_HomeLocationsTempScanSubbySKU"
DoCmd.Requery "F_LocationContentsbySKU"
DoCmd.SetWarnings True
Me!LocationNameCombo.SetFocus
End Sub

Barry
 
Happy Australi Day to you CJ.

Depending on the relationships between the tables, you may need to delete
records form child tables before you try to delete the parent record from
the parent table.
Do you have referential integrity set between the tables involved in the
form and subforms?
Look at your tables and relationships and try to figure out which record
depends (is related to) a record in the parent table.
Change your code to delete from the child tables before deleting from the
parent table.

You may not need to requery both subforms - which subfrom should show
changes after the record is deleted?

Your queries suggest that you are deleting a record and then appending
another record.
Perhaps try to run the delete query then requery what ever is appropriate.
Then run the append query then requery what ever is appropriate.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
Thanks Jeanette. I think you nailed the answer with the Parent/Child
Relationship.

Sorry for the extremely late reply.....my internet connection has been down
for 15 days!
.....I'm a wee bit bitter.....

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Jeanette Cunningham said:
Happy Australi Day to you CJ.

Depending on the relationships between the tables, you may need to delete
records form child tables before you try to delete the parent record from
the parent table.
Do you have referential integrity set between the tables involved in the
form and subforms?
Look at your tables and relationships and try to figure out which record
depends (is related to) a record in the parent table.
Change your code to delete from the child tables before deleting from the
parent table.

You may not need to requery both subforms - which subfrom should show
changes after the record is deleted?

Your queries suggest that you are deleting a record and then appending
another record.
Perhaps try to run the delete query then requery what ever is appropriate.
Then run the append query then requery what ever is appropriate.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
Click to expand...
 
Back
Top