Moving in a recordset

[Steff_DK]

I have a recordset that has a number of fields including days from 28 to 31 ...
I want to put the data in a ListView control but how do I loop through the changing number of days???
Here's what I got so far:

rs.MoveFirst
Do Until rs.EOF
Set lstItem = Form_frmvagter.MainList.ListItems.Add()
lstItem.Text = rs!Navn
lstItem.SubItems(1) = rs!Afd

For z = 1 To intNoOfDays
lstItem.SubItems(z) = rs!z 'Here comes trouble: rs!z
Next

rs.MoveNext 'next row
Loop

The rs!z in the For/Next loop should be replaced with number from 1 to (28/.../31) e.g. lstItem.SubItems(z) = rs!1 holds the value for day 1 and so on ...

 

[Steff_DK]

Okay - here's the mdb file.

I'm building a duty roster, but cant seem to get the recordset straight ...
When I paste the SQL into a query in design view it works fine

Try opening the form frmvagter ...

 

[MarkK]

Here are a few changes to consider...

 

[Steff_DK]

Hey Lagbolt - thanks for your tweaks!
Good stuff to learn from.

However I need the query to have a given name only represented once:
e.g. "J.S. Steffensen" only appears as row header once and then all the days from 1 to 30-ish to follow in that same row. Will post that question in the queries section though...

Thanks again,

Steff