Populate Multi-Column listbox in excel with access data.

[lpking2005]

Hi all,

I am in need of some help...

I have a listbox on a excel userform which gets populated from a table in access via a selected option in a combobox.
In its current state my code detects how many table rows and pulls ALL of the table data into the listbox.

What i really want, is to pull all the data into the listbox but only from certain columns in the table. (some data is not needed)
One of the columns is populated with a date, so i want the data in the listbox to be sorted by the latest date also.

Here is my current code: (please excuse the terrible coding! )

Option Explicit

' The location of the data files
'Const DATA_FLDR As String = ":\Production\PROD MOULDING\Paul\CURRENT PROJECTS\Digital Line Board\"
Const DATA_FLDR As String = ":\Users\lpkin_000\Dropbox\Line Board Project\"
'Const DATA_FLDR As String = ":\Warehouse Production\Data\"

'===== Data file names
Const MLD_FLAG As String = "Mouldings Order Log.accdb"
'
_________________________________________________________________________________________________________________________


Sub SearchJobDatabase()


'//Reads from the Order Log Database
'//Inputs the orders into the correct line ranges in order of priority
'BOF = Beginning of Recordset
'EOF = End of Recordset




Dim cnt As ADODB.Connection, _
    rst As ADODB.Recordset, _
 _
    stCon As String, _
    stDB As String, _
    sSql As String, _
 _
    sRow As Integer, _
    sCol As Integer, _
    i As Integer, _
 _
    rng As Range, _
    c As Range, _
 _
    ws As Worksheet, _
    cLine As Integer


Dim NoOfRecords As Long




On Error GoTo eHandler




'Initiate the ADO COM objects
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset
 
 
'Path and name of the database
    'stDB = GetDriveLetter & DATA_FLDR & MLD_FLAG
    stDB = "C" & DATA_FLDR & MLD_FLAG '******************************* overide
    
'return selected line from userform
    cLine = frm_SearchJobs.Line_Box.Value
    


'Create the connection string.
stCon = "Provider=Microsoft.Ace.OLEDB.12.0; Persist Security Info = False;" & _
                   "Data Source=" & stDB & ";"


'Open the connection
cnt.Open stCon


            
            'Open the table and sort by Date
            rst.CursorLocation = adUseClient
            rst.Sort = "[Date]"
            




            sSql = "SELECT * FROM " & cLine


            
            rst.Open sSql, cnt, 1, 3
            


            'Determine the number of records in the recordset
                With rst
                  .MoveLast
                  NoOfRecords = .RecordCount
                  .MoveFirst
                End With
              
              
            If Not (rst.BOF Or rst.EOF) Then
                
                    'Set the number of ListBox columns = number of fields in the recordset
                    frm_SearchJobs.ListBox1.ColumnCount = rst.Fields.Count


                    'Load the listbox with the retrieved records
                    frm_SearchJobs.ListBox1.Column = rst.GetRows(NoOfRecords)
                     
            End If
            
            
            
            
            
            rst.Close
    
cnt.Close
    


eHandler:




'Close the connections
On Error Resume Next




    'Tidy up by releasing the variables
    Set cnt = Nothing
    Set rst = Nothing


On Error GoTo 0


End Sub

Thanks for any help given!

 

[Kyle123]

You just need to learn a bit of SQL I find it somewhat odd that you have numbers for table names, but hey ho - I've tidied a little and removed redundant code. It's also not a good idea to have field names that are reserved words in access:

Option Explicit


' The location of the data files
'Const DATA_FLDR As String = ":\Production\PROD MOULDING\Paul\CURRENT PROJECTS\Digital Line Board\"
Const DATA_FLDR As String = ":\Users\lpkin_000\Dropbox\Line Board Project\"
'Const DATA_FLDR As String = ":\Warehouse Production\Data\"


'===== Data file names
Const MLD_FLAG As String = "Mouldings Order Log.accdb"
'
_________________________________________________________________________________________________________________________




Sub SearchJobDatabase()




'//Reads from the Order Log Database
'//Inputs the orders into the correct line ranges in order of priority
'BOF = Beginning of Recordset
'EOF = End of Recordset
    
    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    
    Dim stDB As String
    Dim sSql As String
    
    Dim cLine As Integer




    'Initiate the ADO COM objects
    Set cnt = New ADODB.Connection
    Set rst = New ADODB.Recordset
 
    
    'Path and name of the database
    'stDB = GetDriveLetter & DATA_FLDR & MLD_FLAG
    stDB = "C" & DATA_FLDR & MLD_FLAG '******************************* overide
    
    'return selected line from userform
    cLine = frm_SearchJobs.Line_Box.Value
    




    'Create & open the connection.
    cnt.Open "Provider=Microsoft.Ace.OLEDB.12.0; Persist Security Info = False;" & _
                       "Data Source=" & stDB & ";"




    'Open the table and Order by Date
    sSql = "SELECT Field1, Field2, Field3 FROM " & cLine & " ORDER BY [Date] DESC"
    rst.Open sSql, cnt, 1, 3
                  
      
    If Not (rst.BOF Or rst.EOF) Then
            'Set the number of ListBox columns = number of fields in the recordset
            frm_SearchJobs.ListBox1.ColumnCount = rst.Fields.Count
            'Load the listbox with the retrieved records
            frm_SearchJobs.ListBox1.Column = rst.GetRows()
    End If
    
    rst.Close
    cnt.Close
    


End Sub

 

[lpking2005]

Thanks Kyle for the response! i must admit that looks alot better.

But im now getting an error on the following line:

rst.Open sSql, cnt, 1, 3

The error message is: "No value given for one or more required parameters"

Im having a look around google to find out what this is but not alot of luck yet.

Thanks again.

 

[Norie]

Which fields do you actually want to display in the listbox?

 

[lpking2005]

Fields 2, 3, 4 & 5.

 

[Norie]

Are those the actual field names?

 

[lpking2005]

Now i know why!

Thanks!

i used this instead.

sSql = "SELECT Date, Time, [Order Number], [TO Number], Quantity FROM " & cLine & " ORDER BY [Date] DESC"

Thank you all!

 

[Kyle123]

Wrap your Date field in square parenthesis or you're asking for trouble

 

[Norie]

And the Time field.

 

[lpking2005]

ok, Thanks again! im learning