Results 1 to 8 of 8

Macro to Search Lowest Value

  1. #1
    Larcen60 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2019
    Posts
    4

    Macro to Search Lowest Value

    Hi everyone,

    I'm looking to make a workload balancing tool and I'm having some difficulties. I'm relatively new to Access so any help would be greatly appreciated!

    I'm looking to make a database that has a form that has 2 fields, one that has a button that says "Assign Work" and another that has a field that displays the persons name to assign the work to. When you click the button it on the form it displays an input box that says "Amount of work to be assigned". It then concurrently runs a query to the database to look for the lowest value in the "Work Assigned" field. It then returns the name of the person that has the lowest value in the "Work Assigned" field in the box on the form. The user then inputs a number into the input box, and hits "Assign Work". It adds that value to the "Work Assigned" field in the database for that person who is displayed on the form.

    The only additional wrinkle is that on the database there would be a Y/N button that says "Exclude from Work". If that button is checked, the macro would ignore that person and look for the person with the next lowest value to assign work to.



    Currently, I have the actual database made and a macro for the button that makes the input box. I've done research on this site, and I'm still stumped on how to move forward with this. If anyone has any ideas or tips on how I could accomplish this, I would be incredibly grateful!

    Thanks in advance!


    Click image for larger version. 

Name:	Table.jpg 
Views:	15 
Size:	21.5 KB 
ID:	39616Click image for larger version. 

Name:	Form.jpg 
Views:	15 
Size:	11.1 KB 
ID:	39617

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,251
    You have a table named "Table1"?

    Include in the search criteria: [Exclude from Work]=False

    Post your code.

    Advise not to use spaces in naming convention.
    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
    Larcen60 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    4
    Thanks for the response!

    The screenshots are just some examples I made quick for a visual example of what I'm looking to accomplish. I mocked that up fast to remove any sensitive information on the database I'm currently working on, that's why one is labeled "Table1".

    The only code I have so far is just to make an inputbox:

    Code:
    Private Sub Command1_Click()
    
    Dim Message As String
    Dim Title As String
    Dim Work As String
    
    Message = "Amount of work to assign"
    Title = "Assign work"
    
    Work = InputBox(Message, Title)
    
    End Sub
    Thanks again for the help!

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,251
    First of all, that is not a macro, it is VBA. Macros in Access are very different.

    Advise not to use spaces in naming convention.

    Advise to avoid InputBox because it is difficult to validate user input - requires more code. Input to control on form.

    Consider this approach:

    strWorker = DLookup("EmployeeID", "Table1", "ExcludeFromWork=False AND WorkCount=" & DMin("WorkCount", "Table1", "ExcludeFromWork=False"))

    Alternative would involve a query.

    SELECT TOP 1 EmployeeID, WorkCount FROM Table1 WHERE ExcludeFromWork=False ORDER BY WorkCount;

    Save that query and then use DLookup on it: DLookup("EmployeeID", "queryname")


    What if multiple people match the same low WorkCount?

    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
    Larcen60 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    4
    Thanks for your help! I really appreciate it. I've implemented a lot of your suggestions, and it truly helped. I took out the spaces in the naming convention and I took out the input box code and I made it just an input on the actual form. The last piece of the puzzle I'm trying to figure out is how I get the amount of work added from the form that the user inputs to add to the workers work total.

    Right now my code looks like (For button 1):

    Code:
    Private Sub Command1_Click()
    
    Dim strWorker As String
    
    strWorker = DLookup("EmployeeName", "Table1", "ExcludeFromWork=False AND WorkCount=" & DMin("WorkCount", "Table1", "ExcludeFromRotation=False"))
    
    
    Text1.SetFocus
    Text1 = strWorker
    Text1.SelStart = 0
    
    End Sub
    Any tips you can give me on how to get the number inputted from the user into the workers total count would be great. Thanks again for all your help!

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,251
    What table do you want to save inputs into? Do you want to create a new record in Table1? Why don't you bind form to table and bind textboxes to fields? Make sure focus is on
    new record row. User input and code will populate this new record.
    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
    Larcen60 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    4
    I don't want to add a new record, I want to add the number the user inputs to the existing record to keep a running total. Thanks very much!

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,251
    Running total is data aggregation. Aggregate data should not be saved to table, should calculate when needed. Enter transaction records then calculate balance.

    But if you must, then can run an UPDATE action SQL.

    This runs risk of data getting corrupted if user accidentally runs UPDATE more than once. There is no way to determine if saved value is correct because it is not based on data - there are no supporting records.
    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.

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

Similar Threads

  1. Replies: 2
    Last Post: 05-17-2019, 07:00 AM
  2. Error with entry search macro
    By AndrewD in forum Macros
    Replies: 1
    Last Post: 12-08-2015, 01:34 PM
  3. Replies: 3
    Last Post: 01-18-2015, 01:29 AM
  4. Replies: 7
    Last Post: 06-08-2014, 03:11 PM
  5. Macro Search Form : BrowseTo
    By char in forum Access
    Replies: 0
    Last Post: 11-19-2012, 04: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
  •  
Tech Forums: Microsoft Office Forums