Loop Problem out of control

[act2]

I been looking at this for a couple of hours and can’t figure it out. The program gives me the results I’m look for, however it gives it to me multiple times. If I check one ck boxes I get the information in the groups I expect except it copies it 27 times. If I have both ck boxes checked the first boxes results, I get twice and the second,I get 27 times. Any Ideas. Thanks

Option Compare Database
Option Explicit
Private Sub cmdEnter_Click()

Dim wtb As Integer
Dim r As Integer
Dim sr As Integer
Dim x As Integer
Dim Y As Integer
Dim w As Integer
Dim dh As Integer
Dim lo As Integer
Dim jt As String

wtb = Format(Me.CompleteDate(), "ww") - Format(Me.StartDate(), "ww")
Me.WeeksToBuild = wtb
x = Format(Me.CompleteDate(), "ww")
w = Format(Me.StartDate(), "ww")
dh = Me.txtDesign / wtb
lo = Me.txtLayout / wtb
Y = wtb + w


Dim ctl As Control

   Dim i As Integer
   For i = 1 To 9
      
      For Each ctl In Me.Controls
         If ctl.ControlType = acCheckBox Then
            If ctl.Value = True Then
               
               jt = ctl.Controls(0).Caption
               
          End If
     End If
       
           For x = w To Y
                  If x < Y Then
                  If jt = "Design" Then
                        Forms.frmMain.frmsubMain.Form.WkNumber = x
                        Forms.frmMain.frmsubMain.Form.JobType = jt
                        Forms.frmMain.frmsubMain.Form.ToolNum = Me.ToolNum
                        Forms.frmMain.frmsubMain.Form.Year = Me.Year
                        Forms.frmMain.frmsubMain.Form.JobHr = dh
                      Else
                      If jt = "Layout" Then
                        Forms.frmMain.frmsubMain.Form.WkNumber = x
                        Forms.frmMain.frmsubMain.Form.JobType = jt
                        Forms.frmMain.frmsubMain.Form.ToolNum = Me.ToolNum
                        Forms.frmMain.frmsubMain.Form.Year = Me.Year
                        Forms.frmMain.frmsubMain.Form.JobHr = lo
                     End If
                        End If
                            End If
                            
                                Me.Refresh
                                  Forms.frmMain.frmsubMain.Form.Recordset.AddNew
Next x
    Next
       Exit For
    Next i
End Sub

 

[Zion7]

What's this for?...

Dim i As Integer
For i = 1 To 9
Next i

 

[act2]

Hi Zion7, It cycles through check boxes to grab the names of any that are checked. This is the code from my post yesterday that you helped me with. I took it one step further today and tried to combine it with my other code. It works and gives me the correct results. (just to many of them)

Private Sub cmdEnter_Click()

Dim ctl As Control

   Dim i As Integer
   For i = 1 To 5
      
      For Each ctl In Me.Controls
         If ctl.ControlType = acCheckBox Then
            If ctl.Value = True Then
               
               Forms.frmMain.frmsubMain.Form.JobType = ctl.Controls(0).Caption
               
          End If
     End If
        Me.Refresh
            Forms.frmMain.frmsubMain.Form.Recordset.AddNew
    Next
  
     Exit For
    Next i

End Su

 

[Zion7]

Yes but, You're already cycling through the check boxes, using the controls collection?
So you're doing it again, 9x too many? Or is it 5? (last post was 9, this one 5?)

Are there 9/5 checkboxes that you're analysing?
Apparently, you're re-checking them, 9/5x.

 

[act2]

Zion7, Thanks for your help on this I think I finally have my problem solved. It may not be pretty but it works. I have to do some more, add error checking and clean up. Here’s the Code:

Option Compare Database
Option Explicit
Private Sub cmdEnter_Click()

Dim wtb As Integer
Dim x As Integer
Dim Y As Integer
Dim w As Integer
Dim dh As Integer
Dim lohr As Integer
Dim CNCphr As Integer
Dim Layout As String
Dim Design As String
Dim CNCProg As String

wtb = Format(Me.CompleteDate(), "ww") - Format(Me.StartDate(), "ww")
Me.WeeksToBuild = wtb
x = Format(Me.CompleteDate(), "ww")
w = Format(Me.StartDate(), "ww")
dh = Me.txtDesign / wtb
lohr = Me.txtLayout / wtb
CNCphr = Me.txtCNCProg / wtb
Y = wtb + w
  


If Me.ck1.Value Then
Design = Me.Designlbl.Caption
End If

If Me.ck2.Value Then
Layout = Me.LayoutLbl.Caption
 End If
 
 
If Me.ck3.Value Then
CNCProg = Me.CNCProLbl.Caption
 End If
 
         
            For x = w To Y
                  If x < Y Then
                  If Design = "Design" Then
                        Forms.frmMain.frmsubMain.Form.WkNumber = x
                        Forms.frmMain.frmsubMain.Form.JobType = Design
                        Forms.frmMain.frmsubMain.Form.ToolNum = Me.ToolNum
                        Forms.frmMain.frmsubMain.Form.Year = Me.Year
                        Forms.frmMain.frmsubMain.Form.JobHr = dh
                        End If
                             Me.Refresh
                             Forms.frmMain.frmsubMain.Form.Recordset.AddNew
                            End If
                            Next x


            For x = w To Y
                  If x < Y Then
                  If Layout = "Layout" Then
                        Forms.frmMain.frmsubMain.Form.WkNumber = x + 1
                        Forms.frmMain.frmsubMain.Form.JobType = Layout
                        Forms.frmMain.frmsubMain.Form.ToolNum = Me.ToolNum
                        Forms.frmMain.frmsubMain.Form.Year = Me.Year
                        Forms.frmMain.frmsubMain.Form.JobHr = lohr
                        End If
                             Me.Refresh
                             Forms.frmMain.frmsubMain.Form.Recordset.AddNew
                            End If
                            Next x

            For x = w To Y
                  If x < Y Then
                  If CNCProg = "CNC Prog" Then
                        Forms.frmMain.frmsubMain.Form.WkNumber = x + 2
                        Forms.frmMain.frmsubMain.Form.JobType = CNCProg
                        Forms.frmMain.frmsubMain.Form.ToolNum = Me.ToolNum
                        Forms.frmMain.frmsubMain.Form.Year = Me.Year
                        Forms.frmMain.frmsubMain.Form.JobHr = CNCphr
                        End If
                             Me.Refresh
                             Forms.frmMain.frmsubMain.Form.Recordset.AddNew
                            End If
                            Next x





End Sub

 

[Zion7]

Good to hear, you've reached your objective.

A couple of pointers if I may...

You can use "With" statements, to abbreviate "long winded" referencing.
ie;

With Forms.frmMain.frmsubMain.Form
!WkNumber = x + 2
!JobType = CNCProg
!ToolNum = Me.ToolNum
!Year = Me.Year
!JobHr = CNCphr
End With

mind you, I would actually put the "End With", after the last refrence to the subform, in your code(last if block).

2ndly, not sure if I'm accurate or not, but I wonder if your logic is correct, within each If Block...

For x = w To Y 'why loop, unless Design = "Design"
If x < Y Then
If Design = "Design" Then
......
End If
End If
Next x


...in other words, ask it first. You may be able to avoid the whole loop?

With Forms.frmMain.frmsubMain.Form

If Design = "Design" Then
For x = w To Y
If x < Y Then
!WkNumber = x
!JobType = Design
!ToolNum = Me.ToolNum
!Year = Me.Year
!JobHr = dh
Me.Refresh
.Recordset.AddNew
End If
Next x
End If

If Layout = "Layout" Then
For x = w To Y
If x < Y Then
!WkNumber = x + 1
!JobType = Layout
!ToolNum = Me.ToolNum
!Year = Me.Year
!JobHr = lohr
Me.Refresh
.Recordset.AddNew
End If
Next x
End If

End With


NOW, I MAY BE WRONG, on that logic. Disregard if so.

Lastly, can ck1, ck2 & ck3 all be true simultaneously?
Or even 2 at a time (it's not an option group?).

If so, everything is fine, otherwise, too much repetition.

Hope I'm not being too presumptuous.
Good luck either way.
Thx for showing the code!