Results 1 to 4 of 4
  1. #1
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48

    Query To Run On Current Record Only

    Hi Guys

    I have a query that has the following code

    UPDATE POSurvey SET
    NewTSSRRxPlanCompDate = IIF(SurveyComAd IS NOT NULL,dateAddWeekday(SurveyComAd,1)),

    NewTSSRSuPlanCompDate = IIF(TSSRRxComAd IS NOT NULL,dateAddWeekday(TSSRRxComAd,1)),

    NewTSSRApPlanCompDate = IIF(TSSRSuComAd IS NOT NULL ,dateAddWeekday(TSSRSuComAd,1))

    WHERE PODate IS NOT NULL;

    How do I get this query to not run on ALL the records in the POSurvey table but only on the current record in a form. My form is called frmPOSurvey The idea is that as soon as either SurveyComAd, TSSRRxComAd or TSSRSuComAd are updated then the query would run on only the record that is currently selected.

    I get it to run on all the records but just need it to run on the current record in the form.

    Could you please explain what the best option would be, a function, or some other solution. It could even be on a button that the user clicks after they enter the dates.



    Also it would be greatly appreciated if the Code Syntax could be given.

    Thanking you in Advance

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Is form bound to POSurvey table? Why run UPDATE query? Actually, trying to run UPDATE on a record that is already in edit status should cause runtime error. Could just set value of field on the form.

    The code is really quite simple. The real trick is figuring out what event to put it in. Perhaps each line in the AfterUpdate event of appropriate control.

    If Not IsNull(Me.SurveyComdAd) Then NewTSSRRxPlanCompDate = dateAddWeekday(Me.SurveyComAd,1)
    If Not IsNull(Me.TSSRRxComdAd) Then NewTSSRSuPlanCompDate = dateAddWeekday(Me.TSSRRxComAd,1)
    If Not IsNull(Me.TSSRSuComAD) Then NewTSSRApPlanCompDate = dateAddWeekday(Me.TSSRSuComAd,1)

    Are these associations correct? NewTSSRSuPlanCompDate is dependent on TSSRRxComAd, etc?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48
    Hi

    Thank you for your reply.

    Is form bound to POSurvey table? - Yes
    Are these associations correct? NewTSSRSuPlanCompDate is dependent on TSSRRxComAd, etc? - Yes

    If I could maybe explain what my desired outcome is.

    A user would select a PONo from a combox box that would populate the frmPOSurvey. The user then can edit the SurveyComAd, TSSRRxComAd,TSSRSuComAd dates when these tasks are completed. When the user, for example, fills in the SurveyComAd, the NewTSSRRxPlanCompDate would automatically update and the user would be able to view it immediately in the form.So without exiting the form they must be able to view the NewTSSRRxPlanCompDate. Is this possible?

    Any solutions would be welcome. Im definitely no expert with Access and am open to any ideas that maybe more efficient and are more closer to best practice.

    ------
    Edit
    ====

    Where would I put the following code

    If Not IsNull(Me.SurveyComdAd) Then NewTSSRRxPlanCompDate = dateAddWeekday(Me.SurveyComAd,1)
    If Not IsNull(Me.TSSRRxComdAd) Then NewTSSRSuPlanCompDate = dateAddWeekday(Me.TSSRRxComAd,1)
    If Not IsNull(Me.TSSRSuComAD) Then NewTSSRApPlanCompDate = dateAddWeekday(Me.TSSRSuComAd,1)

  4. #4
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48
    Thank You. I seem to have got it to work. Will test further and update post.

    A big Thank you for your assistance.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Append Query Current Record Only
    By KCC47 in forum Queries
    Replies: 5
    Last Post: 10-25-2013, 02:00 PM
  2. Query for current record
    By zooyo in forum Queries
    Replies: 3
    Last Post: 07-26-2013, 11:10 AM
  3. Run query for current record only
    By lmh24 in forum Queries
    Replies: 3
    Last Post: 06-01-2012, 03:23 PM
  4. Current Record Value in query
    By A S MANN in forum Queries
    Replies: 3
    Last Post: 12-21-2011, 06:21 AM
  5. How to output only current record in query
    By mslieder in forum Access
    Replies: 0
    Last Post: 01-20-2006, 05:48 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums