+ Reply to Thread
Results 1 to 1 of 1

Custom form to search a table and automatically change fields

  1. #1
    Registered User
    Join Date
    10-26-2021
    Location
    Brisbane, Australia
    MS-Off Ver
    2109
    Posts
    1

    Question Custom form to search a table and automatically change fields

    Hi there, I was wondering if someone could provide assistance or point me in the right direction.

    Our head office makes us do a stocktake of onsite assets once a year. They spit out a spreadsheet from their asset management software, and we're meant to check off the assets off the list (spreadsheet). Once the assets have been sighted and marked off on the spreadsheet, the spreadsheet gets uploaded back up to the asset management software at head office. Assets are barcoded with their "Asset ID" number. I can use the inbuilt Form in excel and go to Criteria and search and bring up an asset by scanning an asset barcode, then click (or tab) to the relevant field and mark it off, but having to move between fields constantly, it becomes quite time consuming. The other issue is assets are mostly mobile and so we have to also record their current location. So, with a barcode on the asset, and a printout of barcodes for locations (a "Facility" code in our case), and printed barcode for the sighted "Status", and printed barcode for TAB (to move to next field), it turns into a lot of beeping/scans to record the sighting of one asset. It's doable but does become tedious.

    I was hoping this could be improved, and I was thinking the following would improve things:

    - Have a custom form that has a separate search field that looks up the table's "Asset ID" column, and then displays all the asset details, similar to the inbuilt Form. It returns focus to the search field, ready to scan the next asset.

    - Upon retrieving the asset details, it automatically changes the "Status" field to "S" (which is the code for the asset having been sighted)

    - And what would be huge, would be somehow, set the "Facility" (location) field for subsequent asset searches to be the same as the current search. I hope that makes sense. So, if I walk into a room and start scanning 20 assets in there, I only need to scan a "Facility" location code once.


    If there is a better way to do this, I'd be happy to hear it.

    I have attached a sample xlsx file which might help make more sense.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Live Search and Auto Populate Fields on User Form
    By jwood1650 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-06-2020, 04:39 PM
  2. Add custom fields to a table to simulate a pivot table - Power Query
    By nathalielesperance in forum Excel General
    Replies: 2
    Last Post: 10-25-2018, 06:44 AM
  3. Change Pivot Table Fields Month by Month Automatically
    By phoebeyanan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-16-2017, 04:03 PM
  4. Can I automatically hide/show text fields in a form?
    By acamarsv in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-14-2015, 10:49 AM
  5. Create a Excel Search Form that populates fields
    By RevJeff in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-12-2012, 09:51 AM
  6. Automatically complete fields on a form
    By piercer in forum Access Tables & Databases
    Replies: 1
    Last Post: 08-17-2010, 05:40 PM
  7. Filling form fields via worksheet dropdowns, user update via form, change form color
    By Demosthenes&Locke in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-11-2010, 08:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1