Output vba\sql to subform when I initially wrote it for a listbox

[shabbaranks]

Hi guys,



Im in a bit of a position. I wrote some VBA which outputted the results of mysql to a listbox as per the below code:

    Case "Current User"
         MySQL = "SELECT TimesheetTable.sUser, TimesheetTable.Activity, TimesheetTable.Hours, TimesheetTable.Project, TimesheetTable.[Task Date], TimesheetTable.Description FROM TimesheetTable "
         MySQL = MySQL & "WHERE ((TimesheetTable.sUser)= '" & [Forms]![TotalHours_Frm]![CurrentUser] & "') AND ((TimesheetTable.[Task Date])>=[Forms]![TotalHours_Frm]![TotalHours_Fromtxtbox] AND "
         MySQL = MySQL & "(TimesheetTable.[Task Date])<=[Forms]![TotalHours_Frm]![TotalHours_Totxtbox]) AND ((TimesheetTable.Project) Like '*" & [Forms]![TotalHours_Frm]![ProjectRef_txtbox] & "*')"
            
            Me.TotalHrs_Listbox.RowSourceType = "table/query"
            Me.TotalHrs_Listbox.ColumnCount = "6"
            Me.TotalHrs_Listbox.ColumnWidths = "3.5cm;3.5cm;1.50cm;6.5cm;2.25cm;7.5cm;"
            Me.TotalHrs_Listbox.RowSource = MySQL
            
                    
       
    End Select

Is there an easy way to change the listbox to a subform and then carry on from where I left off? The reason being I am trying to tally a column not realising that the listbox wasnt the best option for this method.

Thanks in advance!!

[pbaldy]

Sure, you just set the RecordSource property of the subform instead of the RowSource property of the listbox. You'd have to create it. You could loop the listbox to sum up a column too.

[shabbaranks]

Hi,

I ws trying to get the sum of a column in the listbox but what ever I do, Access seems to think that the value is null as per below:

? me.TotalHrs_Listbox.Column(2)
Null
? me.TotalHrs_Listbox.Column(1)
Null
? me.TotalHrs_Listbox.Column(3)
Null

Where as you can see from this image http://imgur.com/rOgKG its not - there is data in the listbox. the code is here

            For counter = Me.TotalHrs_Listbox.Column(2).ListCount - 1 To 0 Step -1
            sum = sum + Me.TotalHrs_Listbox.ColumnCount
            Next counter
            Me.TotalHours_txtbox.Value = sum

Thanks

[pbaldy]

This is only adding selected items in a multi-select listbox, but see if how I'm referring to the value works for you:

  For Each varItem In ctl.ItemsSelected
    curTotal = curTotal + ctl.Column(4, varItem)
  Next varItem
  Me.txtTotalPayments = curTotal

[shabbaranks]

This ones working, there were a couple of issues. I had a combo which referenced different columns depending on selection. Ironed that out and used the code

            For counter = Me.TotalHrs_Listbox.ListCount - 1 To 0 Step -1
            sum = sum + Me.TotalHrs_Listbox.Column(2, counter)
            Next counter
            Me.TotalHours_txtbox.Value = FormatNumber(sum, 2)

And now everything is hunky dorey!

Thanks

[pbaldy]

Happy to help.