Results 1 to 6 of 6
  1. #1
    samos1023 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    18

    form for inventory control

    I have a db I wrote for tracking inventory. I have a form that allows the user to scan or type in a serial number, I then use a query to find the item in the db. I have a subform that shows the item/items with additional details about the item. Once the item is found and the sub form is populated the user can check a yes/no field for that item to track that is has been removed from inventory. This form works fine, the users have been using it for months now. Their request is to speed this process up. They have to scan each item, then check the box, then scan the next item, check the box ...... This can be time consuming. They would like a batch update feature. So what they want is to scan an item, then it show up in the subform, scan another time, it then shows up in the subform, on on and on. After adding as many items as they like, they want to push a button to transfer all items out of stock(ie check the yes no tick box for all items scanned). So my current query runs on the inventory table and returns the matching serial number record. Now I know that I could write Some VBA code that just keeps adding an OR to the where clause with additional serial numbers. This seems to be the fastest way to make this change. However I would assume there is some limit to how long an SQL statement can be, or how many ORs that can be in a where clause. If not then I have to assume if they scanned in hundreds of items at once and the SQL statement just kept getting larger and larger that may be a problem. So my question is more of a best practice question as as apposed to how to question. Is contentiously adding an OR clause to the SQL statement like that OK? Or does anyone have other ideas on how to do something like this? Thank You

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Yes, there are limits.

    VBA code could physically move to each record of the subform and check the yes/no field.

    The more 'user friendly' the more code.
    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
    samos1023 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    18
    So how could i filter the query I have so that the subform shows multiple items each with different SN. Right now only one record shows up for each serial number. This is the visual que to the end user. The only way I know of is by having a query with all of the serial numbers i want to display, ie the long where clause.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Sorry, I missed the part about filtering the subform to the single SN.

    So my question is, does user have to make a decision as to whether or not this scanned item should be 'removed' or should that be an automatic response when the item is scanned?
    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
    samos1023 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    18
    I have not determined that yet, they may want to check the tick manually, they may want to automate it, thats not my problem. My problem is creating a query each time they scan a new serial number. So the first item would create one option in the where clause. The second scan would create 2,(the first SN and the second SN), and so on and so on. At some point the where clause could have 100 Or's in it. That does not seem like the most effective way to get this info. What other ways could I use to query the data to avoid this> or is this my only option? I want my subform to show a recordset of all items that have been scanned since the form opened.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If the form is opened with the Data Entry property set to Yes then the form will display only records created during that session.

    Otherwise, base the filter on some common criteria such as some field Is Null or a date of creation or some combination of parameters.

    Another approach is to write the inputs to a 'temp' table as well as the main table and use 'temp' table as filter criteria. The SQL would look like:

    SELECT * FROM maintable WHERE SN IN (SELECT SN FROM temptable);

    The 'temp' table is actually permanent, the data is temporary, records would be deleted when the session ends. It would have to be in the frontend of split db.
    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. Inventory Control DB
    By nachesebro in forum Database Design
    Replies: 3
    Last Post: 10-04-2016, 02:15 AM
  2. Stock\inventory Control
    By tweety in forum Queries
    Replies: 51
    Last Post: 03-31-2013, 11:47 AM
  3. Inventory control
    By Mclaren in forum Programming
    Replies: 11
    Last Post: 03-13-2012, 03:15 AM
  4. DB For Inventory Control
    By blueraincoat in forum Database Design
    Replies: 4
    Last Post: 03-09-2011, 08:02 PM
  5. Inventory Control DB (Beginner)
    By Clayton252 in forum Database Design
    Replies: 1
    Last Post: 10-06-2010, 06: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
  •  
Other Forums: Microsoft Office Forums