Results 1 to 4 of 4
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Report Header Question (A2K SR1)

    In an nutshell, A form contains several multi select listboxes that drive a query and provide the results for a report.

    When the user opens the report, the report does not indicate the selections made in the multiselect listbox.

    Anyone know of a clever way to put the selected multiselect listbox parameters from the form into the header of the report so the users can remember the selections made?

    I tried to create a variable that would get the name of each parameter selected while looping through the recordset for the listbox and add it to itself and then write the variable to a textbox on the report but couldn't get the code to work. Any ideas.
    Regards,

    Gary
    (It's been a while!)

  2. #2
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Header Question (A2K SR1)

    Gary,

    Try placing the following code in the Report's Header Format Event

    Dim lngCount As Long
    Dim lngloop As Long
    Dim strTemp As String

    lngCount = Form_Form3.List0.ListCount

    For lngloop = 0 To lngCount - 1
    If Form_Form3.List0.Selected(lngloop) = True Then
    strTemp = strTemp & Form_Form3.List0.Column(1, lngloop) & ", "
    End If
    Next

    Me.Text0 = Left(strTemp, Len(strTemp) - 2)

    Where Form3 is your form name, List0 is the Listbox Name on the form and Text0 is the Textbox in the Report header.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Report Header Question (A2K SR1)

    Thanks Bryan.

    I will give this a try and see how it works. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Report Header Question (A2K SR1)

    Bryan,

    Thanks for the advice. I tried your suggestion however couldn't get it to work. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> However, it gave me an idea based on what I was originally trying to do. <img src=/S/smile.gif border=0 alt=smile width=15 height=15> The following code is launched from the form and extracts the data selected in the multiselect listbox for the report after pressing a command button.

    Private Sub Command64_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim stDocName As String
    Dim ctl As ListBox
    Dim varitem As Variant
    Dim strSql As String
    Dim SelMonth As String 'Store Months Selected from Multiselect Listbox
    Dim SelYear As String 'Store Years Selected from Multiselect Listbox
    Dim SelDepartment As String 'Store Departments Selected from Multiselect Listbox
    Dim SelLaborType As String 'Store Labor Types Selected from from Multiselect Listbox
    Dim SelEmployee As String 'Store Employees Selected from Multiselect Listbox
    Dim SelWBS As String 'Store WBS's Selected from Multiselect Listbox

    'Below Code gets all listbox selections and writes it to Table.Temp1
    'Data for each Listbox is stored in a column in Table.Temp1 Table.Temp 1 is then used to drive queries.

    DoCmd.SetWarnings WARNINGSOFF

    'Delete all prior selections from Temp1 table
    strSql = "Delete * from Temp1;"
    DoCmd.RunSQL strSql

    'List13 Stores results of selections on screen. Show no records when opening form.
    Me.List13.RowSource = ""

    'Begin obtaining data of selections made in each list box

    '1. LISTBOX SelectYear

    'Set ctl to the the listbox
    Set ctl = Me!SelectYear

    ' Exit if nothing in the listbox is selected
    If ctl.ItemsSelected.Count = 0 Then
    MsgBox "Please select one or more YEARS", vbCritical, "Select Year"
    Exit Sub
    End If

    'Set Recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Select[TEMP1].Year from [TEMP1]")

    'Loop through items in Listbox
    'Write Selections Made to Table.Temp1
    'Store Selections made in SelYear

    For Each varitem In ctl.ItemsSelected
    With rst
    .AddNew
    !YEAR = ctl.Column(0, varitem)
    .Update
    SelYear = SelYear & ctl.Column(0, varitem) & ", "
    End With
    Next varitem

    'Write Years Selected to invisible textbox on form to be written to report
    Me.StoreSelYear.Value = Left(SelYear, Len(SelYear) - 2)

    'End Processing for Listbox



    Thanks again for the help. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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