Solved

Table record updating - Very complicated

Posted on 2012-12-22
22
363 Views
Last Modified: 2012-12-27
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
0
Comment
Question by:SteveL13
  • 8
  • 7
  • 7
22 Comments
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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

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.
0
 

Author Comment

by:SteveL13
Comment Utility
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
0
 

Author Comment

by:SteveL13
Comment Utility
Sorry, I posted my attempt right after you suggested.  I will definitely try your way.  I do see how the "looping works"
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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

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

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.
0
 

Author Comment

by:SteveL13
Comment Utility
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

Open in new window

0
 

Author Comment

by:SteveL13
Comment Utility
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
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Miriam, beat me to it again!  That's what I get for walking away and getting a cup of coffee.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 61

Expert Comment

by:mbizup
Comment Utility
:-)
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
Comment Utility
If you are concerned that a user might close the form without clicking the "Close" button, you should add the following code to your form.

1.  In the declarations section add:

    Private AllowClose as Boolean

2.  In the Form_Open event add:

     AllowClose = False

3.  In the cmd_Close_Click code, add the following just prior to calling the Close method

     AllowClose = True

4.  Then, in the Form_Unload event use:

Private Sub Form_Unload(Cancel as Integer)

    If AllowClose = False then
        msgbox "Click the 'Close' button to close this form!"
        Cancel = true
    End If

End Sub

The above steps will prevent your users from being able to close the form by clicking on the X in the upper right corner of the form.
0
 

Author Comment

by:SteveL13
Comment Utility
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
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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

Open in new window

0
 

Author Comment

by:SteveL13
Comment Utility
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
0
 

Author Comment

by:SteveL13
Comment Utility
To mbizup:

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

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
Comment Utility
Check your control names.

You are using both txtAppliedInvQty and txtAppliedQty.  Which one is correct?  (replace as needed).

Also, add the following to the top of your form's code - right under Option Compare Database:

Option Explicit

Open in new window


That will force you to declare all of your variables (and doing so will help you spot any typos).
0
 

Author Closing Comment

by:SteveL13
Comment Utility
WOW!!!  I think this was the toughest one I've run into.  You both deserve credit.  Thank you so much!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Steve, it would be helpful for others to see what your final solution looked like.  Can you post your final code here?
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now