Get ADODB.Recordset data to appear in an Access form Listbox

[Guest]

How do I get ADODB.Recordset data to appear in an Access form ListBox?

I have two tables "staff" and "members"
When a user opens the database they use my "login" form
which, given the correct information, sends staff the my "staff" form.
For the "staff" form to know which user had logged in i have used a
'Global veriable'; "OffsetPos" which is set during the login and can
be successfuly called from the "Staff" form. I need to bring up a list
using a ListBox controle in the "staff" form containing details from
the "members" table but only for members who have their "personal
staff" field set as the current loged-in staff member (the offsetPos
value.)

I can not seem to use the global veriable in an expresion in the query
criteria wizard. So i have succsesfuly used the forms code to open a
recordSet and use its filter function to generate the results i need.
HOW do I get the recordset data to appear in a form ListBox???

 

[Brendan Reynolds]

You can't access a variable directly from a query, but you can write a
function that returns the value of the variable and call the function in the
query ...

Public SomeVariable As String

Public Function GetSomeVariable() As String
GetSomeVariable = SomeVariable
End Function

SELECT GetSomeVariable AS Whatever, * FROM SomeTable

You can assign an ADODB recordset to the Recordset property of a list box as
in the following example. This is a relatively new feature of recent
versions of Access - I'm afraid I can't remember in exactly which version it
was first introduced. It might have been 2002 or it might have been 2003,
I've fairly sure it wasn't in 2000. Setting the CursorLocation to
adUseClient seems to be necessary.

Option Compare Database
Option Explicit

Private mrstEmployees As ADODB.Recordset

Private Sub Form_Close()

If Not mrstEmployees Is Nothing Then
If mrstEmployees.State <> adStateClosed Then
mrstEmployees.Close
End If
End If

End Sub

Private Sub Form_Load()

Set mrstEmployees = New ADODB.Recordset
With mrstEmployees
Set .ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.Source = "SELECT EmployeeID, LastName FROM Employees ORDER BY
LastName"
.Open
End With
Me.List0.ColumnCount = 2
Me.List0.ColumnWidths = "0;2.5"
Set Me.List0.Recordset = mrstEmployees

End Sub