Do until Loop question

[Tammy F]

I have 2 fields in a table - a station and a use checkbox. This is only used
for set-up purposes... but I'd like the user to check boxes on the subform
and have a sentence show up on the main form in a field.

Example:
Station Use
Cheese x
Sauce
Chips x
Salsa

Sentence would look like: Cheese, Chips
My programming works, except I get an error because it can't go to specified
record when I am on the last record. For some reason - .movefirst and
..movenext don't want to work.

Here is my code. Thank you for any help! Tammy

Private Sub use_AfterUpdate()
Dim stationdesc As String
stationdesc = ""
Dim stationone As String

Dim dbs As DAO.Database
Dim rsZips As DAO.Recordset
Set dbs = CurrentDb
Set rsZips = dbs.OpenRecordset("tlist_station")
With rsZips
If .EOF = False And .BOF = False Then
'.MoveFirst
DoCmd.GoToRecord , , acFirst
Do While .EOF = False
stationone = IIf(Me!use = -1, Me!Station & ", ", "")
stationdesc = stationdesc & stationone
'.MoveNext
On Error GoTo Err_exitloop_Click
DoCmd.GoToRecord , , acNext

Loop
End If
End With

Exit_exitloop_Click:
rsZips.Close
Set rsZips = Nothing
dbs.Close
Set dbs = Nothing

stationlen = Len(stationdesc)

Forms!frm_menu_item_setup!Station = Left(stationdesc, stationlen - 2)
Exit Sub

Err_exitloop_Click:
MsgBox Err.Description
Resume Exit_exitloop_Click



rsZips.Close
Set rsZips = Nothing
dbs.Close
Set dbs = Nothing

stationlen = Len(stationdesc)

Forms!frm_menu_item_setup!Station = Left(stationdesc, stationlen - 2)
End Sub

 

[Jack Leach]

I'm not really sure why that wouldn't work. Personally I use the While/Wend
syntax myself rather than Do/Loop, though there's no real reason (that I know
of), it's just how I started and stayed... I've never had any problems with
something like this:

If rs.RecordCount <> 0 Then
rs.MoveFirst
While Not rs.EOF
'do your stuff here
rs.MoveNext
Wend
End If

Not really sure what the difference would be, but there maybe be something a
little wrong with your do/loop statement (but I wouldn't know really). The
above should work though...

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Marshall Barton]

I have 2 fields in a table - a station and a use checkbox. This is only used
for set-up purposes... but I'd like the user to check boxes on the subform
and have a sentence show up on the main form in a field.

Example:
Station Use
Cheese x
Sauce
Chips x
Salsa

Sentence would look like: Cheese, Chips
My programming works, except I get an error because it can't go to specified
record when I am on the last record. For some reason - .movefirst and
.movenext don't want to work.

Here is my code. Thank you for any help! Tammy

Private Sub use_AfterUpdate()
Dim stationdesc As String
stationdesc = ""
Dim stationone As String

Dim dbs As DAO.Database
Dim rsZips As DAO.Recordset
Set dbs = CurrentDb
Set rsZips = dbs.OpenRecordset("tlist_station")
With rsZips
If .EOF = False And .BOF = False Then
'.MoveFirst
DoCmd.GoToRecord , , acFirst
Do While .EOF = False
stationone = IIf(Me!use = -1, Me!Station & ", ", "")
stationdesc = stationdesc & stationone
'.MoveNext
On Error GoTo Err_exitloop_Click
DoCmd.GoToRecord , , acNext

Loop
End If
End With

Exit_exitloop_Click:
rsZips.Close
Set rsZips = Nothing
dbs.Close
Set dbs = Nothing

stationlen = Len(stationdesc)

Forms!frm_menu_item_setup!Station = Left(stationdesc, stationlen - 2)
Exit Sub

Err_exitloop_Click:
MsgBox Err.Description
Resume Exit_exitloop_Click



rsZips.Close
Set rsZips = Nothing
dbs.Close
Set dbs = Nothing

stationlen = Len(stationdesc)

Forms!frm_menu_item_setup!Station = Left(stationdesc, stationlen - 2)
End Sub

That code uses the form's recodset to control a loop through
values on the form, but then has Docmd ibstad of MoveNext,
With all that confusion, I can't figure out what's operating
on what. Also the error handling has a bunch of code that,
as far as I can see, is never executed.

Here's some air code that I think might do whar you want:

Private Sub use_AfterUpdate()
Dim stationdesc As String
Dim dbs As DAO.Database
Dim rsZips As DAO.Recordset
Set dbs = CurrentDb
Set rsZips = dbs.OpenRecordset("tlist_station")
On Error GoTo Err_exitloop_Click

With rsZips
If .RecordCount > 0 Then
Do Until .EOF
If !use = -1 Then
stationdesc = stationdesc & ", " & !Station
End If
.MoveNext
Loop
End If
End With

Exit_exitloop_Click:
rsZips.Close : Set rsZips = Nothing
Set dbs = Nothing

Forms!frm_menu_item_setup!Station = Mid(stationdesc, 3)
Exit Sub

Err_exitloop_Click:
MsgBox Err.Description
Resume Exit_exitloop_Click
End Sub

Without seeing what you have in the tlist_station
table/query, I have no way of knowing if the end result is
what you want.

 

[Tammy F]

Thanks Jack...
Actually, it is like my .movenext and .movefirst are not even working.
I tried rsZips.movefirst - because that is what my varible is dim as. I
can't use docmd.gotorecord,,acnext because it produces an error. Why won't
the .movefirst work?
Tammy