Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report using critera entered list box (2000)

    I have a report which currently uses combo boxes to allow the user to select 3 fields which are used as criteria for the report. The 3 fields are Factory, Line and Date.

    I would like to select multiple lines (each factory can have a number of production lines) and run the report for each of the lines selected.

    I've changed the form used to make the selections to use a list box for the entry of the lines, allowing multiple lines to be selected, but don't know how to run the report using the selected criteria.

    I've used very little VBA todate - I guess this is the time when I will need it.

    Any one able to offer a simple guide to how I can carry out the above?

    John

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report using critera entered list box (2000)

    You will need to build a criteria string or where clause for your report while looping through the items selected collection of your listbox. At the end you should have something that resembles "[factory] = "SomeID" AND [line] = "LineID01" AND [date] = #SomeDate# OR [factory] = "SomeID" AND [line] = "LineID02" AND [date] = #SomeDate# OR [factory] = "SomeID" AND [line] = "LineID03" AND [date] = #SomeDate#;" You can then use this string in the where argument of the OpenReport method to filter the records displayed in your report. This is just an example to get you started in the right direction. Hope it helps!

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report using critera entered list box (2000)

    Thanks for getting back so quickly.
    I understand the idea of building a criteria line, but not the "how to".

    I don't know how to reference the entries in the list box to see if they have been selected or not.

    There is/are a variable number of lines per factory, so I can't reference them as line01, line02, line03 etc

    I was expecting to have to do something like the following:

    start at the top of the line list box
    if not the end of the list
    if the line has been selected
    run report using the factory, date and this entry for line
    endif
    move down the list
    endif

    I know the syntax of the above isn't right, but it's the thought that counts

    Anyone give better guidelines for what it should be?

    John

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report using critera entered list box (2000)

    If you look at the properties and methods of the listbox object in the online help you should be able to figure this out. Specifically look at the ItmesSelected collection and the ItemData property. With these two you should be able to loop through the collection using a For Each...Next statement and return the selected item with the ItemData property. Here is an example from the online help in Access97 (my 2002 machine is offline at the moment):

    Sub BoundData()
    Dim frm As Form, ctl As Control
    Dim varItm As Variant

    Set frm = Forms!Contacts
    Set ctl = frm!Names
    For Each varItm In ctl.ItemsSelected
    Debug.Print ctl.ItemData(varItm)
    Next varItm
    End Sub

    Replace the object names with the names of your form and listbox. Then, instead of debug.print, concatenate the selected values to build your where string.

Posting Permissions

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