Create a dynamic reports from 3 different tables bases on users' entry

[bigmax98]

I can create a select query from 3 oracle tables via ODBC from a form (Ms Access 2000 with VBA) that the users enter the information. After I get the query, I would like to display the report on the screen without exit the form and go through Ms Access to generate the report via report wizard . The user either can get a hard copy or view it on the screen. But I don't know how to proceed to this step. I don't have formal VBA/Ms Access training at all. I just picked up by playing around. My boss will order VB book for me. I am still waiting for the book.
Thanks
Trang

 

[EastWind]

A way (not the only way) of doing it would be to create two buttons on your form: one for report-print preview and the other for report-print. If you have created the report beforehand, create a new button on the form from which you want the User to have these options. The form's button wizard will give you the option of defining the button as a Report Operations > Report Print or Report Print-Preview without having to mess with the VBA directly.

If you want to make the buttons more sophisticated or add a message box to the operations, you will need to do a bit of VBA programming. But, this will at least do th minimum task you currently require.

EastWind

 

[bigmax98]

EastWind,

I don't want to create a report beforehand. I would like to handle everything in the form. How do you proceed to the next step. I only know how to generate the query.
Would you share some codes if you already wrote them before. I never have any formal training. I am still waiting for my co-worker to order a VB book.
Thanks
trang

 

[EastWind]

I've not had to create a report from code. If you're using Access 2002, the good news is that you need not use DAO or ADO because forms and reports are Access objects and not data. What this means is that you can create/append a new report to the Access AllReports collection without needing to make a choice between ADO/DAO. The bad news is (1) I don't have example code, (2) it would take me too long at present to give you anything useful this way, and (3) you may have to wait until your co-worker gets an Access/VBA manual.

I realize that is no real help at all, but I didn't want to leave you hanging for an answer.

Why do you not want to pre-create a report? Is it because you want the user to have choice about which data is used? If that is the case, you can still create an unbound report, and then define its RecordSource per the user's choice of available options.

Good luck,


EastWind

 

[bigmax98]

Thanks for all the answer. I think I get the idea. I will try to implement it.
Trang

 

[mkov]

bigmax98,

Not sure if this is what you are intending to do or not, but on the form you can boxes for the user to input criteria into and then refer to the text boxes on the form in the query.

I do this a lot. I will have query and report already setup the way that I want it. Then the user chooses/inputs the criteria on the form and that way a single query and report are pretty versitle.