Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48

    Updating Field In Different Table From Table Bound to Form

    Hi

    I have a form frmPOSurvey bound to tblPOSurvey

    In tblPOSurvey is a field TSRApCoDate which is a date input on the form.

    I want to do the following:



    After updating/entering the date in the TSRApCoDate field on the form I want it to update a field 'NewEQApDate' in tblPOInstall

    I have a query that does this perfectly when I run the query in design mode. (If I manually input the dates in tblPOSurvey.TSRApCoDate)

    UPDATE POInstall
    LEFT JOIN POSurvey ON POInstall.POID=POSurvey.POID
    SET POInstall.NewEQApDate= dateAddWeekday(POSurvey.TSRApCoDate ,1)
    WHERE POSurvey.TSRApCoDate Is Not Null And POInstall.NewEQApDate Is Null;

    I have tried running it on an event TSRApCoDate_AfterUpdate but all it does is open the query in datasheet view with no details and POInstall is not updated.

    Also, how do I prevent queries that are run after an even in a form from opening up in datasheet view. I just want the query to run in the background.

    Thanking you in Advance

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    An update query , in fact any action query shouldn't be opened per se - it should be run using Currentdb.Execute "YourQueryName" .

    I don't understand why the query that works, would be asking you for manual input of the date(s?) if it was running correctly either.
    That generally indicates you have mis-typed something.

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

    Im not sure If Im explaining it correctly. When Im testing the query "manually" , I manually input the dates into tblPOSurvey.TSRApCoDate (not from the form). I then run the query and it successfully updates POInstall.NewEQApDate .

    What I would like is when a user inputs the date into frmPOSurvey (the date field
    TSRApCoDate ) it should automatically update POInstall.NewEQApDate

    Hope this clarifies.

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I see - then simply replace the tblPOSurvey.TSRApCoDate with [Forms]![YourFormName]![YourDateControlName] in the query.
    You can use the Expression Builder to create the reference for you.

  5. #5
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48
    Like So?

    UPDATE POInstall LEFT JOIN POSurvey ON POInstall.POID=POSurvey.POID


    SET POInstall.NewEQApDate = dateAddWeekday([Forms]![POSurvey]![TSRApCoDate],1)
    WHERE ([Forms]![POSurvey]![TSRApCoDate]) Is Not Null And POInstall.NewEQApDate Is Null;

  6. #6
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Assuming it's a saved query yes, that looks about right.

    If you where building this as a SQL query String in VBA you would be better to parameterise it, and pass the value in to the string that way, but that's probably another topic.

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

    I have tried the above solution and all that happens is that after the field is updated with the date in the form, the query runs and opens a datasheet view with field "NewEQApDate" and a few blank records. The actual field in tblPOInstall is not updated. Any suggestions. I have tried triggering the query on a few different events with the same result.

    The query is a saved query.

    Thank You


  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm guessing this is a function call and it's getting a text value rather than a date. Assuming you're building this as sql in vba, suggest trying
    Code:
    "UPDATE POInstall LEFT JOIN POSurvey ON POInstall.POID=POSurvey.POID 
    SET POInstall.NewEQApDate = dateAddWeekday(#" & [Forms]![POSurvey]![TSRApCoDate] & "#,1)
    WHERE ([Forms]![POSurvey]![TSRApCoDate]) Is Not Null And POInstall.NewEQApDate Is Null;"
    I leave it to you to add the line continuation characters or otherwise deal with the line breaks I've got that vba won't like.
    Alternatively, you could test the sql as
    Code:
    ...SET POInstall.NewEQApDate = dateAddWeekday(#12/07/2017#,1)...
    and also
    Code:
    ...SET POInstall.NewEQApDate = dateAddWeekday(12/07/2017,1)...
    I'd expect the latter to fail, which ought to replicate the situation you have by referencing the value of a textbox, which usually looks like a string to sql and not a valid date.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

    Thanks for all the assistance thus far.



    Public Function DoSQL()
    Dim sSQL As String




    sSQL = "UPDATE POInstall LEFT JOIN POSurvey ON POInstall.POID=POSurvey.POID" & _
    "SET POInstall.NewEQApDate = dateAddWeekday(#' & [Forms]![POSurvey]![TSRApCoDate] & '#,1)" & _
    "WHERE ([Forms]![POSurvey]![TSRApCoDate]) Is Not Null And POInstall.NewEQApDate Is Null;"


    ====================== I Also Tried================================


    sSQL ="UPDATE POInstall LEFT JOIN POSurvey ON POInstall.POID=POSurvey.POID" & _
    '"Set POInstall.NewEQApDate = dateAddWeekday(POSurvey.TSRApCoDate, 1)" & _
    "WHERE (POSurvey.TSRApCoDate) Is Not Null And POInstall.NewEQApDate Is Null;"
    ================================================== ============


    DoCmd.RunSQL sSQL


    End Function


    I am at the end of the rope today - about to use it to hang myself......

    I JUST cannot get this to work. I have tried everyway to do it.

    Its keeps failing and on the same line DoCmd.RunSQL sSQL
    With error 3075 Syntax Error (Missing Operator in Query Expression)

    Please Help

  10. #10
    archie1_za is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    48
    Tried this way as well

    Dim db As DAO.Database, sSQL As String
    Set db = CurrentDb()


    sSQL = "UPDATE POInstall LEFT JOIN POSurvey ON POInstall.POID=POSurvey.POID" _
    & "POInstall.NewEQApDate = dateAddWeekday(POSurvey.TSRApCoDate, 1)" _
    & "POSurvey.TSRApCoDate Is Not Null And POInstall.NewEQApDate Is Null;"

    db.Execute sSQL

    End Function


    Also Same Error On db.Execute sSQL

  11. #11
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Debug.print your sql to immediate window, copy/paste into new query and run it. The offending portion will likely be highlighted. FWIW you didn't try what I posted. You added single quotes around the #'s.

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

    Firstly Thanks for all the assistance. @Micron I did try your code Sir/Ma'am, with double and single quotes and it kept giving me the same error. I obviously pasted your code with the single quotes after trying it. But I did try it (See Post #9).
    In any case

    Im not sure exactly what happened but I did the following:

    I cleared all my tables and then on my form I inserted a button to call up my original saved SQL query (using the query wizard). For some reason this worked on the "Click" event.
    I then checked the Macro Builder to see what Access did and just did the same on the AfterUpdate event and it seems to be working.

    Whats strange is that I did exactly this for a while (See Post #7)... So Im not exactly sure what I did differently this time. I honestly don't understand it.



    Once Again Thank You. Everyones assistance is always greatly appreciated

  13. #13
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Glad you solved it. I posted this dateAddWeekday(#" & [Forms]![POSurvey]![TSRApCoDate] & "#,1)
    in an attempt to wrap date delimiters around what I figured was a date, as in
    = dateAddWeekday(#01/01/2017#,1) whereas what you show you tried was

    "SET POInstall.NewEQApDate = dateAddWeekday(#' & [Forms]![POSurvey]![TSRApCoDate] & '#,1)" & _
    Single quotes would not work there, plus you wrapped the entire line in double quotes, which is wrong. I should have used the line continuation characters too so that you'd see

    "SET POInstall.NewEQApDate = dateAddWeekday(#" & [Forms]![POSurvey]![TSRApCoDate] & "#,1)" & _

    Note the sections between double quotes wrap the form control reference in whatever is on the left and right. So it should translate to

    SET POInstall.NewEQApDate = dateAddWeekday(#01/01/2017#,1)
    I'll try not to be so lazy next time.

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

    Thank you for the reply. Even though I solved it another way, I want to learn how we would get this to work. I have uploaded a small Notepad++ file containing the variations of the code I used.

    Im not sure I understand the difference between

    "SET POInstall.NewEQApDate = dateAddWeekday(#' & [Forms]![POSurvey]![TSRApCoDate] & '#,1)" & _


    "SET POInstall.NewEQApDate = dateAddWeekday(#" & [Forms]![POSurvey]![TSRApCoDate] & "#,1)" & _

    Yes there is the issue in the single quotes but I also tried it with the double quotes exactly as you have it and it still threw out an error.

    I would really like to learn as Im sure this will definitely come in handy in the future, so please bear with me.NewPlanDate.zip

    Thank You

  15. #15
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    The difference between those two is easy to see if you do the following with your form open and a date in the control;

    Code:
    Dim sSql as String
    
    sSql= "SET POInstall.NewEQApDate = dateAddWeekday(#' & [Forms]![POSurvey]![TSRApCoDate] & '#,1)"
    Msgbox sSql
    
    sSql= "SET POInstall.NewEQApDate = dateAddWeekday(#" & [Forms]![POSurvey]![TSRApCoDate] & "#,1)"
    Msgbox sSql

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Text Box In Form Not Updating Table Field
    By dgarber in forum Forms
    Replies: 8
    Last Post: 11-22-2017, 03:15 AM
  2. Replies: 8
    Last Post: 02-07-2017, 09:58 AM
  3. Replies: 2
    Last Post: 07-12-2016, 10:57 PM
  4. Replies: 1
    Last Post: 04-26-2013, 05:30 PM
  5. Updating Table field from Form
    By Kunuk in forum Access
    Replies: 0
    Last Post: 02-26-2009, 11:41 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