Nested Loop??

[Carol]

First, thank you in advance for your help!

I have a form from which I want to run an append query for
all records in a table. The query append the date and
hour of service. If there is more than one hour, it
appends a record for each hour the patient is here. It
works great for the first patient, but I can't seem to get
it to advance to the next record and do the same thing.
Here is my code:


Do

Do While dtNextHour <= dtEndHour - 1
dtNextHour = dtNextHour + 1

Me.PTHour.Value = dtNextHour

If dtNextHour >= 24 Then
dtNextHour = dtNextHour - 24
dtEndHour = dtEndHour - 24
End If

DoCmd.OpenQuery stDocName, acNormal, acEdit

Loop

DoCmd.GoToRecord , , acNext


Loop Until IsNull(Me.PatientID.Value)

Thanks again,
Carol

 

[Crystal]

You need to tell it what recordset to look in (the table
on which your form/query is based)

Try something like:

Dim db as DAO.Database
Dim rst as DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("TableName", dbOpenDynaset)

rst.MoveFirst
Do Until rst.EOF = True
Do While dtNextHour <= dtEndHour - 1

dtNextHour = dtNextHour + 1

Me.PTHour.Value = dtNextHour

If dtNextHour >= 24 Then
dtNextHour = dtNextHour - 24
dtEndHour = dtEndHour - 24
End If

DoCmd.OpenQuery stDocName, acNormal, acEdit
rst.MoveNext
Loop

Loop

Hope this helps,
Crystal

 

[Crystal]

You have to set those two variables to their corresponding
fields in the table:

dtNextHour = rst!FieldName
dtEndHour = rst!FieldName
etc.

Do this at the top of the second Do loop.