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

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
LVL 5
eantarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
eantarAuthor 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.
eantarAuthor 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

Maximize Customer Retention with Superior Service

The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
eantarAuthor 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 (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
eantarAuthor 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 (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
eantarAuthor 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 (Microsoft MVP/ EE MVE)President / OwnerCommented:
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 (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
eantarAuthor 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
eantarAuthor 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
PatHartmanCommented:
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.
eantarAuthor 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
eantarAuthor Commented:
No other ideas?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
None here...it doesn't add up.

Jim.
eantarAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eantarAuthor Commented:
Thanks again.
PatHartmanCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.