Solved

using drop down from ms access to get a crystal report

Posted on 2006-10-20
17
323 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

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
 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
display n/a in column 2 26
Null as value for Dynamic parameter in CR2008? 9 46
Field in crystal 2011 is being cut off. 9 33
Sum of multiple formulas 6 18
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…
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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

776 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