Results 1 to 7 of 7
  1. #1
    WarrenG is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    6

    Change Order Status (combo box) to "Closed" based on quantity received.

    This sounds easy to me, but I can't make it work. I have found topics that sound similar, but are not really about the same thing.
    I have an Order Entry form that ties to my main Order table. On the form I have a subform tied to the subrecords of the Order table and stored on a Receiving Table.
    In the subform I have fields for a receive date and received quantity. In the Subform header I have a calculated field that sums up all the received quantities. (subform and form linked via Order ID).

    On the main form I have a Qty field which reflects the total quantity on the order. I have a combo box on the Main Form that stores the selection on the Order table. I want to comapare the Total Received (calculated field in the subform) to the Qty (total quantity ordered field on main form). If they are equal, change the combo box value to "Closed".

    Any help will be greatly appreciated. I had some help from that theDBguy(thanks much) on another forum, and thought we had it licked but I was mistaken and no one has returned to my thread.. I most have been looking at the wrong field (one we made for testing) because I thought it was working. I can not get the results to populate my Order Status combo Box.

    Tested it out with a field to make sure we could get it to recognize the calculated field in the subform: =IIf([Qty]=[Receiving Table Sub].[Form]![Total Received], "Closed", [Order Status]) That worked so I made an On Current event :


    If Me.[Order Status] & "" = "" Then
    If [Qty]=[Receiving Table Sub].[Form]![Total Received] Then
    Me.[Order Status] = "Closed"
    End If
    End If
    This did not work. I want this field to stay the default value of "Open" if the Total Received is less than QTY ordered. The current event did Not change my Order Status combo box value.

    Thanks in advance for any help.

  2. #2
    WarrenG is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    6
    Ps,
    I know it is usually not a good idea to store something that can be calculated in a table but anytime I want to run a report either based on order status, or even just including order status, I would have to run through my query that totals up the multiple receipt lines.
    I do want the [Order]![Order Status] field to remain a field that can be updated manualy as well. For instance, If I manage to automate closing a report based on received quantity that would be great, but I also want to be able to go to that field and change to "Canceled" or "On Hold" as needed. I need to be able to show other status as Open, Closed, Canceled or on hold.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,916
    The code executes only if Order Status is empty. If it already has value, the code won't run and won't change value. Why bother checking for empty? A one-line IIf could serve.

    Me![Order Status] = IIf(Me!Qty = Me.[Receiving Table Sub].[Form]![Total Received], "Closed", [Order Status])

    However, not sure Current event of main form is best place for this code. It won't run if the subform records are edited or new records added.
    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.

  4. #4
    WarrenG is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    6
    Correct about the not needing or wanting to search for blank, I did have a version without that and copied the wrong one.
    I couldn't get your code to work. I will try in different Events.

    Thanks for the reply.

  5. #5
    WarrenG is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    6
    I have tried that code all over, and still not updating the Order Status.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,916
    If you want to provide db for analysis, follow instructions at bottom of my post.
    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.

  7. #7
    WarrenG is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    6
    I am clicking upload, but it does not appear in the window so I can drag to attach. No error either. I will try some more in a little while.

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

Similar Threads

  1. Change Sort Order of Combo Box "On-The-Fly"
    By JoeM in forum Programming
    Replies: 6
    Last Post: 09-26-2013, 06:50 PM
  2. Replies: 1
    Last Post: 10-26-2012, 12:52 PM
  3. Replies: 6
    Last Post: 03-10-2011, 11:31 AM
  4. Replies: 1
    Last Post: 10-19-2009, 02:37 AM
  5. Replies: 0
    Last Post: 09-25-2008, 12:19 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