?
Solved

using drop down from ms access to get a crystal report

Posted on 2006-10-20
17
Medium Priority
?
327 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
17 Comments
 
LVL 101

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 101

Expert Comment

by:mlmcc
ID: 17776944
Try this

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

mlmcc
0
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

 

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 101

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 101

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
 

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 101

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 101

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 101

Accepted Solution

by:
mlmcc earned 1000 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 101

Expert Comment

by:mlmcc
ID: 17829396
Glad i could help

mlmcc
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

765 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