Solved

using drop down from ms access to get a crystal report

Posted on 2006-10-20
17
321 Views
Last Modified: 2012-06-27
hi experts,
i have a form in ms access that has a drop down menu(account_id). i want to be able to select one account_id from the drop down and it shud open up a crystal report based on that id. i know i have to make a parameter account_id in the report which i already. im just not sure how integrate it with access. Here is my existing code which just opens a whole report when i click on a button. can plz someone give me the code. thanks.

Option Compare Database
Public crxapplication As New CRAXDRT.Application
Public crxreport As CRAXDRT.Report

Private Sub Form_Load()

    'Maximizes the form
    DoCmd.Maximize
    Me!CrystalActiveXReportViewer1.Top = 600
    Me!CrystalActiveXReportViewer1.Left = 800
    Me!CrystalActiveXReportViewer1.Height = 8000
    Me!CrystalActiveXReportViewer1.Width = 12000
Set crxreport = crxapplication.OpenReport("C:\report1.rpt")
CrystalActiveXReportViewer1.ReportSource = crxreport
CrystalActiveXReportViewer1.ViewReport
CrystalActiveXReportViewer1.Zoom (100)

End Sub
0
Comment
Question by:zafridi
  • 9
  • 8
17 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 17774537
Try adding this line
if AccoutnId is a string

   crxreport.RecordSelectionFormula = "{AccountIdField} = '"  &  YourDropDown.Text  & "'"

If it is a number

   crxreport.RecordSelectionFormula = "{AccountIdField} = "  &  YourDropDown.Text

Change to use your field name and your drop down box

Put it just before the Report Source assignment

mlmcc
0
 

Author Comment

by:zafridi
ID: 17775479
sorry being a pain but can u elaborate on it a little more..im really new to this thing..my dropdown name is ( accountid ) and the parameter in crystal is ( ?account_id ) and its a string..how do i code it..thanks a lot for ur help
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 17776944
Try this

   crxreport.RecordSelectionFormula = "{?account_id} =  '" & accountid.Text & "'"

mlmcc
0
 

Author Comment

by:zafridi
ID: 17798468
I tried what u suggested but its giving me "object required" error. i added the line u suggested. thanks for ur help. i really need to get this thing done so please if u can  help me out i wud really appreciate. thanks

Option Compare Database
Option Explicit

Public crxapplication As New CRAXDRT.Application
Public crxreport As CRAXDRT.Report




Private Sub Form_Load()



    'Maximizes the form
    DoCmd.Maximize
    Me!CrystalActiveXReportViewer1.Top = 600
    Me!CrystalActiveXReportViewer1.Left = 800
    Me!CrystalActiveXReportViewer1.Height = 8000
    Me!CrystalActiveXReportViewer1.Width = 12000
   

 crxreport.RecordSelectionFormula = "{?account_parent} =  '" & DAccountParent.Text & "'"

Set crxreport = crxapplication.OpenReport("C:\schedule3b.rpt")
CrystalActiveXReportViewer1.ReportSource = crxreport
CrystalActiveXReportViewer1.ViewReport
CrystalActiveXReportViewer1.Zoom (100)

End Sub
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 17800408
Put the line after you initialize the report object.

Move it down after the SET statement

mlmcc
0
 

Author Comment

by:zafridi
ID: 17802987
i did that as well. its telling me the same thing "object required". the way i have it set up is tat i have a drop down i select one account and then i have a button viewreport which shud open the report for selected account..do i need to have some sort of reference to that button as well. thanks for baring with me..
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 17803134
Try this

Change
  crxreport.RecordSelectionFormula = "{?account_parent} =  '" & DAccountParent.Text & "'"


to

crxreport.RecordSelectionFormula = "{?account_parent} = 'xxx' "

where xxx is a known account parent

Do you get the report you expect?

mlmcc
0
 

Author Comment

by:zafridi
ID: 17803613
crxreport.RecordSelectionFormula = "{?account_parent} = '020701032006' " ...i tried this..but im not sure do i need to have a select expert pointing to the account_parent parameter..the problem is i dont want it to open the pop up window from crystal where it asks u for som value..In either case for some reason crystal is not taking in this value from VBA..im not getting any error but when i click on view report its asking me to enter the account number. im not sure what the remedy would be. thanks
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:zafridi
ID: 17805161
crxreport.ParameterFields.GetItemByName("account_parent").AddCurrentValue "020707271084"..... i tried this and it worked for me. my only problem now is that i wana be able to get a value from the drop down which i tried ur way but didnt work. i wana be able to select an account from the drop down and then click on a command button..u think there need to be some sort of reference between the command button and drop down. any suggestions. thanks for ur help..
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 17807068
Sorry about the confusion.  The line should have used your account field not the parameter.

Change
crxreport.ParameterFields.GetItemByName("account_parent").AddCurrentValue "020707271084".....

to

crxreport.ParameterFields.GetItemByName("account_parent").AddCurrentValue YourDropDown.Text

mlmcc
0
 

Author Comment

by:zafridi
ID: 17811378
I changed it to
crxreport.ParameterFields.GetItemByName("account_parent").AddCurrentValue DParentAccounts.Text
where DParentAccounts is my dropdown name..its give me error "object required". any suggestions..thanks
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 17811873
Is this code on the form with the dropdown?  If not you have to fully qualify the name.

Forms!MyForm!DParentAccounts.Text


mlmcc
0
 

Author Comment

by:zafridi
ID: 17811932
no its on the seperate form. the combo is on a separate form. thanks
0
 

Author Comment

by:zafridi
ID: 17812027
crxreport.ParameterFields.GetItemByName("account_sub").AddCurrentValue Forms!perac!DParentAccounts.Text

i modified it to the above..it just opens a blank viewer..no records on it. im not sure watelse to do with it.
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 17813018
Is the other form still open or do you close it when you click the report button?  If you close it leave it open until you have extracted the values you need.

mlmcc
0
 

Author Comment

by:zafridi
ID: 17829186
hey thanks for all ur help. Actually i figured it out the actual syntax that worked for me is the following.

crxreport.ParameterFields.GetItemByName("FieldName").AddCurrentValue Forms![FormName]![ComboBoxName].Value

Im sure there's probably other ways of doing it but this one worked for me.  thanks
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 17829396
Glad i could help

mlmcc
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now