Moving To Next List Row

[DMT Dave]

Hi Guy's is there an adjustment that can be made here ?



RecQty (this is list count) with no Headers so Row 0 is a record

If for example there are 3 rows, i want to be able to move down the list for the next record until the last count

in my code !StartDate = Format(Me.lstMS.Column(0, 0), "ddd-dd-mmm-yyyy")

If listcount is 3
!StartDate = Format(Me.lstMS.Column(0, 0), "ddd-dd-mmm-yyyy")
!StartDate = Format(Me.lstMS.Column(0, 1), "ddd-dd-mmm-yyyy")
!StartDate = Format(Me.lstMS.Column(0, 2), "ddd-dd-mmm-yyyy")

!MSNo = Me.lstMS.Column(3, 0)
!MSNo = Me.lstMS.Column(3, 1)
!MSNo = Me.lstMS.Column(3, 2)

Set rs = CurrentDb.OpenRecordset("Select * From tblDriverHours")With rs
For i = 1 To RecQty
.AddNew
!Driver = Me.cboDriver
!StartDate = Format(Me.lstMS.Column(0, 0), "ddd-dd-mmm-yyyy")
!MSNo = Me.lstMS.Column(3, 0)
.Update
Next i
End With

Hope this makes sense !!!

[Welshgasman]

You are not using i though?
I also would not store a formatted date?, that makes it a string? Just format it as required when needed.?

[Micron]

I agree with don't store a formatted date. If that becomes the decision I suppose a fix is no longer needed.

For i = 1 To RecQty

I don't see where you figure out RecQty - it's likely 1 or zero in that code so no loop anyway. I suggest ALWAYS verify your variable values to trouble shoot. Anyway, I'm not sure I understand the goal but for future consideration maybe want you'd want is
(Me.lstMS.Column(0, Me.ListCount), "ddd-dd-mmm-yyyy")

This sort of thing is subject to being screwed up by future design changes, so another reason maybe why to not do it. If you later decide to see the list headers and forget about this code, you end up with unexpected results.

[moke123]

If you trying to iterate through a listbox you need something like:

    Dim i As Integer


    For i = 0 To Me.lstMS.ListCount - 1


        !StartDate = Me.lstMS.Column(0, i)


        !MSNo = Me.lstMS.Column(3, i)


    Next i

[DMT Dave]

Hi Moke, thank you works superb

[Micron]

Doesn't that simply make MSNo = first list item 1 column value then list item 2 column value then list item 3 column value ... then list item n column value? Why not just use the nth list item column value?