More efficient for loop

[Theguyinthehat]

I use a strict convention in naming--the boxes indicating thickness are 'thicktxt#' and for the mounting puck they are 'txtpuck#'. In any case, I currently have the form set up to accommodate 6 of each. However, if I change this to 7, is there some way I can set up the code so I only have to change the 'for' line instead of adding a few extra lines of code? I'd like to use something like 'puck("Puck)=Me.txtpuck' & I THis is my code (ignore strSQL part-- this just deletes null entries that unfortunately have to be recorded).


Private Sub dpt_Click()
Dim I As Integer
Dim puck As Object
Dim cthick As Object
For I = 1 To 6
Dim strSQL As String
strSQL = "DELETE FROM [dptdata] " & _
"WHERE [CZTNumber] = 0 AND [Sub Lot Number] = " & I
CurrentDb.Execute strSQL, dbFailOnError
Set puck = db.OpenRecordset("SELECT [Puck] FROM dptdata WHERE LotNumber = '" & Me.cboLotNumber & "' AND [Sub Lot Number] = " & I)
Set cthick = db.OpenRecordset("SELECT [Max Corner Thickness] FROM dptdata WHERE LotNumber = '" & Me.cboLotNumber & "' AND [Sub Lot Number] = " & I)
If I = 1 Then
puck("Puck") = Me.txtpuck1
thick("Max Corner Thickness") = Me.txtthick1
ElseIf I = 2 Then
puck("Puck") = Me.txtpuck2
thick("Max Corner Thickness") = Me.txtthick2
ElseIf I = 3 Then
puck("Puck") = Me.txtpuck3
thick("Max Corner Thickness") = Me.txtthick3
ElseIf I = 4 Then
puck("Puck") = Me.txtpuck4
thick("Max Corner Thickness") = Me.txtthick4
ElseIf I = 5 Then
puck("Puck") = Me.txtpuck5
thick("Max Corner Thickness") = Me.txtthick5
ElseIf I = 6 Then
puck("Puck") = Me.txtpuck6
thick("Max Corner Thickness") = Me.txtthick6
End If
puck.Close
thick.Close
Set puck = Nothing
Set thick = Nothing
Next I
End Sub

 

[pbaldy]

Try

Me("txtpuck" & I)

 

[Theguyinthehat]

great! Thanks. One more thing--I had to move the strSQL expression behind the puck and cthick edits (and made some other changes that became obvious once I ran the code). How do I prevent the code from making edits on entries that don't exist? Oftentimes I only have 4 entries, therefore sub lot numbers go 1-4. Is there a way to make it so if the record with sub lot number 5 doesn't exist, it just moves on instead of sending an error?

 

[pbaldy]

You mean the textbox might be empty?

If Len(Me("txtpuck" & I) & vbNullString) = 0 Then
  'box is empty
Else
  'box is populated
End If

 

[Theguyinthehat]

Sorry--let me better explain. The very first form has 6 potential entries (that's as many slides as the DPT machine can handle at once). When they indicate fewer slides, say 4, there are 2 records without CZT numbers. I then know to delete these. Since I'm running my for loops assuming 6 records all the time, if I delete these before I stop making edits (via subsequent forms), It tries to edit the entry where the sub Lot Number = 5 and 6, whereas those entries were deleted. When the for loop doesn't find a record for
Set puck = db.OpenRecordset("SELECT [Puck] FROM dptdata WHERE LotNumber = '" & Me.cboLotNumber & "' AND [Sub Lot Number] = " & I)
for I = 5 or 6 it sends me an error. How do I prevent that? Or should I just let the records remain until the final edit, then delete them?

 

[RossWindows]

This is just air-code, but it looks solid to me.

vPrivate Sub dpt_Click()
[COLOR=blue]On Error Resume Next[/COLOR]
 
Dim I As Integer
Dim puck As Object
Dim cthick As Object
 
For I = 1 To 6
 
Dim strSQL As String
 
strSQL = "DELETE FROM [dptdata] " & _
"WHERE [CZTNumber] = 0 AND [Sub Lot Number] = " & I
 
CurrentDb.Execute strSQL, dbFailOnError
 
Set puck = db.OpenRecordset("SELECT [Puck] FROM dptdata WHERE LotNumber = '" & Me.cboLotNumber & "' AND [Sub Lot Number] = " & I)
Set cthick = db.OpenRecordset("SELECT [Max Corner Thickness] FROM dptdata WHERE LotNumber = '" & Me.cboLotNumber & "' AND [Sub Lot Number] = " & I)
 
[COLOR=blue]puck("Puck") = Me!("txtpuck" & Format$(I))[/COLOR]
[COLOR=blue]thick("Max Corner Thickness") = Me!("txtthick" & Format$(I))[/COLOR]
 
puck.Close
thick.Close
 
Set puck = Nothing
Set thick = Nothing
 
Next I
 
End Sub