asked on

using drop down from ms access to get a crystal report

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

Mike McCracken

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

zafridi

ASKER

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

Mike McCracken

Try this

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

mlmcc

zafridi

ASKER

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

Mike McCracken

Put the line after you initialize the report object.

Move it down after the SET statement

mlmcc

zafridi

ASKER

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..

Mike McCracken

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

zafridi

ASKER

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

zafridi

ASKER

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..

Mike McCracken

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

zafridi

ASKER

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

Mike McCracken

Is this code on the form with the dropdown? If not you have to fully qualify the name.

Forms!MyForm!DParentAccounts.Text

mlmcc

zafridi

ASKER

no its on the seperate form. the combo is on a separate form. thanks

zafridi

ASKER

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.

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

zafridi

ASKER

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

Mike McCracken

Glad i could help

mlmcc