Requery not good on delete

[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.

 

[Jeanette Cunningham]

Try doing a requery on each of the subforms as well as the parent form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[CJ]

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.

 

[Barry A&P]

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

 

[Jeanette Cunningham]

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

 

[CJ]

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!

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