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! )
Thanks for any help given!
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!