List box to Query-FIDSGAL

  • Thread starter Thread starter fidsgal
  • Start date Start date
F

fidsgal

I have a list box with 2-9 items listed. How can I set up my macro (I
don't know VB much)to operate as follows: Click on a specific item in
my list and a query will run, then return results to 3 separate text
boxes on the same form (same form as where the list box is found)?
 
learn vba ;-)
in the YourListBox_AfterUpdate event type:

'On Error Resume Next '--- uncomment for production
Dim QDef as DAO.QueryDef
Dim Db AS DAO.Database
Dim Rs AS DAO.Recordset
Dim Prm AS DAO.Parameter
Dim Fld AS DAO.Field

Set Db = Access.CurrentDB
'Set Qdef=Db.CreateQueryDef("")
'QDef.SQL = "SELECT A,B,C FROM D WHERE E=Forms!YourForm!YourListBox"
-- OR --
Set QDef = Db.QueryDefs("MyQuery") ' "same" SQL ... - only stored
' and now for the neat trick
For Each Parm in QDef.Parameters
Parm.Value = Access.Eval(Parm.Name)
Next

Set Rs = Qdef.Openrecordset()
If (Not rs Is Nothing) AND (Not Rs.EOF) Then
For Each Fld In Rs.Fields ' Requires same name for the controls as for
the fields (A,B,C)
Me.Controls(Fld.Name) = Fld.Value
Next
end if
rs.close : Set Rs = Nothing ' Clean up objects
set Qdef = nothing
Set Db = Nothing
'End Sub

HTH

Pieter
 
Back
Top