Fill an Unbound Listbox

[Wayne]

MS Access 2000

In a Form, code like this will fill an unbound list box
object when using linked tables:

strSQL = "SELECT tblWOAcc.WorkOrderID,
tblWOAcc.AccessoryDescription
FROM tblWOAcc
WHERE (tblWOAcc.WorkOrderID=" & Me.txtWorkOrderID & " AND
(Not (tblWOAcc.CategoryID) Is Null And
(tblWOAcc.CategoryID)<>1"
Me.lstvwAcc.RowSource = strSQL

How can I fill the same list box object if I connect to
the tables using a global connection object (no linked
tables)? The object's name could be gcnnWOData. I know how
to fill a textbox or checkbox object using linked tables.

Thanks!

[Sandra Daigle]

You could probably use a callback function for this. To use a callback
function basically all you do is change the RowSourceType property of the
listbox to
the name of the callback function that will fill the list. This tells Access
to use the function for obtaining the list items instead of a table, query
or value list. If you copy the following functions into the the form's class
module then you would set the RowSourceType to the name of the callback
function. In order for Access to use the callback function, it must have
specific parameters and handle specific conditions - for more information on
the callback query itself see the help for the RowSourceType and look for
the link to the section on 'specific function code arguements'.

Here's a link to another example of a callback function
http://www.mvps.org/access/forms/frm0049.htm

Here's some sample code for a callback function that uses DAO to fill the
array which is the datastore for the callback.

Function ListCustomers(fld As Control, _
id As Variant, _
row As Variant, _
col As Variant, _
code As Variant) As Variant
Static vardata() As Variant
Static Entries As Integer
Dim ReturnVal As Variant
ReturnVal = Null
Select Case code
Case acLBInitialize ' Initialize.
Entries = GetCustomers(vardata)
ReturnVal = Entries > 0
Case acLBOpen ' Open.
' Generate unique ID for control.
ReturnVal = Timer
Case acLBGetRowCount ' Get number of rows.
ReturnVal = Entries
Case acLBGetColumnCount ' Get number of columns.
ReturnVal = Me.lstCustomers.ColumnCount
Case acLBGetColumnWidth ' Column width.
' -1 forces use of default width.
ReturnVal = -1
Case acLBGetValue ' Get data.
ReturnVal = vardata(row, col)
Case acLBEnd ' End.
Erase vardata
End Select
ListQueries = ReturnVal
End Function

Function GetCustomers(vardata() As Variant) As Integer
Dim db As DAO.Database
Dim intI As Integer
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("Select custid, " _
& "Custname, Address1, City, " _
& "State, Zip from tblCustomer")

With rst
If Not (.EOF And .BOF) Then
.MoveLast
.MoveFirst
ReDim vardata(.RecordCount, 7)
Do Until .EOF
vardata(intI, 0) = !Custid
vardata(intI, 1) = !Custname
vardata(intI, 2) = !Address1
vardata(intI, 3) = !City
vardata(intI, 3) = !State
vardata(intI, 3) = !Zip
intI = intI + 1
.MoveNext
Loop
End If
.Close
End With
Set db = Nothing
Set rst = Nothing
GetCustomers = intI
End Function

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

[Wayne]

Thanks Sandra! I am building an MS Access 2000 app in
which I want all the textbox objects, listbox objects,
combo box objects, etc. to be unbound. This helps a lot!

Wayne