asked on

List Box woes

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.Requery
Me.List_Bookings.Selected(0) = True
Me.List_Bookings.SetFocus

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
or
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?

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

Don't remember off the top of my head but at at one time (version) I think you also had to do:

me.list_Bookings.Selected(0) = true

in order for that row in the listbox to get highlighted.

MikeDTE

ASKER

Hi Fyed

Immediate answer on your second post: I did do

Me.List_Bookings.Selected(0) = True

It's in my original post

MikeDTE

ASKER

Hi Fyed

OK the "me.list_Bookings = me.list_Bookings.Itemdata(0)" works inasmuch that List_Bookings Row 0 now highlights (has got focus) but it is not selected because the booking details are not refreshed.

The "Call list_Bookings_Click" causes a compile error - I have no function called 'List_Bookings_Click' - I do have a List_Bookings_AfterUpdate proc. I'll try calling that.

Dale Fye

Yes, but that doesn't actually change the value of the listbox, thus the

me.list_Bookings = me.list_Bookings.itemdata(0)

Dale Fye

And the Click event won't fire automatically when you do that, so you have to call it after setting the value.

MikeDTE

ASKER

Hi Fyed

Hey - success in part one of the problem. The details now refresh.

I need ideas on what to do with part two - i.e.

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
or
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.

Rey Obrero (Capricorn1)

try this revision

Me.List_Bookings.Requery
Me.List_Bookings.SetFocus
Me.List_Bookings.Selected(0) = True ' first item is selected
Me.List_Bookings.value=Me.List_Bookings.itemdata(0) '< set the value of the listbox to the item selected

Dale Fye

Can you post all of the code associated with the Cancel button (actually may just be the BookingCancel subroutine code). I assume that code is in the forms code module, not a public code module?

MikeDTE

ASKER

Fyed

Just to clarify

I have the following code which works absolutely fine (for part one - i.e. where there is at least one remaining booking in List_Bookings:

Me.List_Bookings.Requery
Me.List_Bookings = Me.List_Bookings.ItemData(0)
Call List_Bookings_AfterUpdate

Dale Fye

This might be what you were looking for WRT if there are no items left in the Bookings listbox.

Me.List_Bookings.Requery
if me.list_Bookings.listCount > 0 then
Me.List_Bookings = Me.List_Bookings.ItemData(0)
Call List_Bookings_AfterUpdate
Else
docmd.close acform, me.name
end sub

MikeDTE

ASKER

Hi Fyed

I can see the logic - I had something similar in mind.

The problem is that every-time in use the DoCmd.Close in this proc I get:

“The expression you entered refers to an object that is closed or doesn't exist" and Access just totally locks-up.

With your code in place I also get the error "No current record."

Dale Fye

As I requested above. Post all of the code associated with cancelling the booking. If it is a click event of a button, include that. Also include the BookingCancel code.

MikeDTE

ASKER

Hi Fyed

Here is the CancelBooking code - this code is the only event for the Button that call sit and the button is on the same form.

Private Sub CancelBookingButtonB_Click()
On Error GoTo Err_CancelBookingButtonB_Click

    Dim strBookingID As String
    Dim strContactName As String
    Dim strTemp As String
    Dim lngMember As Long
    Dim curPayment As Currency
    Dim curDepPaid As Currency
    Dim bln1stRec As Boolean
            
    'Move focus away from command button
    
    Me.List_Bookings.SetFocus
    
    'Disable other command buttons
    
    Me.BBOpsButtonB.Enabled = False
    Me.PartsButtonB.Enabled = False
    Me.ReportsButtonB.Enabled = False
    Me.CancelButtonB.Enabled = False
    Me.SaveButtonB.Enabled = False
    Me.CloseButtonB.Enabled = False
    Me.PaymentsButtonB.Enabled = False
    
    'Disable FindButton in Events form
    
    Forms!fJDWEvents!FindButtonE.Enabled = False
    
    If Me.jdwbStatus = "CANX" Then
        
        stOK = MsgBox("This Booking is already CANX!", 64, "Booking information")
    
        GoTo Exit_CancelBookingButtonB_Click
    
    Else
  
        lngMember = Me.jdwbMember
        
        strBookingID = Me.jdwbBookingID
        strTemp = ELookup("con_Lastname", "tContact", "con_ContactId=" & lngMember)
        strTemp = strTemp & ", " & ELookup("con_Firstname", "tContact", "con_ContactId=" & lngMember)
        strContactName = strTemp & ", " & ELookup("con_Title", "tContact", "con_ContactId=" & lngMember)
        
        If MsgBox("Cancelling Booking " & strBookingID & " (" & strContactName & ")?" & vbNewLine & _
            vbNewLine & "Are you sure?" & vbNewLine & vbNewLine & _
            "This will cause financial data to be transferred from the" & vbNewLine & _
            "Bookings table to the Audit table." & vbNewLine & vbNewLine & _
            "The booking will also be removed from the list of bookings" & vbNewLine & _
            "and all financial data will be recast.", 49, "CANX Booking?") = vbCancel Then
            
            GoTo Exit_CancelBookingButtonB_Click
        
        Else
                
            If Nz(DCount("jdwb_BookingID", "qCanxBookingAudit"), 0) = 0 Then
            
                MsgBox "No Bookings were found for the event", 64, "Booking information"
            
            Else
    
                'Open recordsets for tJDWAudit and query and copy selected data to tJDWAudit
                
                'Find out if there has been a payment made
                
                curPayment = Forms!fJDWBookings!jdwbTotPaid
                
                If curPayment > 0 Then
            
                    'Find out if there has been a deposit paid
                
                    curDepPaid = Forms!fJDWBookings!jdwbDeposit
                
                    If curDepPaid = 0 Then
                    
                        '1st payment was total sum (no deposit made so standard depost per participant applies)
                        
                        curDepPaid = Val(Forms!fJDWEvents!jdwe_StdDeposit) * Val(Forms!fJDWEvents!jdweNoParticipants)
                        
                    End If
            
                End If
                               
                Dim RS04 As DAO.Recordset
                Dim RS05 As DAO.Recordset
                Dim QDF04 As DAO.QueryDef
                               
                Set QDF04 = CurrentDb.QueryDefs("qCanxBookingAudit")
                QDF04.Parameters("jdwb_BookingID").Value = [Forms]![fJDWBookings]![jdwbBookingID]
                
                Set RS04 = QDF04.OpenRecordset
                Set RS05 = CurrentDb.OpenRecordset("tJDWAudit")
    
                RS04.MoveFirst
                               
                bln1stRec = True
                
                Do Until RS04.EOF
                    
                    RS05.AddNew
                    
                    RS05!jdwa_ContactID = RS04!jdwb_Member
                    RS05!jdwa_EventID = RS04!jdwe_EventID
                    RS05!jdwa_BookingRef = RS04!jdwb_BookingID
                    RS05!jdwa_PaymentRef = RS04!jdwp_Payment
                    RS05!jdwa_RcptNo = RS04!jdwp_RcptNo
                    RS05!jdwa_StartDate = RS04!jdwb_Start
                    RS05!jdwa_AmountPaid = Nz(RS04!jdwp_Amount, 0)
                    RS05!jdwa_DatePaid = RS04!jdwp_Date
                    RS05!jdwa_AmountRefunded = 0
                    RS05!jdwa_Refund = RS04!jdwp_Refund
                    RS05!jdwa_Withheld = False
                    RS05!jdwa_Notes = RS04!jdwb_Notes
                    RS05!jdwa_DateCANX = Date
                    
                    'Only show deposit paid on first payment
                    
                    RS05!jdwa_DepositPaid = IIf(bln1stRec = True, curDepPaid, 0)
                    
                    bln1stRec = False
                    
                    'Get cancelled booking stats data
                    
                    
                    RS05.Update
                    
                    If Not RS04.EOF Then RS04.MoveNext
                
                Loop
    
                RS04.Close
                RS05.Close
                
                Set RS04 = Nothing
                Set RS05 = Nothing
                
                'Re-enable FindButton in Events form
        
                Forms!fJDWEvents!FindButtonE.Enabled = True
                
                Dim strSQL As String
                                        
                strSQL = "UPDATE tJDWBookings SET jdwb_Status = 'CANX' WHERE jdwb_BookingID = " & Me.jdwbBookingID
                CurrentDb.Execute strSQL, dbFailOnError
                
                Me.List_Bookings.Requery
                Me.List_Bookings = Me.List_Bookings.ItemData(0)
                Call List_Bookings_AfterUpdate
            
                'Update Global variables for Bookings, Participants, Observers & Reserves
                
                'strESPrts = Str(Val(strESPrts) + lngChgPrts)
                'strESBkgs = Str(Val(strESBkgs) + lngChgBkgs)
                'strESRsvs = Str(Val(strESRsvs) + lngChgRsvs)

                'Above is in progress at the moment

                'Update event stats summary - increase/decrease Participants
            
                Forms!fJDWBookings.txtESBookings.Requery
                Forms!fJDWBookings.txtESParticipants.Requery
                Forms!fJDWBookings.txtESReserves.Requery
            
                'Update Events form finance summary
                
                Call UpdateEventsFinancials
                
                'Update Events form event stats summary
                
                Call UpdateEventsStats
                                                 
            End If
            
            GoTo Exit_CancelBookingButtonB_Click
    
        End If

    End If
    
Exit_CancelBookingButtonB_Click:
    
    'Re-eable other command buttons
    
    Me.BBOpsButtonB.Enabled = True
    Me.PartsButtonB.Enabled = True
    Me.ReportsButtonB.Enabled = True
    Me.CancelButtonB.Enabled = True
    Me.SaveButtonB.Enabled = True
    Me.CloseButtonB.Enabled = True
    Me.PaymentsButtonB.Enabled = True
    
    If Me.CancelBookingButtonB.Visible = False Then Me.CancelBookingButtonB.Enabled = True
    
    Forms!fJDWEvents!FindButtonE.Enabled = True
    
    Exit Sub

Err_CancelBookingButtonB_Click:
    MsgBox Err.Description
    Resume Exit_CancelBookingButtonB_Click
    
End Sub

Open in new window

Dale Fye

1. If you are trying to close the form at the point where you did the list requery, then the references to "Me" later in the code would cause the error you are getting. Since you have more code following that segment, you should remove the Else and Docmd.Close portion of my previous comment and move those to the last line of the Exit portion of your code, just before Exit Sub.

If me.list_Bookings.listcount = 1 then Docmd.close acform, me.name

2. With your code in place I also get the error "No current record."

Are you displaying column headers in your listbox? If so you will need to change that to read:

if me.list_Bookings.listCount > 1 then
Me.List_Bookings = Me.List_Bookings.ItemData(0)
Call List_Bookings_AfterUpdate
end sub

Just a couple of other comments.

1. I would get rid of lines 30-36 and that overall If/Else construct. If you put those lines right after your last Dim, you could do it like:

'If already cancelled, exit the sub
If Me.jdwbStatus = "CANX" Then
stOK = MsgBox("This Booking is already CANX!", 64, "Booking information")
Exit Sub
End If

2. I would move your button code to a subroutine (I have a Sub Buttons() in almost every form), and pass it the parameter (bEnabled) as either a true or false value. This just reduces the amount of code.

3. If you change the vbCancel in message box call (line 50) to vbOK, then you can get rid of line 52, since dropping out of this IF/Then construct takes you directly to the Exit part of the procedure. When you use the msgbox function call, lines 45, 58 I would use the vbconstants instead of 49 and 64 as most people don't know the constant value so 49 would be vbExclamation + vbOkCancel; and 64 would just be vbExclamation. Much easier to read.

MikeDTE

ASKER

Fyed

Thanks for your comments

When I put your suggesed code in place I put it well below any of the Me. references i.e. it was in place of the code that is in Line 146 to 148. Lines 184 to 190 were not being run I had an Exit Sub after your code - I did also rem out to the Me. code at the bottom of the proc to ensure that they were not causing the issue.

I am not dispalying column headers.

Can't get teh form to Close without this error. If I use the Close button it works fine. BTW the Close button code is "DoCmd.Close" - weird or what?

Thanks for the other comments:

- I can't see a huge advantage between your check for CANX and mine - it works either way
- Not used Sub Buttons - I'll investigate but in a future release of this app
- I have over 10,000 lines of code in this app and msgbox features a lot so I'm keeping my 64's and 49's - I know what they are and they work. Never used vbconstants. Maybe I'll look at this in a later release.

I am bug solving and have only 7 days before it's due for release so I think I'll concentrate on what is a problem rather than fine-tuning what works.

Dale Fye

Mike,

With that many lines of code, I know I'm not talking to a newbie, unfortunately, you never know unless someone identifies their experience level, or has answered some EE questions.

Only thing that I can think of is that one of these other procedures (List_Bookings_AfterUpdate, UpdateEventsFinancials, UpdateEventsStats) is looking at the listbox and when it doesn't find a value, is causing one of those errors.

Have you tried it with just this syntax, and stepped through the code?

if me.list_Bookings.listCount >0 then
Me.List_Bookings = Me.List_Bookings.ItemData(0)
Call List_Bookings_AfterUpdate
end sub

What is in the List_Bookings_AfterUpdate procedure?

MikeDTE

ASKER

Fyed

I have been developing part-time for about 10 years and my experience is really with VB6. Never took it further into .Net. My work has been driven by my ability to earn - so I get-by by doing something of everything - hardware support, networking, PC security and a bit of development work if I can get it. I have never taken Access seriously until now - my VB6 + full MS SQL is where my experience is. VBA is similar in structure but there are too many difference to make it easy for me. The current project has to be Access - it's a "what the client wants the client gets" scenario i.e. he's paying the bill!

List_Bookings_After update is about 850 lines of code - some needs moving into sub routines but again I will need to do that later.

I am getting around the problem - I have found that when I delete a last remaining Booking my existing code with your code snips in place basically works apart from some Invalid use of Null errors - Nz() will sork these out and I have an Application-defined or Object-defined error that I am running-down now (between phone calls from clients with hardware and malware problems and chatting with you).

I think you've earned you 500 points - without that first part of the problem resolved I could see no way forward and now I can with a bit more work. Oh there goes the phone again ...

MikeDTE

ASKER

Also thanks to Capricorn1 for the suggestion but Fyed gott it right and his code worked without modification for part one of my question.