multiple loops

[SKILTC]

I am working on a schedule program and am having trouble getting the program to schedule all employees at once. I created a form based on a schedule set table which has the fields: ID, PostID, PositionID, TourID, EmpID. I also have a subfrom based on the legs table- fields: TourID, Sequence, On, Off. The subform is linked to the main form by TourID. I have 5 unbound fields Startdate, Enddate, datestep, Countit1 (record count of main form) and Countit (record count of subform). When the ScheduleAll button is clicked it adds 13 days to the startdate field for the enddate value, and copies startdate for datestep value. The program should then loop through each record and loop through the subform records to call an append query which adds the Date, EmpID, PostID, PosID, ScheduleID to a table. The program loops through the first record and subform sequence just fine. On the second record to the last record of the main form the looping does not occur for the subform. Not sure why the looping does not occur. I have tried Do While, Do Until, For Next all with the same results. I have also tried all possible combinations of removing/adding the .refresh command I am also having trouble with the date field. The append query is using the unbound control datestep on the main form but appends either nothing or square boxes. Here is the code.

Private Sub SchdAll_Click()
On Error GoTo Err_SchdAll_Click

Dim stDocName As String
Dim x, y, s, t, subtrec, trec As Integer
Dim datex As Date


trec = [countit1]
t = 0

Do Until t >= trec

datex = Startd
datestep = datex
subtrec = [countit]
s = 0
[tbl legs subform1].SetFocus

Do Until s >= subtrec

x = 0
y = 0

Do While x < Forms![frm scheduleit]![tbl legs subform1]![On]
stDocName = &quot;qry TourtempAll ONtest2&quot;
DoCmd.OpenQuery stDocName, acNormal, acEdit
datex = datex + 1
datestep = datestep + 1
Me.Refresh
x = x + 1
Loop

Do While y < Forms![frm scheduleit]![tbl legs subform1]![Off]
datex = datex + 1
datestep = datestep + 1
Me.Refresh
y = y + 1
Loop

stDocName = &quot;qry Update Off Dates2&quot;
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.GoToRecord , , acNext
s = s + 1
Loop

schdID.SetFocus
DoCmd.GoToRecord , , acNext
t = t + 1
Me.Refresh
Loop

Exit_SchdAll_Click:
Exit Sub

Err_SchdAll_Click:
MsgBox Err.Description
Resume Exit_SchdAll_Click

End Sub

 

[MichaelRed]

Check Later.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Craig0201]

Small point but x, y, s, t and subtrec have not been declared as a datatype. You need to declare one for each variable. In your code, only trec has been declared as an integer.

Craig

 

[MichaelRed]

Hmmmmmmmmmm,

I thought this would be quick. Oh well.

I do not use a form for a recordsource so this is 'off (MY) beaten track, and I will not attempt to delve into the real soloution.

I do have a few suggestions:

As previously noted, you do not have your variables declared properly - in fact some of them (the variables) are not &quot;DECLARED&quot; at all. Add the Option Explicit to all of your existing modules and set the flag in the database to include this in all future modules.

I generally avoid single character variables for several reasons, not the least of which is standardization / naming convention. You should consider adopting one of the several naming conventions for VB/VBA code.

Your module appears to be one of the forms class modules, relying on forms controls for several of the values (DateStep, StartDate, EndDate, ...). While this is not illegal, I would generally seperate anything as complex as this in a &quot;regular&quot; module and explicitly pass the form's comtrols content/values explicitly By Val (or - if you REALLY want the procedure to change them, then By Ref) - but at least PASS them to the routine.

The use of the 'variables' DateX and DateStep is &quot;Interesting&quot;. As far as I can tell, they are &quot;lock stepped&quot; to being equal, do one of them (DateStep) is superfolous (sp?).

A really minor point, the outter loop is:

Do Until Tdx >= Trec

but Trec is simply incremented from Zero, so unless Trec is negative, the &quot;> is useless.

Apply the same 'logic?' to the &quot;S&quot; loop.

You use &quot;I&quot; for a loop counter, followed immediatly by the use of &quot;Y&quot; as another (non overlapping) loop counter. You can easily save the declaration (storage) of the loop counter(s) by re-use of just one. Actually, the second loop only appears to increment DateX / DateStep by the value of the referenced control, so the (second) loop is un-necessary, and should be replaced by a simple assignment statement.

You open each (both) of the &quot;queries&quot; within their respecitve loops, - but you never close them. In the inner loop (&quot;I&quot;, I don't see where any record change is ever accomplished. Since we do not know the relation between the &quot;queries&quot; and the form, it is NOT possible to be sure that the DoCmd.GoToRecord accomplishes this. However, evenif it does, the &quot;I&quot; loop doesn't see the change except when once fro each pass of the &quot;S&quot; loop.

And, a &quot;parting shot&quot;. You appear to be 'fond' of readability, as evidenced in the embedded spaces in your control and &quot;querry&quot; names. This practice is not recommended by anyone. Stop now and save yourself some grief / angst.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over