if Trim ("" & txtQty) = "" then
msgbox "Qty?"
Exit Sub
End if
Private Sub Form_BeforeUpdate (Cancel as Integer)
'Set the default value for the Cancel argument
Cancel = True
'Check to make sure that if chkbxSerialize is checked, there is a value in the field
'txtExcessQuantity and that the value is within parameters
if me.chkbxSerialize = True Then
if Len(me.txtExcessQuantity & "") = 0 then
msgbox "enter an Excess Quantity value!
me.txtExcessQuantity.SetFocus
Exit Sub
elseif Isnumeric(me.txtExcessQuantity) = False then
msgbox "Excess Quantity must be numeric between 1 and 100"
me.txtExcessQuantity.SetFocus
Exit Sub
elseif Val(me.txtExcessQuantity) < 1 then
msgbox "Excess Quantity value must be between 1 and 100"
me.txtExcessQuantity.SetFocus
Exit Sub
elseif Val(me.txtExcessQuantity) >100 then
msgbox "Excess Quantity value must be between 1 and 100"
me.txtExcessQuantity.SetFocus
Exit Sub
End If
End If
'If the code passes these tests, then set Cancel = False
Cancel = False
End Sub
  c.  Then you need to write the SQL string.  This string will have some logic involved, but is not too difficult:
Private Sub Form_AfterUpdate
Dim strSQL as string
'This assumes that your PartN field is a string
'It also assumes that the absence of the [Serial] field in the top part of your explanation
'means that that field should be NULL if chkbxSerialize = False
'I've included examples for the "other fields". If the field is numeric use syntax
'similar to the [FieldX] row; if a string, use the [FieldY] example
strSQL = "INSERT INTO tblCast (OpenOrdRecID, Qty, [Date], PartN, Serial, SerialUsed, [FieldX], [FieldY]) " _
& "SELECT " & me.txtRecID & ", " _
& iif(me.chkbxSerialize, 1, me.txtAppliedInvQty) & ", " _
& "#10/10/2020#, " _
& chr$(34) & me!txtPart_No & chr$(34) & ", " _
& iif(me.chkbxSerialize, "'To Come'","NULL") & ", " _
& iif(me.chkbxSerialize, -1, 0) & ", " _
& [FieldX] & ", " _
& chr$(34) & [FieldY] & chr$(34) & " " _
& "FROM qry_Numbers " _
& "WHERE [lng_Number] < " & iif(me.chkbxSerialize, me.txtExcessQuantity)
Debug.print strSQL
Currentdb.Execute strsql, dbfailonerror
End Sub
I have included the Debug.print line above as a way for you to test the SQL string before actually executing it. Â The critical aspect of this is making certain that any of the values you will be inserting into TEXT fields in your table are wrapped in quotes or single quotes, and that you have commas between each of the elements of the SELECT statement.Private Sub Form_Close()
'Routine to add records to tblCast if part is not a serialized part and applied qty is greater than 0
If chkbxSerialize = False And txtAppliedQty > 0 Then
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("tblCast")
rs.AddNew
rs!OpenOrdRecID = txtRecID
rs!QtyCast = txtAppliedInvQty
rs!Date = #10/10/2020#
rs!PartN = txtPart_No
rs!PO = txtPurchase_Order
rs!AckDate = txtAck_Date
rs!OrdQty = txtOrder_Qty
rs.Update
End If
'Routine to add records to tblCast if part IS a serialized part and applied qty is greater than 0
If chkbxSerialize = True And txtAppliedQty > 0 Then
rs.AddNew
For I = 1 To frmOpenOrders!txtAppliedInvQty
rs!OpenOrdRecID = txtRecID
rs!QtyCast = 1
rs!Date = #10/10/2020#
rs!PartN = txtPart_No
rs!PO = txtPurchase_Order
rs!AckDate = txtAck_Date
rs!OrdQty = txtOrder_Qty
rs!Serial = "To Come"
rs!SerialUsed = True
Next
rs.Update
End If
'Close the form
DoCmd.OpenForm "frmMainMenu"
End Sub
Private Sub cmdClose_Click()
'Routine to add records to tblCast if part is not a serialized part and applied qty is greater than 0
If chkbxSerialize = False And txtAppliedQty > 0 Then
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("tblCast")
rs.AddNew
rs!OpenOrdRecID = txtRecID
rs!QtyCast = txtAppliedInvQty
rs!Date = #10/10/2020#
rs!PartN = txtPart_No
rs!PO = txtPurchase_Order
rs!AckDate = txtAck_Date
rs!OrdQty = txtOrder_Qty
rs.Update
End If
'Routine to add records to tblCast if part IS a serialized part and applied qty is greater than 0
If chkbxSerialize = True And txtAppliedQty > 0 Then
For I = 1 To frmOpenOrders!txtAppliedInvQty ' Add new needs to come after the For statement
rs.AddNew
rs!OpenOrdRecID = txtRecID
rs!QtyCast = 1
rs!Date = #10/10/2020#
rs!PartN = txtPart_No
rs!PO = txtPurchase_Order
rs!AckDate = txtAck_Date
rs!OrdQty = txtOrder_Qty
rs!Serial = "To Come"
rs!SerialUsed = True
Next
rs.Update
End If
'Close the form
DoCmd.OpenForm "frmMainMenu"
Docmd.Close acForm, Me..Name
End Sub
Private Sub cmd_Close_Click()
Dim intLoop as Integer
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("tblCast")
For intLoop = 1 to iif(me.chkbxSerialize = False and me.txtAppliedQty > 0, 1, me.txtAppliedInvQty)
rs.AddNew
rs!OpenOrdRecID = txtRecID
rs!QtyCast = iif(me.chkbxSerialize = False, me.txtAppliedInvQty, 1)
rs!Date = #10/10/2020#
rs!PartN = me.txtPart_No
rs!PO = me.txtPurchase_Order
rs!AckDate = me.txtAck_Date
rs!OrdQty = me.txtOrder_Qty
if me.chkbxSerialize = True Then
rs!Serial = "To Come"
rs!SerialUsed = True
end if
rs.Update
End If
'Close the form
docmd.close acform, me.name
DoCmd.OpenForm "frmMainMenu"
End Sub
Private Sub Command132_Click()
'Routine to add records to tblCast if part is not a serialized part and applied qty is greater than 0
If chkbxSerialize = False And txtAppliedQty > 0 Then
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("tblCast")
rs.AddNew
rs!OpenOrdRecID = txtRecID
rs!QtyCast = txtAppliedInvQty
rs!Date = #10/10/2020#
rs!PartN = txtPart_No
rs!PO = txtPurchase_Order
rs!AckDate = txtAck_Date
rs!OrdQty = txtOrder_Qty
rs.Update
End If
'Routine to add records to tblCast if part IS a serialized part and applied qty is greater than 0
If chkbxSerialize = True And txtAppliedQty > 0 Then
For I = 1 To frmOpenOrders!txtAppliedInvQty ' Add new needs to come after the For statement
rs.AddNew
rs!OpenOrdRecID = txtRecID
rs!QtyCast = 1
rs!Date = #10/10/2020#
rs!PartN = txtPart_No
rs!PO = txtPurchase_Order
rs!AckDate = txtAck_Date
rs!OrdQty = txtOrder_Qty
rs!Serial = "To Come"
rs!SerialUsed = True
rs.Update
Next
End If
End Sub
Open in new window
When adding to this, use delimiters appropriate for the data type -
 Text needs quotes
Dates need hash marks (#)
Numbers get no delimiters
I'm assuming that Part_No is text, Qty is numeric, and OpenOrdRecID is numeric in this code.