Jumat, 15 November 2019

Re: [MS_AccessPros] The Update query issue

 

You didn't mention which line it is crashing on. I would modify what you provided to aid in debugging. I would also set a break point and step through the code a line at a time.

Dim strSQLUpdate as String
Do While Not rs2.EOF
    Id = rs2!EventID
    ProcessedInv = rs2!ProcessedInv
    ProcessEventEndDt = rs2!ProcessEventEndDt
    EventEndDt = rs2!EventEndDt
   ' MsgBox "INner Loop" & EventEndDt & currDate & Id, vbInformation
   'if the event is over but the inventory is not put back, the loop in run
    If (DateDiff("d", EventEndDt, currDate) > 0) And ProcessedInv = True And ProcessEventEndDt = False Then
   
       ' MsgBox "INnermost Loop" & EventEndDt & currDate & Id, vbInformation
        cntr = 0
        Set RS = Form_frmMainEvents.SubformEventDetails.Form.Recordset
        RS.MoveFirst
        Do While Not RS.EOF
       '  MsgBox "INnermost Loop" & EventEndDt & currDate & Id, vbInformation
            Q1 = Form_frmMainEvents![SubformEventDetails].Form![quantity]
            colName1 = Form_frmMainEvents![SubformEventDetails].Form![Item_Name]
            Lcntr = DateDiff("d", rs2!DeliveryDt, rs2!EventEndDt) + 1
            For cntr = 0 To Lcntr - 1
                invDate = DateAdd("d", cntr, rs2!DeliveryDt)
                strSQLUpdate = " Update Tbl_InvDetails set [" & colName1 & "] = [" & colName1 & "] + " & Q1 & _
                      " where Tbl_InvDetails.InvDate = #" & invDate & "#  "
                Debug.Print strSQLUpdate     'let's take a look at the SQL statement
                CurrentDb.Execute strSQLUpdate, dbFailOnError
            Next
            RS.MoveNext
        Loop

Duane


From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, November 15, 2019 2:19 PM
To: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Subject: [MS_AccessPros] The Update query issue
 


Hello all,


I am having issues with a line of code for the UPDATE statement. I have created a query and then created the update query and pasted the sql statement into the line of code and modified it to be relevant and it is still not working. can someone give me some direction please. It is a mission critical database and the user is calling my boss and complaining



Thank You


Jim Wagner


here is the code. it is crashing on the


Do While Not rs2.EOF
    Id = rs2!EventID
    ProcessedInv = rs2!ProcessedInv
    ProcessEventEndDt = rs2!ProcessEventEndDt
    EventEndDt = rs2!EventEndDt
   ' MsgBox "INner Loop" & EventEndDt & currDate & Id, vbInformation
   'if the event is over but the inventory is not put back, the loop in run
    If (DateDiff("d", EventEndDt, currDate) > 0) And ProcessedInv = True And ProcessEventEndDt = False Then
   
       ' MsgBox "INnermost Loop" & EventEndDt & currDate & Id, vbInformation
        cntr = 0
        Set RS = Form_frmMainEvents.SubformEventDetails.Form.Recordset
        RS.MoveFirst
        Do While Not RS.EOF
       '  MsgBox "INnermost Loop" & EventEndDt & currDate & Id, vbInformation
            Q1 = Form_frmMainEvents![SubformEventDetails].Form![quantity]
            colName1 = Form_frmMainEvents![SubformEventDetails].Form![Item_Name]
            Lcntr = DateDiff("d", rs2!DeliveryDt, rs2!EventEndDt) + 1
            For cntr = 0 To Lcntr - 1
                invDate = DateAdd("d", cntr, rs2!DeliveryDt)
                CurrentDb.Execute " Update Tbl_InvDetails set [" & colName1 & "] = [" & colName1 & "] + " & Q1 & " where Tbl_InvDetails.InvDate = #" & invDate & "#  ", dbFailOnError
            Next
            RS.MoveNext
        Loop



__._,_.___

Posted by: Duane Hookom <duanehookom@hotmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (3)

.

__,_._,___

Tidak ada komentar:

Posting Komentar