How to deal with deleted record

[Shep]

On a form with a listbox which the user views almost constantly, how can I deal with deleted records?

They will show as #Deleted, of course. If the user clicks on that row, the After_Update code runs. Bad things happen, as you can imagine.

Is there a way to detect whether a record in that recordset has been deleted, so that I can issue a requery?

I don't want to use a manual requery button...which would do little good anyway if a user clicks on the #Deleted row. I can trap the resulting error if necessary, but am looking for something more seamless. Any ideas?

Many thanks

 

[Ally]

I presume you must delete the record from another form? What about requerying the listbox on close event of the form you've deleted it from?

 

[Shep]

That's a thought...

But I didn't mention that this is a split database with many users. The form which contains the listbox is in the front end. How's that for afterthought?

 

[Ally]

Ah. Not sure about implications of that - not really dealt with splits.

 

[smed]

you could grab the current record number, using the OnTimer event requery the form and move to the last record viewed.

 

[Shep]

I gave your suggestion some consideration too, smed...thanks.

I dislike the Timer; I've seen a lot of screen flicker when it's used and it bothered me enough at the time to swear off the thing unless absolutely necessary.

I finally decided to do nothing about it, though.

The listbox was a list of customers. The form itself is used for finding or adding a customer, choosing or adding a 'ship to' address, choosing or adding a piece of equipment to be serviced then continuing to a service order form with the collected information.

The code necessary to find customers whose accounts are inactive slows the form too much. Simplicity and speed, and the fullest possible functionality within those confines, is my goal here.
Adding code to check for customers who've recently been set to inactive (while the form's been open) slowed searches and After Update events by .25 to .5 seconds. Too much for me to swallow.

I rationalized that since the customer has struggled into your facility, made it to the front desk and thrown a piece of equipment up onto the counter, the accounting department shouldn't have set the customer inactive, as they obviously are not.

 

[Rakier]

There are a couple of other ways to try to correct this.

I've had luck requerying the list using the Activate event of the form with the list. When you close the updating form, the list form becomes active and requeries the list.

Another thought is to requery the list using the Click event for the list. That way when an entry is clicked on the list, it will requery the list.

Just a couple of thoughts.

 

[Shep]

The 'deletion' of the customer account would normally be done by a person in a different department. The form with listbox would likely always be the active form until the user advances to the next step of the order creation. In other words, the form would only rarely lose focus, so the Activate event would seldom fire.

The second...
Hmm...

Sometimes the simplest of ideas are overlooked until someone points it out. I'll do a requery on one event or another and see if it slows code execution enough to bother me. I know it will add some time, but it may be justifiable. It might even be the cat's meow, who knows?