Record a deletion in a table after a deletion on the form.

eantar
eantar used Ask the Experts™
on
How does one refresh the underlying table of a form to make sure that deletions occur in the table after they've happened on the form? Here's the situation.

 I'm building a query engine for a user. On the left side is a listbox with available fields for the query. On the right is a subform in continuous form with the fields selected for the query (see picture.The Query Engine form. When the user deletes a selected field (continuous form on the right), the On Delete sets a hidden check box to true. This acts as a flag to indicate the deletion. The flag is checked on the On Current event.

Okay. The Row Source of the list box is an SQL statement that only produces fields that have NOT been selected. Everything works fine, When a field is moved to the selected continuous form, it no longer shows in the list box. BUT after a deletion on the continuous form, when I requery the listbox, the field removed from the continuous form does NOT show up in the listbox. The reason is that even though the field HAS been deleted from the subform, it HASN'T been deleted from the table that is the datasource of the subform.

I've tried, requerying the form, refreshing...

Can anyone tell me how to force the underlying table of a form to refresh so that the deletion is captured?

Thanks,
Eddie
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Can anyone tell me how to force the underlying table of a form to refresh so that the deletion is captured?

 What's your SQL statement for the list box?  Is the criteria that it does not appear in the table the form is based on?   If so, the only thing you should need to do is requery the list box.

Jim.

Author

Commented:
Here's the SQL statement:

SELECT tblUserQueryFlds.UserQueryFldsID, tblUserQueryFlds.FldNameUser FROM tblUserQueryFlds LEFT JOIN tblUserQuerySelectedFlds ON tblUserQueryFlds.UserQueryFldsID = tblUserQuerySelectedFlds.UserQueryFldID WHERE (((tblUserQueryFlds.ExcludeFromList)=False) AND ((tblUserQueryFlds.QuerySection)="Main") AND ((tblUserQuerySelectedFlds.UserQuerySelectedFldID) Is Null)) ORDER BY tblUserQueryFlds.FldOrder;

It's basically a select statement with a left join testing for a NULL on the Selected Flds table.

Author

Commented:
Here's the code on the On Current Event of the subform...
If Parent!chkRequerylstFldsAvailable Then

    'Parent.Form.Requery
    Me.Refresh
    Parent!lstFldsAvailable.Requery
    Parent!chkRequerylstFldsAvailable = False

End If

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Your setting this:

Parent!chkRequerylstFldsAvailable

 To true when the user removes a field?   I think I'd just requery the list when they delete or add.

Jim.

Author

Commented:
Hey Jim, thanks for your response. The only way I know of to detect a deletion AFTER the deletion occurs on a form (without using After Del Confirm, which I don't have set) is to set a flag in the On Delete and then check it on the On Current that happens immediately after the deletion is completed. Is there another way?

But also, back to my original problem, why isn't the deletion occurring in the table when it happens on the form??? I know it's not because I can pop up the table and SEE the record I just deleted. Weird, right?

Let me know your thoughts.

Eddie
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Try adding a:

dbEngine.Idle dbFlushCache

 to your current OnCurrent setup.

as far as avoiding the flag altogether, AfterDelConfirm would be the event to use.  This occurs after the records have actually been deleted.

Jim.

Author

Commented:
Hi Jim, I tried the dbEngine.Idle statement above and it didn't have any effect.

On the screen below, I just deleted Review Date from the Selected Fields subform on the right QueryEngineform2.jpg
It was the only item on the Selected Fields subform. Review Date is the first that would be in the list box. But as you can see it doesn't show up.

What's more, I put a breakpoint in my On current code and tested the following in the immediate window:

? DCount("*", "tblUserQuerySelectedFlds")
 1

As you can see the table STILL has 1 record, EVEN TOUGH...

? me.Recordset.recordcount
 0


My form recordset has no records.

ARRRGH! Any ideas???

Thanks.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
is there any chance your looking at two different tables?

The dbEngine.Idle dbFlushCache  forces all changes to disk and execution won't resume until it's complete, so I can't think of anyway you should be in the spot your in.

If not, then if you could do a small sample DB and upload it, that would be great.

Jim.

Author

Commented:
Here's my RecordSource on the selected fields side:

SelectedFldsDataProps.jpg
(BTW, tried just using a table as the record source instead of the SQL statement. No avail.)

Here a picture of the Query design of the listbox rowsource:

listboxquerydesign.jpg
Here's the code triggered after the Deletion:

Dim strRowSource As String
Dim db As DAO.Database, rstUserQuerySelectedFlds As DAO.Recordset

If Parent!chkRequerylstFldsAvailable Then

'    Set db = CurrentDb
'    Set rstUserQuerySelectedFlds = db.OpenRecordset("Select * from tblUserQuerySelectedFlds", dbOpenSnapshot)
'    rstUserQuerySelectedFlds.MoveLast
'    rstUserQuerySelectedFlds.Close
'    Set rstUserQuerySelectedFlds = Nothing
'    Set db = Nothing

'    strRowSource = Parent!lstFldsAvailable.RowSource
'    Parent!lstFldsAvailable.RowSource = ""
'    Parent!lstFldsAvailable.RowSource = strRowSource


'    Parent!cmdDeselectAll.SetFocus
'    Parent.CallDeselectAll
'

    DBEngine.Idle dbRefreshCache
    Parent!lstFldsAvailable.Requery
    Parent!chkRequerylstFldsAvailable = False

End If

Open in new window


The comment sections are different things I've tried.

Do you see anything I'm missing?

If not, I'll try and compose a simple db and upload it.

Thanks a lot for being so responsive.

Eddie
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
I don't see anything.....only other thought that comes into play is filtering of the subform where it's not showing you the record, but's still in the table.

 But I would assume when your deleting, your deleting<g>

Jim.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
If you delete that one record again, then close and reopen the form (with no OnOpen and OnLoad code), without doing anything else, is the list box correct?

Jim.

Author

Commented:
Yes. If I go into design view and then reopen the form, the deleted field appears in the list box. Crazy, right?

Here's my latest hair brain scheme, ready? I'm going to store each delete value in a table, make that table part of my list box query, test if it exists in this new table, and if it does, include it in the list.

I'll let you know how that goes.

Let me know if you can think of anything else.

Thanks again.

Eddie

Author

Commented:
I got it to work... but not without some heavy lifting. Ready?

As I mentioned above, I created a table that would store the id or each record deleted. I added an outer join to the list box rowsource checking if the id exists in this new table. If it does include it in the list, even if you don't see it in the selected fields table. The actual design view of the list box query looks like this...

listboxquerydesign2.jpg
In the On Delete event of the subform I send the deleted value to this table.

In my On Command event that checks for deletions, I delete all records of this new table.

Like I said, heavy lifting, but it works.

Eddie
Distinguished Expert 2017

Commented:
why isn't the deletion occurring in the table when it happens on the form??
Because recordsets are instantiated in memory.  Access does refresh a form to show updates to data but the way your form works, you are using a data value to control selection.   Since the query is not rerun in the background when Access automatically refreshes the form, the set of selected records would never change.

The solution, which has already been mentioned and which you discarded, is to requery the listbox.  Do the requery in the subform's AfterUpdate event.  That way whether you are adding or "deleting" from the list, the listbox gets updated.

Author

Commented:
Hey Pat, how are you? I haven't disregarded the requery of the list box (take a look at my code above). That was the first thing I tried. But it doesn't seem to flush the record out of the table and back into the list box.

If there is another solution other than the crazy workaround, I'd love to hear it.

Thanks,
Eddie

Author

Commented:
No other ideas?
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
None here...it doesn't add up.

Jim.
Commented:
Well, it's good to know that the workaround works, as my client is insistent on how this should work. Jim thanks for your help on all of this. I truly appreciate it.

Eddie

Author

Commented:
Thanks again.
Distinguished Expert 2017

Commented:
But it doesn't seem to flush the record out of the table and back into the list box.
It works for me but as I said, the requery belongs in the form's AfterUpdate event so that it runs whether you move off the current record or not.  If you don't immediately move to a new record, the Current event doesn't run to cause the requery.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial