Populating a List Box

[RB123]

Hi all,

I am trying to populate a list box ona form with data in a table. I am new to Access VBA and am not able to do it. Following is the snapshot of my code. Could somebody please look into it tell me where am I going wrong and wht shld I change. Any help would be sincerely appreciated.

Private Sub Form_Load()
    Dim strSQL As String
    Dim strcustomername As String
    Dim rstLoans As DAO.Recordset
    Dim dbase As DAO.Database
 
 'strcustomername = txtorderno.Value
    Set rstLoans = dbase.OpenRecordset("MASTERRECORD")
     'Set rstLoans = dbase.OpenRecordset("MASTERRECORD", dbOpenCDO)
    SecuritiesList.RowSource = ""
 
'strSQL = "SELECTtblorder.orderno,tblorder.ProductID1,tblorde  r. [Clothingtype1]"
'strSQL = strSQL & "FROM tblorder;"
 
strSQL = "SELECT * FROM [MASTERRECORD]"
 
    Me.SecuritiesList.RowSource = strSQL
End Sub

Thanks
RB

[Stewart Ross]

Hi. There is no need at all to define recordsets if you want to set the recordsource of a listbox in code. The only statement that matters is the one setting your rowsource - none of the recordsource or database lines have any relevance to this at all and can be removed with no penalty. Doing so on the code you provided and removing the commented out lines leaves just:

Private Sub Form_Load()
    Dim strSQL As String
    strSQL = "SELECT * FROM [MASTERRECORD]"    
    Me.SecuritiesList.RowSource = strSQL
End Sub

As it is just one very simple select statement this should provide to the listbox all fields from your masterrecord table in the order they are defined for that table, if this is indeed what you want to show in your listbox. However, what your listbox shows to users depends on the number of columns you have defined for that listbox, and their widths. You will need to check what you have specified for that listbox to make sure you will actually see the list of fields concerned. You won't see much if you have defined just 1 column of width 0...

If you make further changes to the recordsource of a control you may need to include a requery call to effect those changes after setting the rowsource property.

Me.SecuritiesList.Requery

-Stewart