Populate Multi-Column listbox in excel with access data.

lpking2005

Board Regular
Joined
Mar 21, 2011
Messages
140
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! ;) )

Code:
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!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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:
Code:
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
 
Upvote 0
Thanks Kyle for the response! i must admit that looks alot better.

But im now getting an error on the following line:
Code:
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.
 
Upvote 0
Which fields do you actually want to display in the listbox?
 
Upvote 0
Are those the actual field names?
 
Upvote 0
Now i know why! :laugh:

Thanks!

i used this instead.

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

Thank you all!
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top