Table record updating - Very complicated

Try this -

dim strSQL As string
dim I as integer

If chkbxSerialize = TRUE then
    strSQL = "INSERT INTO YOUR TABLE " _ 
              & " (OpenOrdRecID, Qty , PartN, Serial, SerialUsed )  " _
              & " VALUES ( " & frmOpenOrders!txtRecId & ", 1, #10/10/2020#, '" _
                                     & frmOpenOrders!txtPart_No & "','To Come', TRUE)"
     If frmOpenOrders!txtAppliedInvQty > 0 THen
          For I = 1 to frmOpenOrders!txtAppliedInvQty
                CurrentDB.execute strsql, dbFailOnError
          Next
     End if
Else
    strSQL = "INSERT INTO YOUR TABLE " _ 
              & " (OpenOrdRecID, Qty , PartN, SerialUsed )  " _
              & " VALUES ( " & frmOpenOrders!txtRecId & ", " _
                                      & frmOpenOrders!txtAppliedInvQty & ", #10/10/2020#, '" _
                                     & frmOpenOrders!txtPart_No & "','To Come', False)"

           CurrentDB.execute strsql, dbFailOnError
 End if

Select all 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.

Here is what I have so far.  I just don't know how to do the "looping" part in here...

        'Code must loop here number of times = txtAppliedInvQty

Private Sub Form_Close()

    If Me.txtAppliedInvQty > 0 Then
        Dim rs As dao.Recordset
        Set rs = CurrentDb.OpenRecordset("tblCast")

        rs.AddNew

        rs!OpenOrdRecID = Me.txtRecID
        rs!Date = #10/10/2020#
        rs!Cust = Me.txtCustomer_1
        rs!PartN = Me.txtPart_No
        rs!QtyGood = Me.txtAppliedInvQty
        rs!QtyScrap = 0
        rs!PO = Me.txtPurchase_Order
        rs!AckDate = Me.txtAck_Date
        rs!OrdQty = Me.txtOrder_Qty
        rs!Serial = "To Come"
        rs!SerialUsed = "True"

        rs.Update
       
    Else
       
        'Code must loop here number of times = txtAppliedInvQty
        Dim rs As dao.Recordset
        Set rs = CurrentDb.OpenRecordset("tblCast")

        rs.AddNew

        rs!OpenOrdRecID = Me.txtRecID
        rs!Date = #10/10/2020#
        rs!Cust = Me.txtCustomer_1
        rs!PartN = Me.txtPart_No
        rs!QtyGood = 1
        rs!QtyScrap = 0
        rs!PO = Me.txtPurchase_Order
        rs!AckDate = Me.txtAck_Date
        rs!OrdQty = Me.txtOrder_Qty
        rs!Serial = "To Come"
        rs!SerialUsed = "True"

        rs.Update
       
    End If

    Set rs = Nothing

    DoCmd.OpenForm "frmMainMenu"

End Sub

Sorry, I posted my attempt right after you suggested.  I will definitely try your way.  I do see how the "looping works"

You can apply that for-next loop to the code you posted too (your recordset code looks good overall).  If you do that, be sure to include a > 0 check for your txtQty.  If that quantity is mis-entered as a zero, you'll have an infinite loop.

You should probably also beef this up with a null check -- ie: something like:

if Trim ("" & txtQty) = "" then
         msgbox "Qty?"
         Exit Sub
End if

Select all Open in new window

Steve -

Just a thought here.  If you are not real comfortable with embedding those delimiters in SQL strings, the recordset method you posted will probably be easier for you to add to/maintain.

Steve,

1.  Why are you using an Inputbox rather than a form to capture this quantity figure?  By using the inputbox, you limit your ability to validate the number, what if someone enters the number 1000, or -3?  You need to include a textbox for this value on your form.

2.  you use the phrase "I have a form", do you mean  frmOpenOrders?  Are you using the AfterUpdate event of this form to insert this record(s) into the other table?  If so, add the Excess Quantity textbox to that form.

3.  You could do this insert operation with a loop, or with a single SQL statement.  I prefer to use a single SQL statement, although the SQL will be a little more complicated.  

    a.  First, you need a table (or a query) which contains as many records as you will possibly need to insert (ExcessQuantity).  I do this with a table (tbl_Numbers) which contains a single field (lng_Number) and ten records (the values 0-9).  With this table, I can create a query (qry_Numbers) that generates numbers as large as I want (for this example, I'll use 0-99).  That query looks like:

SELECT Tens.lng_Number * 10 + Ones.lng_Number as lng_Number
FROM tbl_Numbers as Tens, tbl_Numbers as Ones

This will generate the numbers 0-99.

    b.  I would put some code in the forms BeforeUpdate event to ensure that if chkbxSerialize is checked, the user has entered a numeric value in the new textbox that is within parameters (in this case 1-100)

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

Select all Open in new window

   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

Select all Open in new window

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.

You will notice that in a couple of instances, I have used the IIF() function to determine either what to write to a particular field, or how many records to include in the Insert.

Sorry for the delay.  Here is my code so far but it is not adding the record to tblCast if the part is serialized and the txtAppliedQty = 1 for example.  Also, it is not adding the record if the part is NOT serialized and the txtAppiedQty is "5", for example.  Can someone tell me what is wrong with my code?

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

Select all Open in new window

I also tried...

Private Sub Form_Close()

    If Me.txtAppliedInvQty > 0 Then

        'Routine to add records to tblCast if part is not a serialized part and applied qty is greater than 0
        If chkbxSerialize = False 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
   
    End If

    'Close the form
    DoCmd.OpenForm "frmMainMenu"

End Sub

The close event might be too late for this code.  Give this a try... add a commandbutton (cmdClose) to run this code and then close the form when it is done.  Also note the correction I made in the second half of the code:

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

Select all Open in new window

This is not going to work in the Close event.  By the time the close event has fired, the form has already unloaded and the of your controls will have been lost.

I would put it in the Click event of a "Close" button on your form.  I've modified your code slightly so that you only have one loop.

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

Select all Open in new window

Miriam, beat me to it again!  That's what I get for walking away and getting a cup of coffee.

:-)

Still not writing the record to the tblCast.  As a test I added a command button to the form just to write the record, not including the closing of the form and it still didn't work.  Here's the code for the test command button...

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
       
        Next
       
        rs.Update
       
    End If


End Sub

Whoops - Your update is in the wrong place too.  It should be before the Next:

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

Select all Open in new window

To fyed:

Using this code on my test command button I get...

"Compile Error:  Method or data member not found"


Code:

Private Sub Command132_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

To mbizup:

Using your latest code it still doesn't write to the tblCast

and if you are trying the shortened version that I posted, then you need to replace line 27:

    End If

with:

    Next

And then should probably add a line to close the recordset as well.  Line 28 would be:

rs.Close

Steve,

Try the changes mentioned above, then if you are still getting an error, indicate the line that the error is occurring on.  My code assumes that the form this button is on, and the controls referenced are on on frmOpenOrders, and not on a subform within the main form.

It would be helpful if you could take a screen shot of the form in design view and post it here.

WOW!!!  I think this was the toughest one I've run into.  You both deserve credit.  Thank you so much!

Steve, it would be helpful for others to see what your final solution looked like.  Can you post your final code here?