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?