How to generate a dynamic report via a form that involves 3 tables

[bigmax98]

The data comes from different tables via oracle ODBC. I can create a query bases on the users enter in the form.
Thanks
Trang
tngo@fisk.edu

 

[dhookom]

Is there a question here? Check the FAQ link in my signature for info on asking a question.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[bigmax98]

Duane,
I mean that 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 for me.
Thanks
Trang
tngo@fisk.edu

 

[dhookom]

You might not need VB. Do you have a report designed? If not, create one and select your query as the record source. You can then use the command button wizard to add a button to your form that opens the report in either mode.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[bigmax98]

Duane,

I only have Ms Access. What do you mean by create one?
(in Ms Access??) and would you please show me how to select my query as the record source. I am confused. I thought it is VB coding. Please don't laugh. Thanks.
Here is my query coding:
Set db = CurrentDb
db.QueryDefs.Delete ("Genesis") 'Clean out whatever in query Genesis before filling the data.
Set QDF = CurrentDb.CreateQueryDef("Genesis")
strqry = "Select spriden_last_name, spriden_first_name, spriden_id from spriden, rorstat, rcrapp1 where rorstat_pidm = spriden_pidm and spriden_pidm = rcrapp1_pidm and spriden_change_ind is null"
'strqry = strqry & " from spriden, rorstat"
'strqrq = strqry & " where rorstat_pidm = spriden_pidm and spriden_pidm = rcrapp1_pidm and spriden_change_ind is null and"
'strqry = strqry & " spriden_change_ind is null and"
strqry = strqry & " and rorstat_aidy_code = " & "'" & [Forms]![Tracking_Form]![Aidy_Code] & "' and rcrapp1_aidy_code = rorstat_aidy_code"
strqry = strqry & " and rorstat_tgrp_code = " & "'" & [Forms]![Tracking_Form]![Tracking_Code] & "' and rcrapp1_curr_rec_ind = 'Y' and rcrapp1_signed <> '" & signed & "' order by spriden_last_name, spriden_first_name"
'strqry = strqry & " and rcrapp1_signed = ' " & signed & "'" & " order by spriden_last_name, spriden_first_name"

QDF.SQL = strqry
QDF.Connect = cnnstr
QDF.ReturnsRecords = True

DoCmd.OpenQuery "Genesis", acViewNormal, acEdit

End Sub

 

[dhookom]

Your thread subject suggests "generate a dynamic report". Are you creating a report or not?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[bigmax98]

Yes, I need to create a report. But I want to create a report inside the form. I don't want to exit the form and go to Ms Access and create a report via report witzard. Is there a way do it inside the form? If you know, would you please share the code with me. Thanks
Trang

 

[dhookom]

Creating a report entirely from code is not an easy task. You can check Help on the functions CreateReport() and CreateReportControl(). I doubt you want to attempt this.

Take a look at the Query By Form "applet" available at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

This might help you create dynamic "reports" on the fly.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[bigmax98]

Duane,

I download your codes, but I had error when I try to open.
The error is like this:
AppName: msaccess.exe AppVer: 9.0.0.6620 ModName: msaccess.exe
ModVer: 9.0.0.6620 Offset: 0008885f
So I can't view your code and see your application.
Thanks
Trang

 

[dhookom]

I haven't heard of this issue with the query by form. You might want to open the application and a module. Then make sure that you have a reference set to the Microsoft DAO library.

Did you look at the CreateReport and CreateReportControl functions?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[bigmax98]

Duane,

I just print out the CreateReport() and CreateReportControl
I will look at and try. Thankyou for pointing to the direction. I can't open any module from your application or anything in your application at all. I just kept having error. I will try to down load again and see whether it is better this time. Once again thank you for
your help. It makes a big difference.
trang