We help IT Professionals succeed at work.
Get Started

List Box woes

MikeDTE asked
Last Modified: 2012-05-12
I have a form which shows Bookings for an Event.  I wish to cancel a booking which removes it from the bookings list.

The bookings are in a list box on the Bookings form. The list box selected record changes the Booking details that are displayed on the rest of the Bookings form.

There is an event financial summary and an event bookings stats summary on the Event  form.

When I run the BookingCancel code in the Bookings form:

I copy all pertinent financial data from the Bookings table to an Audit table.  

I update the booking Status field for the booking record in the Bookings table with ‘CANX’ using:

strSQL = "UPDATE tJDWBookings SET jdwb_Status = 'CANX' WHERE jdwb_BookingID = " &  _ Me.jdwbBookingID
CurrentDb.Execute strSQL, dbFailOnError

I run a ‘bomb proof’ sub proc that updates the financial summary on the Events form.

I run a ‘bomb proof’ sub proc that updates the event bookings stats summary on the Events form.

(NB the two sub procs mentioned above are called from dozens of other procs in the Bookings, Payments, Contacts and Events forms – without issues – that is why I describe them as ‘bomb proof’.)

Post BookingCancel code run – I observe:

The financial data on the Events form is correct.

The event booking stats data on the Events form is correct.

The bookings list box on the Bookings form is correct – i.e. the cancelled record has been removed.

The data displayed in the booking details pertains to the cancelled (and now removed) booking record – obviously a problem.

The problem is that the focus has not returned to the bookings list box and row 0 is not selected.  I would like it to the Bookings form bookings list box select row 0.

By selecting row 0 the booking details displayed for the list box row 0 booking will be refreshed.

If I click on row zero in the Bookings form bookings list box then it is all refreshed and all OK.

I have tried all combinations and orders of the following:

Me.List_Bookings.Selected(0) = True

The Requery works – without this the Bookings form bookings list box still displays the cancelled booking record.

A secondary problem is:

What if there and no bookings left – i.e. the only existing booking has just been cancelled?

I had considered just closing the Bookings form using:

DoCmd.Close acForm, "fJDWBookings"

Actually whenever I cancel a booking (last and only booking or otherwise) I would just warn the user that the form would close. The user would just re-open the Bookings form from the Event form.  Then if there are no records existing code is already in place to handle a ‘no bookings’ scenario.

… but both DoCmd instructions generate the following error:

“The expression you entered refers to an object that is closed or doesn't exist."

This also locks up Access a treat and nothing short of killing Access in Task Manager will clear the error or allow further use of Access.

Any ideas out there?
Watch Question
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
This problem has been solved!
Unlock 1 Answer and 19 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE