Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

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
0
zafridi
Asked:
zafridi
  • 9
  • 8
1 Solution
 
mlmccCommented:
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
 
zafridiAuthor Commented:
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
 
mlmccCommented:
Try this

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

mlmcc
0
Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

 
zafridiAuthor Commented:
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
 
mlmccCommented:
Put the line after you initialize the report object.

Move it down after the SET statement

mlmcc
0
 
zafridiAuthor Commented:
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
 
mlmccCommented:
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
 
zafridiAuthor Commented:
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
 
zafridiAuthor Commented:
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
 
mlmccCommented:
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
 
zafridiAuthor Commented:
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
 
mlmccCommented:
Is this code on the form with the dropdown?  If not you have to fully qualify the name.

Forms!MyForm!DParentAccounts.Text


mlmcc
0
 
zafridiAuthor Commented:
no its on the seperate form. the combo is on a separate form. thanks
0
 
zafridiAuthor Commented:
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
 
mlmccCommented:
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
 
zafridiAuthor Commented:
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
 
mlmccCommented:
Glad i could help

mlmcc
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now