Listbox items selected visual but not via code

[Access_guy49]

Hello all!
I'm running Access 2003, and I have a List box with "extended" multi select enabled.

My code:

Dim i As Integer
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    With Me.List26
        For i = 0 To .ListCount - 1
            If .Selected(i) = True Then
                AddtoBooking Me.RentalID, .ItemData(i)
            End If
        Next i
        .Requery
        Me.List_CurrentOrder.Requery
    End With

Pretty Simple right?
Well the problem is, when I select my list items in the form, everything looks fine. Then I click the button to add those items to another list which fires this code. The problem is the if statement... it cycles through and doesn't ever register a true event in the if...
****HOWEVER****
The code after this code, unselects all the items, so the box looks unselected, if I then select the items AGAIN, and click the button all works well!.... anyone have any idea what might be happening?
Here is the entire code for the button

    Dim i As Integer
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    With Me.List26
        For i = 0 To .ListCount - 1
            If .Selected(i) = True Then
                AddtoBooking Me.RentalID, .ItemData(i)
            End If
        Next i
        .Requery
        Me.List_CurrentOrder.Requery
    End With
    
    i = 0 'set i to zero for next loop
    
    'This loop unselects all the selected values in the listbox on the right
    For i = 0 To Me.List26.ListCount - 1
        If List26.Selected(i) = True Then
            List26.Selected(i) = False
        End If
    Next
    
    
    Me.List_CurrentOrder.Requery
    Me.List26.Requery

Thanks for the help

 

[vbaInet]

Use the ItemsSelected property of the Listbox, then ItemData to retrieve the value. It may also be that your form is experiencing some sort of corruption. Are you highlighting the records in code or manually?

 

[Access_guy49]

Thanks for the reply VBAInet.
I am selecting the values in the listbox manually.

I'll look into the ItemsSelected Property and report back. (fingers crossed!)

 

[vbaInet]

If you're doing it manually then it shouldn't be a problem.

This link should get you started:

http://msdn.microsoft.com/en-us/library/aa196172(v=office.11).aspx

 

[Access_guy49]

Ok. On the Plus side, I learned a new way to reference the selected items in a listbox! (big win)
On the downside, It's doing the same thing....

 

[Access_guy49]

Access is just messin with me now....
If i change the listbox property to "simple" multiselection it works peachy keen!
......AHHHHHH

 

[vbaInet]

Access is just messin with me now....

It must be

Let's have a look at your db.

 

[Access_guy49]

The Front End should be relativly easy to follow.
Just a couple notes...
Start By opening the Main form.
Then go to Check availability,
Select A date and a return date then click on the "Book 'em Dano" (Hawaii 5-0 reference....) button.
It will ask you for a customer name, just be sure to put a full name in the input box
"First Last" It needs both first and last, with a space. Then fill out the information, then proceed to the order form... the order form is where the listboxes are, and this is where i'm having the problems.
I have it currently set to Multi-select Simple, so that it works, just so that when you run through it, you will see it all work. You'll have to change Multiselect property of the inventory list box in order to get the bug to happen. and again, it only happens on the FIRST time you try to add snowshoes to the order.
Thanks for checking this out for me! i'm going CRAZY

 

[vbaInet]

ItemsSelected is working but it appears the append process isn't taking effect. I've changed a few things in your db and now you have to do it twice to take effect.

It appears that the cause is in connection with corruption. I will advise that you create a new database shell and import into that. Do this for both BE and FE. Also, you must compile your code, compact & repair. I think your code has never been compiled. I wrote a few notes so see attached.

 

[Access_guy49]

Sorry for the delayed resposne, I've been sick.

Question

    With Me.List26
        For Each varItm In .ItemsSelected
            db.Execute "INSERT INTO Link_RentalShoes (RentalID, ShoeID) " & _
                       "VALUES (" & Me.txtRentalID & ", " & .ItemData(varItm) & ");"
'            DoEvents    ' <-- It seems to require a little delay
        Next varItm

Should the DoEvents be commented out????

 

[vbaInet]

Welcome back!

It should remain. As mentioned, you need to create a new shell and compile your code. That may get things working properly.

 

[Access_guy49]

Yep, I created a New shell, and imported everything as you suggested. And i'm Compiled my code, I was missing some "dim I as integer" lines. So that was nice to get that cleared up.

Is there an advantage to using your Execute SQL statement over my Subroutine that I call and pass too????

 

[Access_guy49]

Oh, and so far is seems to be working. thanks!

 

[vbaInet]

Is there an advantage to using your Execute SQL statement over my Subroutine that I call and pass too????

I can't quite remember what you did on your old db.

 

[Access_guy49]

I used a Subroutine in which i passed the parameters and made the record through a DAO recordset.

 

[vbaInet]

You were looping for as many records that needed updating right? Performing an append either via the Execute command or just running an append query will always be faster than looping through a recordset.

 

[Access_guy49]

Thanks for all the help,
I tried to add to your reputation, but you help me too much. lol

 

[vbaInet]

Thanks for all the help,
I tried to add to your reputation, but you help me too much. lol

Happy to help.

Maybe I should help you less Just kidding!