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???
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
--
Brendan Reynolds
Access MVP
"david028707" <david...@discussions.microsoft.com> wrote in message
news:4AFED246-D5B7-4EAD...@microsoft.com...