troubleshooting Question

Table record updating - Very complicated

Avatar of SteveL13
SteveL13Flag for United States of America asked on
Microsoft Access
22 Comments2 Solutions482 ViewsLast Modified:
This will get complicated I'm sure.

I have a form which has a field which has this code for the afterupdate event:

    If DSum("[Qty]", "tblFinGoods", "[PartN] = '" & Me.txtPart_No & "'") > 0 Then
        txtAppliedInvQty = InputBox("Excess inventory exists for this part number. Qty = " & (DSum("[Qty]", "tblFinGoods", "[PartN] = '" & Me.txtPart_No & "'") - Nz(DSum("[AppliedInvQty]", "tblOpenOrders", "[Part_No] = '" & Me.txtPart_No & "' AND [Complete] = 0"))) & ".  Enter the qty you want to apply to this order:")
    End If

This code is working just fine but the form needs to have code written to the onclose event of the form .  That code needs to be:

If on the form the checkbox named "chkbxSerialize" is false then accept the number entered in the inputbox and write JUST ONE RECORD per the above code to a separate unbound table named tblCast.  The fields that need to be written to that table are for example,

OpenOrdRecID = (from the open form named frmOpenOrders!txtRecId
Qty = (from the open form named frmOpenOrders!txtAppliedInvQty
Date = which will always be 10/10/2020
PartN = (from the open form named frmOpenOrders!txtPart_No
SerialUsed = False
and so forth.  There are more fields but if someone can help me with the above I will fill them in.  

But if the checkbox is true then I need to write ONE record for each entry in the input box.  For example, if the user entered "4" then four separate records should be written to the table like:

OpenOrdRecID = (from the open form named frmOpenOrders!txtRecId
Qty = 1 (will always be "1" unlike the above code)
Date = which will always be 10/10/2020
PartN = (from the open form named frmOpenOrders!txtPart_No
Serial = "To Come"
SerialUsed = True (different than the above code)
and so forth.  There are more fields but if someone can help me with the above, again I will fill them in.  

If anyone can help with this I sure appreciate it.  Thank you.

--Steve
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 22 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 22 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros