How to update sub when record deleted from another sub

[doyle60]

I have two subs on a tab that are both drawing from the same table.
One sub is just simpler than the other for those who don't want to see
too much data.

The users have the ability to delete records here. When they delete
records from one sub, I want the other sub to have those records
deleted as well. Of course, they are deleted but the lines show up as
being deleted still. They see this:

#Deleted #Deleted #Deleted #Deleted

I tried each one of these in the On Delete Event of one of the subs:

Forms!OTSTranfrm!OTSTransubBuyer.Requery
Form!OTSTranfrm!OTSTransubBuyer.Requery
OTSTranfrm.Forms!OTSTransubBuyer.Requery
OTSTranfrm.Form!OTSTransubBuyer.Requery

But not the right things, of course. How do I do this?

Thanks,

Matt

 

[David H]

If by 'sub' you're referring to a subform try

[Forms]![MainFormName]![SubFormName].subform.requery

 

[Jack Leach]

or simply:

Me.subformcontrolname.Form.Requery


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

If by 'sub' you're referring to a subform try

[Forms]![MainFormName]![SubFormName].subform.requery

I have two subs on a tab that are both drawing from the same table.
One sub is just simpler than the other for those who don't want to see
too much data.

The users have the ability to delete records here. When they delete
records from one sub, I want the other sub to have those records
deleted as well. Of course, they are deleted but the lines show up as
being deleted still. They see this:

#Deleted #Deleted #Deleted #Deleted

I tried each one of these in the On Delete Event of one of the subs:

Forms!OTSTranfrm!OTSTransubBuyer.Requery
Form!OTSTranfrm!OTSTransubBuyer.Requery
OTSTranfrm.Forms!OTSTransubBuyer.Requery
OTSTranfrm.Form!OTSTransubBuyer.Requery

But not the right things, of course. How do I do this?

Thanks,

Matt

 

[David H]

Whoops. It is .form not .subform. My bad.

or simply:

Me.subformcontrolname.Form.Requery


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

If by 'sub' you're referring to a subform try

[Forms]![MainFormName]![SubFormName].subform.requery

I have two subs on a tab that are both drawing from the same table.
One sub is just simpler than the other for those who don't want to see
too much data.

The users have the ability to delete records here. When they delete
records from one sub, I want the other sub to have those records
deleted as well. Of course, they are deleted but the lines show up as
being deleted still. They see this:

#Deleted #Deleted #Deleted #Deleted

I tried each one of these in the On Delete Event of one of the subs:

Forms!OTSTranfrm!OTSTransubBuyer.Requery
Form!OTSTranfrm!OTSTransubBuyer.Requery
OTSTranfrm.Forms!OTSTransubBuyer.Requery
OTSTranfrm.Form!OTSTransubBuyer.Requery

But not the right things, of course. How do I do this?

Thanks,

Matt

 

[doyle60]

Neither of these methods work.

Jack's: Me.subformcontrolname.Form.Requery

This gets a compile error: Method or data member not found.

David's: [Forms]![MainFormName]![SubFormName].Form.requery

This gets an error when I Delete a record: Run-time error '3246':
Operation not supported in transactions.

The Main form is unbound, by the way. The two subs are on the same
tab and draw from the same table.

Thanks,

Matt

 

[David H]

Replace 'subformcontrolname' with the actual name of the subform.

 

[Banana]

IMHO, it may be actually simpler to have single subform that changes the
sourceobject based on the tab selection. That way there's no
conflicts/contentions over the same table from two different source.

Alternatively, if you have Access 2000 or newer, I *suspect* but cannot
guarantee it will work is to assign a single recordset to both subform
at the main form's open (this means both sub's recordsource should be
blank to avoid wasteful loading) and have your delete code interact with
the recordset directly. Deleting should then refer in both sub
immediately without that pesky #Deleted.

I've actually assigned a recordset to both a listbox and a form and that
worked well in keeping both consistent. But I really think the first
idea is much much simpler & robust so you should look into that.

Replace 'subformcontrolname' with the actual name of the subform.

Neither of these methods work.

Jack's: Me.subformcontrolname.Form.Requery

This gets a compile error: Method or data member not found.

David's: [Forms]![MainFormName]![SubFormName].Form.requery

This gets an error when I Delete a record: Run-time error '3246':
Operation not supported in transactions.

The Main form is unbound, by the way. The two subs are on the same
tab and draw from the same table.

Thanks,

Matt

 

[doyle60]

David:
The Name and the Source Object are the same thing (because the wizard
creates it that way):

Name: OTSTransubBuyer
Source Object: OTSTransubBuyer

Your direction seems to say I must name them differently. I tried and
it did not work still, not matter which I referred to in the
expression.

You also seem to be referring to Jack's method, right?

Anyway, I think I'll go with Banana's suggestion, which I've done
before to control speed which I didn't necessarily have to do here.

Thanks,

Matt