Results 1 to 6 of 6
  1. #1
    Wingnut74 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    10

    Using a query to update stock levels by looping through all the records in a subform


    Hi,
    I have created a query to update stock levels when a worksheet has been completed. I can run the query and click on each individual record in the subform and the stock levels update correctly. What I was trying to do was to loop through all the records in the subform and run the query each time. The worksheet form has all the details of the work carried out with a subform with all the parts used.
    Not sure if this is the best way to achieve this but would happily accept any advice.

    The code I have been using is as follows:

    Code:
    Set rst = Forms!frmWorksheet.tblPartsUsedSubform.Form.RecordsetClone
    
    Do Until rst.EOF
        DoCmd.OpenQuery "qryStockWorkUpdate"
        rst.MoveNext
    Loop
    
     rst.Close
    Set rst = Nothing
    
    .

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Do you have a field in which you keep the calculated amount of a product available?
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Wingnut74 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    10
    Hi Bob,
    No, I am holding the stock levels in a table, which after a bit of research seems to be the wrong way of doing things. If you have any advice on how this would work or could point me in the right direction I would be most grateful.

    Many thanks

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by Wingnut74 View Post
    Hi Bob,
    No, I am holding the stock levels in a table, which after a bit of research seems to be the wrong way of doing things. If you have any advice on how this would work or could point me in the right direction I would be most grateful.

    Many thanks
    Hi Wingnut74
    Pleased to know that you've already discovered the error of your ways and that orange has already sent you off in the right direction.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    Wingnut74 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    10
    Thanks Bob, Orange
    I ill make the changes and see how I get on.

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

Similar Threads

  1. Automated marking off stock levels
    By bbdsbd in forum Access
    Replies: 1
    Last Post: 07-29-2015, 05:07 AM
  2. Creating a form to manage stock levels
    By CodLiverOil in forum Database Design
    Replies: 5
    Last Post: 01-22-2015, 06:49 AM
  3. Replies: 1
    Last Post: 05-13-2013, 05:01 AM
  4. Inventory stock levels
    By Sagrado in forum Access
    Replies: 1
    Last Post: 03-15-2012, 10:20 PM
  5. SQL Update stock Query HELP!!!
    By jordanturner in forum Queries
    Replies: 6
    Last Post: 09-06-2010, 10:34 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