Hi All, i'm trying to run a repo

[Frank72]

Hi All,

i'm trying to run a report from a button , the query the report is based on i want altered by a selected listbox(2 in fact) can i use the where condition in the macro to achieve this?, i've tried already and its giving me a blank report and just wanted to know if it was possible

also how would i open a query to manipulate it in code ie instead of dbOpenTable is there a dbOpenQuery equivelant?

thanks a million

frank

 

[Klopper]

Frank,

I don't know if you have done this already, but the syntax for opening a report and restricting its records to those specified by the value of a control on a form is available in MS Access Help files:
[fieldname] = Forms![formname]![controlname on form]

But, as you wish to filter on more than one field, I would use VBA to redefine the SQL propery of the query (based upon the values in the list boxes) before running the report:

Example procedure using MS Access 97 DAO:

Sub cmdRun_Report_Click()

Dim db As DATABASE
Dim str As String
Set db = CurrentDb

str = "SELECT * FROM table WHERE table1.field1 = '" & listbox1.Value & "' AND table1.field2 = '" & listbox2.Value & "'"

db.QueryDefs!Query1.SQL = "SELECT * FROM ltblAgentEntity"

DoCmd.OpenReport "report1"
db.Close
End Sub

 

[JoaoTL]

Hi, or try like this

DoCmd.OpenReport("YourReport&quot, ,"YourQuery"

Best Regards

---
JoaoTL
mail@jtl.co.pt
My MS Access Site:

http://www.jtl.co.pt