Create a select query in the database to contain just the data that you want
in the order that you want. Here is the code that I would use to populate a
listbox from a table/query in Access:
Private Sub UserForm_Initialize()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
' Open the database
Set db = OpenDatabase("D:\Access\ResidencesXP.mdb")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM Owners")
' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
' Set the number of Columns = number of Fields in recordset
ListBox1.ColumnCount = rs.Fields.Count
' Load the ListBox with the retrieved records
ListBox1.Column = rs.GetRows(NoOfRecords)
' Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub
--
Hope this helps.
Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.
Doug Robbins - Word MVP
"Jim" <joc[ at ]nospam.net> wrote in message
news:Xns9847E311C5B8EJoconnoraspsmnet[ at ]216.196.97.136...
[Quoted Text] > Hello,
> I have a userform which fills list/combo boxes from access databases. I
> have gotten this far with questions to this group and looking at the links
> like mvps.org. I have a few questions / more things that I would like to
> do but can't find the answers. If you could help .... I use Word 2003,
> Access 2003, Win XP pro sp2
>
> Here is a sample of the line that calls the sub to fill a box -
>
> FillList Me.txt_AddressFile.Value, "Select * FROM doctoraddresses WHERE
> Frequent = true and Inactive = False", Me.cb_ReferringAttending 'uses the
> address database to fill the doctors addresses (it is on one line in my
> form)
>
>
>
> here is the code that I call to fill each box, I tried with just a
> targetcontrol.list = rstdata but it didn't work right, this work for me.
>
> Private Sub FillList(aDataBaseFile As String, SelectString As String,
> TargetControl As Control)
> Dim wrkJet As Workspace
> Dim dbsDataBase As DAO.Database
> Dim rstData As DAO.Recordset
> Dim numfields, numrecords As Long
> Dim aDataBasePath As String
> Dim DataArray() As Variant
> Dim TargetText As String
> On Error GoTo errmsg
> TargetText = TargetControl.Text
> Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
> Set dbsDataBase = wrkJet.OpenDatabase(aDataBaseFile, True)
> Set rstData = dbsDataBase.OpenRecordset(SelectString, dbOpenDynaset)
> numfields = rstData.Fields.Count
> If TargetControl.ColumnCount < numfields Then 'make sure the box has
> enough columns
> TargetControl.ColumnCount = numfields
> End If
> With rstData 'fill rstdata with the database values
> Do While Not .EOF 'count the number of records
> numrecords = .RecordCount
> .MoveNext
> Loop
> rstData.MoveFirst
> ReDim DataArray(1 To numrecords, 0 To numfields - 1)
> For i = 1 To numrecords
> For j = 0 To numfields - 1
> DataArray(i, j) = rstData.Fields(j)
> Next j
> rstData.MoveNext
> Next i
> End With
> TargetControl.List() = DataArray
> 'TargetControl.ListIndex = 1
> 'TargetControl.Text = TargetText
> rstData.Close
> dbsDataBase.Close
> Exit Sub
> errmsg:
> MsgBox " the " & TargetControl.Name & " listbox could not be filled
> with " & aDataBaseFile
> End Sub
>
> here are my questions:
>
>
> 1. Any way to get just a few of the data fields instead of the whole row?
> I
> could not get it right
>
> 2. Any way to "ORDER BY" to sort the data like you can in access/SQL
>
> 3. I can get info to a table with a new row, but can I change data in an
> existing row ? Each row has a unique ID field so I can identify it if
> needed.
>
> Thanks, Jim
|