Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to generate a dynamic report via a form that involves 3 tables
- Thread starter bigmax98
- Start date
- Thread starter
- #3
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
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
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.
Duane
MS Access MVP
Find out how to get great answers faq219-2884.
- Thread starter
- #5
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
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
- Thread starter
- #7
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
Trang
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 This might help you create dynamic "reports" on the fly.
Duane
MS Access MVP
Find out how to get great answers faq219-2884.
Take a look at the Query By Form "applet" available at This might help you create dynamic "reports" on the fly.
Duane
MS Access MVP
Find out how to get great answers faq219-2884.
- Thread starter
- #9
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
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
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.
Did you look at the CreateReport and CreateReportControl functions?
Duane
MS Access MVP
Find out how to get great answers faq219-2884.
- Thread starter
- #11
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
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
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question