Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2006
    Posts
    60

    Unanswered: access error message from click event???

    Hello all - can anyone put me in the right direction re an error message I'm getting when I try to print a report in a listbox? The "Load Reports" button is fine, shows all the reports. It's when I click the "Preview Reports", button that I get the message. Here's the code for the button:

    DoCmd.OpenReport lstReports.ItemsSelected, View:=acPreview

    The error message says: "An expression you entered is the wrong datatype for one of the arguments".

    The debug option leads me right up to the DoCmd statement.

    I'm damned if I can figure this out..I thought ItemsSelected was the appropriate method here.

    If you want to reproduce this, you need to have a form with a listbox with it's recordtype set to "Value" in properties.

    Here's the code for the "Load" button, taken from a VB Sub courtesy of Alison Balter's Access book. It uses automation so I commented out the automated parts since I'm dealing with Access directly:

    [Code follows]

    Dim ws As Workspace
    Dim db As Database
    Dim doc As Document
    Dim cnt As Container
    'Point at the Jet Engine
    Set ws = DBEngine(0)
    'Set a Filter and Intial Directory for the
    'Common Dialog Control
    ' dlgCommon.Filter = "Databases (*.mdb)|*.mdb"
    ' dlgCommon.InitDir = App.path
    'Display the File Open Common Dialog
    ' dlgCommon.ShowOpen
    'Open a Database using the selected Access file
    Set db = CurrentDb
    'Look at the Reports collection
    Set cnt = db.Containers!Reports
    'Clear the List Box of previous entries
    'lstReports.clear
    'Loop through the collection of Reports
    'Add each report name to the List Box
    For Each doc In cnt.Documents
    lstReports.AddItem doc.Name
    Next doc
    [End of code]

    I'm using Access 2003 on WinXP.
    Anybody? Thanks in advance.

  2. #2
    Join Date
    Jul 2006
    Posts
    108
    im at school right now so i dont have a copy of access, but try putting ""'s around acpreview so that:
    Code:
    DoCmd.OpenReport lstReports.ItemsSelected, View:="acPreview"

  3. #3
    Join Date
    Sep 2006
    Posts
    60
    Thanks, but that just gives me a "type mismatch" error.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    jwalker343 was incorrect as you've found out. I, too, am away from my work amchine, but the problem, I believe is that you're using lstReports.ItemsSelected which is an integer (it returns the item index or position in the box, starting with zero as the frist item) and Access expects a form name, which is a string. I think you need to set up a variable, say strFormName, then assign a name to the variable depending on the integer returned by ItemsSelected using a structure like Case or something similar. Then:

    Code:
    DoCmd.OpenReport strFormName, acPreview
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Missinglinq
    jwalker343 was incorrect as you've found out. I, too, am away from my work amchine, but the problem, I believe is that you're using lstReports.ItemsSelected which is an integer (it returns the item index or position in the box, starting with zero as the frist item) and Access expects a form name, which is a string. I think you need to set up a variable, say strFormName, then assign a name to the variable depending on the integer returned by ItemsSelected using a structure like Case or something similar. Then:

    Code:
    DoCmd.OpenReport strFormName, acPreview
    Not quite. myListBox.ItemsSelected returns a collection of the selected items.

    If you have a simple listbox with no multi-select capabilities, then you need to use something like this:

    myList.Column(0, myList.ListIndex)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Sep 2006
    Posts
    60
    Thanks for your help, folks. Here's what's happened in the last half hour:

    I scoured the web looking for answers and found some interesting candidates on some other forums, but they all lots of code that really didn't WORK!

    But, I noticed one fellow's code that was very simple:

    He made a table of his report names and put in the following line in the AfterUpdate event:

    DoCmd.OpenReport Me.Combo20, acPreview

    Damn if it didn't work like a charm! So, I thought about it and tried it with a Listbox's Double-Click event:

    DoCmd.OpenReport Me.List22, acPreview

    Beautiful! And only ONE line of code!

    Try it. Beats those ugly buttons, I'll tell you...

Posting Permissions

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