Results 1 to 11 of 11
  1. #1
    jdebiso is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    6

    Automatic update query

    Hi!



    I am not sure if this goes here or not so please tell me if it doesn't. I need to make a query run automatically to mark classes full as people register for them. My though was to just make a form that refreshes and executes the query but the query always asks for OK, etc. Any suggestions on how I could accomplish this? The SQL view of the query is below.

    UPDATE (qryqrybaseforanalysis LEFT JOIN Classes ON qryqrybaseforanalysis.BadgeID = Classes.BadgeID) LEFT JOIN CampSessions ON qryqrybaseforanalysis.SessionID = CampSessions.SessionID SET Classes.[Full] = 1
    WHERE (((Classes.Full)<>1) AND (([maxscouts]-[ScoutsRegistered])<=([maxscouts]*0.2)));



    Thanks in advance!
    Joe

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, the "Full" field is essentially a calculated field, thus normalization rules would suggest it not be saved. That said, you can get around the warnings:

    http://www.baldyweb.com/SQLWarnings.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jdebiso is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    6
    I did try that with this code and got an error. "Operation Must Use An Updateable Query"

    Dim stDocName As String
    stDocName = "qryAutoCloseCLasses"
    DoCmd.SetWarnings False
    DoCmd.OpenQuery stDocName, acNormal, acEdit

    Quote Originally Posted by pbaldy View Post
    Well, the "Full" field is essentially a calculated field, thus normalization rules would suggest it not be saved. That said, you can get around the warnings:

    http://www.baldyweb.com/SQLWarnings.htm

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Oh, I got the impression that the query worked, it was just the warnings that were a problem. Have you tried the Execute method?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    jdebiso is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    6
    I'm actually trying that now ut I dont seem to be able to get it to work. Is it possible for youto give me and example? I tried this but it didnt work...

    db.Execute "qryAutoCloseCLasses"

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try

    CurrentDb.Execute "qryAutoCloseCLasses"

    but it would only work if the query works when run from design view (using the exclamation point to actually execute it).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    jdebiso is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    6
    I can execute the query in design mode just fine its when I try from VBA that I get the message "Operation must use an updateable query".

    Quote Originally Posted by pbaldy View Post
    Try

    CurrentDb.Execute "qryAutoCloseCLasses"

    but it would only work if the query works when run from design view (using the exclamation point to actually execute it).

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    jdebiso is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    6
    That would be difficult it is only a front end to a SQL back end. I am going to try and write a view in SQL server to do this and see if that works.

    Quote Originally Posted by pbaldy View Post
    Can you attach the db here?

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I understand. When it works, you're using the "Run" icon from design view, not the "View" icon, right?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    jdebiso is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    6
    You are Correct.

    Quote Originally Posted by pbaldy View Post
    I understand. When it works, you're using the "Run" icon from design view, not the "View" icon, right?

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

Similar Threads

  1. Automatic report update
    By gsucin in forum Reports
    Replies: 4
    Last Post: 10-27-2016, 01:04 PM
  2. Replies: 7
    Last Post: 12-09-2014, 12:24 PM
  3. Replies: 1
    Last Post: 04-08-2014, 09:35 AM
  4. Replies: 3
    Last Post: 09-13-2012, 08:18 AM
  5. Automatic Update Feature of Forms?
    By mkc80 in forum Access
    Replies: 2
    Last Post: 05-18-2012, 08:10 AM

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