Outputting from a form to a file or report

[Compuboy]

I'm using ACcess 2002.

I have a big list box in my form. When a button is clicked, I want to somehow scroll through the whole list box index while outputting all the information to a report or a file. Anyone?? Some code would be nice since I'm pretty lost in VB.

THANKS.

 

[RoyVidar]

There's plenty of code available here at Tek-Tips. Search is encouraged - also do take a look at faq181-2886 on how to get the best answers (also mentiones the search facility, perhaps #8 and #10 might be of interest?). I think the general idea is that the members of Tek-Tips assist when something doesnt' work as expected, but writing complete routines...

Roy-Vidar

 

[PaulF]

This code uses the Employees table from the Northwind Sample Database. The form contains a Listbox (Lst1) with all of the employees in it and its bound column is to the primary Key in the Employees table (EmployeeID). A report is created (rptEmpSQL) to show the desired fields, and its Record Source is a query named qryRpt. The query gets deleted and then rebuilt each time you run the code. This code Loops through the Listbox and captures those items selected and adds it as the criteria (Where condition) to the SQL statement for the query using by using the function IN().

Private Sub cmdRpt_Click()
Dim strSQL As String
Dim db As DAO.database, qdf As DAO.QueryDef
Set db = CurrentDb
Dim var
On Error Resume Next
'Delete the existing query qryRpt
DoCmd.DeleteObject acQuery, "qryRpt"
On Error GoTo errHandler
strSQL = "Select * From Employees Where employeeID In("
For Each var In Lst1.ItemsSelected
strSQL = strSQL & Lst1.ItemData(var) & ", "
Next var
strSQL = Left(strSQL, Len(strSQL) - 2) & ")"
Set qdf = db.CreateQueryDef("qryRpt", strSQL)
DoCmd.OpenReport "rptEmpSQL", acViewPreview
errExit:
Exit Sub
errHandler:
MsgBox Err.Number & " : " & Err.Description
Resume errExit
End Sub


HTH
PaulF

 

[Compuboy]

It has a problem with DAO.database.

It says "User-defined type not defined

 

[PHV]

When in VBE, menu Tools -> References ...
You have to choose Microsoft DAO 3.x Object Library.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244