Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Multiselect for Report - Error (2002)

    Hi,

    I'm using code that I modified from anothe database that I have. I am trying to open a report to only include the the names that are chosen from the list. I'm getting an error that I don't know what to do with since the original that I copied this from was derived with a great deal of help from this forun and the area is still pretty vague to me. I don't know where to begin to correct this.

    Thanks,
    Leesha

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiselect for Report - Error (2002)

    Leesha,
    In your example there are a few errors in the code (if the database structure *is* what you're going to use)...I've highlighted references to database objects that don't exist or are misspelled:

    <pre>Private Sub cmdPrint_Click()



    Dim strSQL As String
    Dim Q As QueryDef, DB As Database
    Dim Criteria As String
    Dim ctl As Control
    Dim Itm As Variant

    ' Build a list of the selections.
    Set ctl = Me![lstAccount]
    For Each Itm In ctl.ItemsSelected
    If Len(Criteria) = 0 Then
    Criteria = ctl.ItemData(Itm)
    Else
    Criteria = Criteria & "," & ctl.ItemData(Itm)
    End If
    Next Itm
    If Len(Criteria) = 0 Then
    Itm = MsgBox("Please select one or more Accounts from the list.", 0, "No Selection Made")
    Exit Sub
    End If
    ' Modify the Query.
    Set DB = CurrentDb()
    Set Q = DB.QueryDefs("qryDe<span style="background-color: #FFFF00; color: #000000; font-weight: bold">n</span hi>ographics")
    Q.SQL = "Select * From tblReferralSource<span style="background-color: #FFFF00; color: #000000; font-weight: bold">Backup</span hi> Where [ID] In(" & Criteria & _
    ");"

    Q.Close

    DoCmd.OpenReport "rptDemographics<span style="background-color: #FFFF00; color: #000000; font-weight: bold">Needed</span hi>", acViewPreview

    End Sub
    </pre>


    After fixing those, it worked fine for me. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>Next time, could you tell us the error you receive?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiselect for Report - Error (2002)

    The code uses DAO, you must aet a reference to the DAO library. Do the following in the Visual Basic Editor:
    <UL><LI>Select Tools | References...
    <LI>Locate Microsoft DAO 3.6 Object Library and tick its check box.
    <LI>Click OK.[/list]Although it is not strictly necessary in this case, I always prefer to make DAO declarations explicit:

    Dim Q As DAO.QueryDef, DB As DAO.Database

    (This is in cmdPrint_Click)

    Notes: there is a typo in the line

    Set Q = DB.QueryDefs("qryDenographics")

    It should be qryDemographics. And there is no report named rptDemographicsNeeded in the database you attached, it is called rptDemographics. Also, there is no table named tblReferralSourceBackup, it is called tblReferralSource. But this may be due to stripping down the database for attaching.

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiselect for Report - Error (2002)

    <img src=/S/woops.gif border=0 alt=woops width=58 height=36>
    I didn't realize no DAO reference had been set <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>I just wiped my C drive...Leesha's database was the first Access Application I've opened since...

    I agree with the explicit DAO declarations, though I'm pretty sure YOU started me on that to begin with....
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiselect for Report - Error (2002)

    Thank you to both of you! I made the corrects and added the DAO library and the error message is gone!! I'll add that piece to my "cheat notes" so when I got to use this code again I'll remember to turn that on.

    Thanks!
    Leesha

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •