Problems With list box

[mo2783]

Hi

I have the follwoing code which should populate a 2 Column listbox

Private Sub Form_Open(Cancel As Integer)

    strSQL = "SELECT tbl_Computer_Main_Applications.Computer_Main_Application_ID, tbl_Computer_Main_Applications.Computer_Main_App_Name, tbl_Computer_Main_Applications.Computer_Main_App_Version " & _
    "FROM tbl_Computer_Main_Applications;"
    
    Set rsOpened = OpenTable(strSQL)
    
    
    rsOpened.MoveFirst
    i = 0
    With Me.lstAppAvailable
        .Clear
        Do
                    
            .AddItem
        
            'Add the Item to the Listbox
            .list(i, 0) = rsOpened.Fields("Computer_Main_Application_ID")
            .list(i, 1) = rsOpened.Fields("Computer_Main_App_Name") & " " & rsOpened.Fields("Computer_Main_App_Version")

        

            i = i + 1
            rsOpened.MoveNext
        Loop Until rsOpened.EOF
    End With
   
    rsOpened.Close
    
End Sub

I get the following error "Compile error. method or data member not found" on .clear statement in the code.

Any idea whats wrong with the code?

The list box is created in MS Access 2003.

Regards

Mo

 

[Remou]

Access 2003 reference does not list Clear as a method for listboxes:

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

I think you must consider RemoveItem.

 

[mo2783]

Remou

I tried RemoveItem i get error "Argument not optional" if i delete RemoveItem then i get the same error on line which reads .AddItem

am i not setting up a reference to something?

thanks

Mo

 

[MajP]

That is not a method of a Acess Listbox in 2003. You can look in the object browser for all properties and methods. May exist in 2007. MSform controls and Access form controls are different. I believe that works in VB.

make a custom procedure to iterate the list and use the remove item method.

Note. In fact the AddItem/removeItem method did not exist in Access until 2003.

 

[MajP]

Look at the object browser for add and remove item. You are using them incorrectly. These methods use an argument.

 

[Remou]

Did you not have a chance to look at the link I posted? It includes examples. Following the RemoveItem link on the page gives:

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

 

[mo2783]

Thinking about it i dont really need to Remove items from list box as it populated when empty or do i?

But how do i populate a multi Column list box in Access 2003?

Is the code above any good?

Mo

 

[Remou]

You can use AddItem if you wish, but I prefer to set thhe RowSource to a table or query:

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

"SELECT tbl_Computer_Main_Applications.Computer_Main_Application_ID, tbl_Computer_Main_Applications.Computer_Main_App_Name, tbl_Computer_Main_Applications.Computer_Main_App_Version " & _
    "FROM tbl_Computer_Main_Applications;"

Me.lstAppAvailable.RowSource=strSQL

Or just set the rowsource in design view.

 

[PHV]

Anyway, use the Load event instead of the Open when you play with controls ...

The workaround for the lack of .Clear method:
.RowSource = ""

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mo2783]

Thanks for the Help everyone. I have it working with SQL, but what if i was going to populate the listbox with "values" (Row Source Type as Value) via code, how would i populate the listbox with 2 columns?


Mo

 

[Remou]

You can use semi-colons in the English versions of Access:

RowSource: 1; Here; 2; There

You must ensure that the column count is set to 2.