ACCESS FEED: Re: [MS_AccessPros] Re: The Update query issue

Senin, 18 November 2019

Re: [MS_AccessPros] Re: The Update query issue

Thank You, I was able to find the problem. I needed to rewrite the query and add that to the line of code.

Jim Wagner

On Friday, November 15, 2019, 1:49:05 PM MST, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <ms_access_professionals@yahoogroups.com> wrote:

The user has the KB installed. I will try the step into

Jim Wagner

On Friday, November 15, 2019, 1:45:28 PM MST, wrmosca@comcast.net [MS_Access_Professionals] <ms_access_professionals@yahoogroups.com> wrote:

Oops! I found your closing paren. Follow Duane's advice. But find out if the user has the bad Office Update KB installed (see earlier posting).-Bill

---In MS_Access_Professionals@yahoogroups.com, <wrmosca@comcast.net> wrote :

Jim - I see a wayward parenthesis right before DateDiff. Either remove it or put a closing paren after ProcessEventEndDt = False

If (DateDiff("d", EventEndDt, currDate) > 0) And ProcessedInv = True And ProcessEventEndDt = False Then

-Bill

---In MS_Access_Professionals@yahoogroups.com, <luvmymelody@yahoo.com> wrote :

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