Results 1 to 5 of 5
  1. #1
    tuniwes is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    43

    VBA to filter an update query by Autonumber

    Hi

    I can usually get by and work things out for myself after searching through this forum but I'm struggling with this one.

    I'm used to launching a sub-report from a fields event button on a report to launch another report filtered by the current record value

    DoCmd.OpenReport "kit master", acViewReport, , " project='" & Me!Project & "'" would be typical....

    I have a simple update query I want to run from an event button at record level on my report

    DoCmd.OpenQuery "copy of pkg" where I want to update only the record where the ID autonumber matches the current ID Autonumber record on the report

    I cant get this to work and would be grateful to anybody who can point me in the right direction with the code/syntax to do this

    Thanks

    Ian




  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What does 'cant get this to work' mean - error message, wrong results, nothing happens?

    Post your query SQL statement.
    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
    tuniwes is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    43
    Hi June

    This is what I tried
    DoCmd.OpenQuery "copy of pkg", " id='" & Me!ID & "'"

    This returns a data mismatch error and I get the feeling I'm "barking up the wrong tree"

    This is the SQL from the query but I only want it to update the ID number that matches the record clicked in the report

    UPDATE pkg SET pkg.Qty = [qty]-1, pkg.done = [done]+1, pkg.live = IIf([qty]=0,0,1);

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    If ID is a number type field, don't use apostrophe delimiters.

    Can't pass criteria to a query object that way.

    You could execute SQL statement in VBA so instead of running action query object:

    CurrentDb.Execute "UPDATE pkg SET pkg.Qty = [qty]-1, pkg.done = [done]+1, pkg.live = IIf([qty]=0,0,1) WHERE ID = " & Me!ID

    However, saving calculated data is usually not necessary nor advisable, especially aggregate data. Aggregate data should be calculated when needed. Review http://allenbrowne.com/AppInventory.html
    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.

  5. #5
    tuniwes is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    43
    Perfect!

    Thanks so much for helping me out and broadening my knowledge.

    It's for a live graphical simulation of am 8 stage single piece flow line on the factory floor controlling the sequence of work at each work station.

    You just got me past the very frustrating position when you know what you want to do, suspect the solution is simple but just cant work it out.

    Been here many times before and sometimes it's a good way to learn

    Thanks

    Ian

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

Similar Threads

  1. Replies: 2
    Last Post: 02-20-2017, 11:28 PM
  2. Replies: 6
    Last Post: 05-04-2015, 02:18 PM
  3. Replies: 4
    Last Post: 11-21-2013, 05:06 PM
  4. Replies: 10
    Last Post: 01-29-2013, 08:59 AM
  5. Filter on Autonumber
    By jgelpi16 in forum Programming
    Replies: 3
    Last Post: 01-24-2011, 10:46 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