Duplicate Loop

[Adam McReynolds]

I need a loop through a text box to duplicate the current form as many times as the text box designates. A loop with SQL Insert is what I was thinking would work. The code I have crashes the DB. Maybe it is stuck somehow in an infinite loop? Here is what I have so far:

Private Sub Command325_Click()
Dim Check, Counter
IntValue = Text410
Check = True: Counter = 0

Do ' Outer loop.
Do While Counter < Me.Text410.Value ' Inner loop.
Counter = Counter + 1 ' Increment Counter.
If Counter = Me.Text410.Value Then ' If condition is True.
Check = False ' Set value of flag to False.
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tbl_module_repairs ([Customer], [Customer_Contact], [end_user], [system_of_origin], [Pickup_Date], [aps_rma], [po_num], [incoming_disposition], [status], [pickup_entity], [repair_tech]) VALUES ('" & Me!Customer & "','" & Me!Combo361 & "', '" & Me!In_Destination_System & "', '" & Me!In_System_of_Origin & "', '" & Me!PickUp_Date & "', '" & Me!APS_RMA & "', '" & Me!PO_No & "', '" & Me!IncomingDisposition & "', '" & Me!Status & "', '" & Me!Pickup_Entity & "', '" & Me!Combo392 & "');"
DoCmd.SetWarnings True

Exit Do ' Exit inner loop.
End If
Loop
Loop Until Check = False ' Exit outer loop immediately.

DoCmd.GoToRecord , "frm_Module_Repairs_Inbound", acNext
DoCmd.OpenForm "frm_Module_Repairs_Quick_Update_Form", acFormDS
DoCmd.GoToRecord acDataForm,"frm_Module_Repairs_Quick_Update_Form",acLast

End Sub

 

[Macropheliac]

It's difficult to see what you're trying to do, but maybe try Do while check=true at the beginning instead of Loop until check=false at the end.

 

[Adam McReynolds]

It's difficult to see what you're trying to do, but maybe try Do while check=true at the beginning instead of Loop until check=false at the end.

Thanks for the response. So drop the outer loop?

 

[Macropheliac]

No. I wasn't suggesting that. I'd have to study and understand your code a bit more before suggesting how to drop a loop. I was suggesting changing the outer loop to:

Do While Check=True
 
Loop

 

[boblarson]

Here's how I would do it:

Private Sub Command325_Click()
Dim lngCount As Long
Dim strSQL As String
 
strSQL = "INSERT INTO tbl_module_repairs ([Customer], [Customer_Contact], [end_user], " & _
         "[system_of_origin], [Pickup_Date], [aps_rma], [po_num], [incoming_disposition], " & _
         "[status], [pickup_entity], [repair_tech]) VALUES ('" & Me!Customer & "','" & _
         Me!Combo361 & "', '" & Me!In_Destination_System & "', '" & Me!In_System_of_Origin & "', '" & _
         Me!PickUp_Date & "', '" & Me!APS_RMA & "', '" & Me!PO_No & "', '" & _
         Me!IncomingDisposition & "', '" & Me!Status & "', '" & Me!Pickup_Entity & "', '" & Me!Combo392 & "');"
 
For lngCount = 1 To Me.Text410
     CurrentDb.Execute strSQL, dbFailOnError
Next
 
DoCmd.GoToRecord , "frm_Module_Repairs_Inbound", acNext
DoCmd.OpenForm "frm_Module_Repairs_Quick_Update_Form", acFormDS
DoCmd.GoToRecord acDataForm, "frm_Module_Repairs_Quick_Update_Form", acLast
 
End Sub

Now, I'm not sure what you are trying to do with the last three lines, but I suspect they can be dealt with a little better as well.

 

[Adam McReynolds]

Thanks for the responses. Bob's method is the direction I took and it worked like a charm except for if any of the fields are null I get an error, run time 3464 for ( CurrentDb.Execute strSQL, dbFailOnError). Is there a setting or code to fix this because sometimes there is a null field or two?

Additionally, can I set a restriction of no more than 100 records to prevent say 10,000 being created on accident?

And to Bob, the last three lines basically move the form to a new record so that the record shows on the datasheet and then leaves the form open so when I close the DS I can go right back to the form.

Thanks again for the help. This forum is awesome!

 

[Macropheliac]

Use nz with your controls:

NZ(Me!APS_RMA)

http://www.techonthenet.com/access/functions/advanced/nz.php

 

[Adam McReynolds]

Use nz with your controls:

NZ(Me!APS_RMA)

Thanks that worked. It only didn't work if the date field was null which is weird because it is not a required field. So I put this code in to prevent the run time error from occurring:

If IsNull(Me.PickUp_Date) Or Me.PickUp_Date = "" Then
MsgBox "Please Enter a Pickup Date to Continue"
Me.PickUp_Date.SetFocus
Cancel = True
Exit Sub
End If

Any ideas on the amount of duplicates restriction? I have never used the For, To, Next functions.

 

[Macropheliac]

Maybe someone else has something better for that, but have you ever used the DCount function?

http://www.techonthenet.com/access/functions/domain/dcount.php

 

[Adam McReynolds]

Ok, so I found the fix. Here is the final code I have and it works great:

Private Sub Command325_Click()
Dim lngCount As Long
Dim strSQL As String

If IsNull(Me.PickUp_Date) Or Me.PickUp_Date = "" Then
MsgBox "Please Enter a Pickup Date to Continue"
Me.PickUp_Date.SetFocus
Cancel = True
Exit Sub
End If

If Me.Text410 > 99 Then
MsgBox "You Cannot Create More than 100 Records in One Instance"
Me.Text410.SetFocus
Cancel = True
Exit Sub
End If

strSQL = "INSERT INTO tbl_module_repairs ([Customer], [Customer_Contact], [end_user], " & _
"[system_of_origin], [Pickup_Date], [aps_rma], [po_num], [incoming_disposition], " & _
"[status], [pickup_entity], [repair_tech]) VALUES ('" & Nz(Me!Customer) & "','" & _
Nz(Me!Combo361) & "', '" & Nz(Me!In_Destination_System) & "', '" & Nz(Me!In_System_of_Origin) & "', '" & _
Nz(Me!PickUp_Date) & "', '" & Nz(Me!APS_RMA) & "', '" & Nz(Me!PO_No) & "', '" & _
Nz(Me!IncomingDisposition) & "', '" & Nz(Me!Status) & "', '" & Nz(Me!Pickup_Entity) & "', '" & Nz(Me!Combo392) & "');"

For lngCount = 1 To Me.Text410
CurrentDb.Execute strSQL, dbFailOnError
Next

DoCmd.GoToRecord , "frm_Module_Repairs_Inbound", acNext
DoCmd.OpenForm "frm_Module_Repairs_Quick_Update_Form", acFormDS
DoCmd.GoToRecord acDataForm, "frm_Module_Repairs_Quick_Update_Form", acLast

End Sub

It requires a date field to not be null and will not let you enter more than 99 duplicates bringing the total to no more than 100 records. Thanks to Macropheliac and Boblarson!!!

 

[Macropheliac]

Oh. I forgot the number of duplicates was dictated by the user input. Sorry. I'm glad you got it.