[RESOLVED] Opening (and printing) a Access report through VB6.0 with parametre

[veseo]

First of all, hi all Second, I'm quite a newbie, so I hope I'm not asking too simple questions. Now, lets get down to business

I'm using VB6.0 pro and microsoft access with access 97 file format. What I want is when I click the button "Print" to open the report, give it the parametre lying in text1.text and print it. So far, I've found some tuts around the internet but the best I've gotten to is be asked to manually enter the parametre and get printed the record whose parametre I've written. Here's the code for my Print button:

Set objaccess = CreateObject("Access.Application")
With objaccess
        
        .OpenCurrentDatabase filepath:="c:\db1.mdb"
        .DoCmd.OpenReport "queryname", acPreview, "[id] = " & text1.text


            .Visible = True

            '.DoCmd.Maximize 'maximizes the Report window

            '.DoCmd.Maximize 'maximizes Access window
End With

Where id is the name of my index in the access database and text1.text is the name of the control whose value I want to get. With this code, I get asked for the id and the report with the record with that id is printed. What I want is NOT to be asked to manually enter the id, but rather the id to be passed automatically from text1.text. Any help?

[RobDog888]

Welcome to the Forums

id is a bad choice for a field name as its a reserved word. Does id exist in your table's fields? Is it a number or text value?

[veseo]

id exists and it's "Autonumber" value. I made text1.text DataFormat: Number. I've tried with names other than "id" and no change.

[RobDog888]

Is your "queryname" the name of a report or a query? .OpenQuery doesnt take a where clause argument.

[veseo]

A report, using a query (to enter the id of the record to be printed).

[RobDog888]

Ok, then that is your issue. You are passing a where caluse in the filter argument position. Use this...

.DoCmd.OpenReport ReportName:="queryname", View:=acPreview, WhereCondition:="[id] = " & text1.Text

[veseo]

Now it asks to enter values for all the fields in the database and prints this values

[RobDog888]

Then something isnt right either in the query or ???
If you manually run the query do you get prompts?

[veseo]

Yes, I had some mistake (probably due to renaming and trying with name other than id) but its fixed now. When I run it manually and enter an existing value, I get the report with the corresponding record. But when I run it in VB, using your code, I still get the prompt It still prints the record I enter though, simply I can't seem to pass the application the value from the text1.text. I even tried passing it the recordset value (which later goes to text1) and it still doesn't work.

[RobDog888]

What does your original query look like?

[veseo]

Here are some screenshots, I think they'll speak better than a newbie (me ):
Query:
http://www.picvalley.net/v.php?p=u/169/42199_365.JPG

I think here everything is clear, the id field is the one that I need to pass the values to.

The prompt I get if I run the report manually:
http://www.picvalley.net/v.php?p=u/169/42201_370.JPG

If I enter here a value, the record is shown, no problems.

What I get when I run the app with the code (temp desing, don't laugh ):

http://www.picvalley.net/v.php?p=u/169/42203_376.JPG

When I select an item from the list, its details are shown in the text fields below and I want, when I click the button in the upper right corner (temp again!) to print a report with this record, without being asked for the id. You can see the prompt, though :/

Thanks for spending so much time for my problem, it's really appreciated

[RobDog888]

It looks like you already have a where clause for hte id field so when you open the report via code and pass the where clause you are doubling up and creating the prompt as far as I can tell from that screen shot.

Either you use ADO and manually code everything or you modify your original query to return all records. Then when you open the query/report and pass the where clause it wont prompt since you passed the only needed info.

[veseo]

Thanks alot! Removing the where clause from the access query solved it Thanks alot, man, you are a great guy!