Results 1 to 6 of 6

Help with designing a form to sign in and out items to people (like a library)

  1. #1
    Pinchy is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    2

    Help with designing a form to sign in and out items to people (like a library)

    Hi anyone that can help


    I'm looking at just using a simple access database with a barcode scanner to assign gear to people. I need a way to assign items to people. (For tracking)




    I only have 3 tables - inventory, staff and a register table which links the 2 to assign items to staff. (using a PK and FKs).


    The barcode scanner can scan a name and then an item which creates a record. The way it works at the moment I need to scan a name for each item and repeat.


    I would like it so i can scan (or select) a name. and then keep scanning items assigning them to that person. Until I select complete or similar way to finish.
    This should create multiple records assigning each item to that person.


    I have looked at multiple different ways online to achieve this from copying text values to temporary tables. Seems that either way i need to write some SQL code and I seem to get stuck.


    Is there an easy way to achieve with access? Or the best way to go about this feature.

    Cheers!


    I'm looking at just using a simple access database with a barcode scanner to assign gear to people. At the moment we dont really have a system in place to manage and track gear.


    I only have 3 tables - inventory, staff and a register table which links the 2 to assign items to staff. (using a PK and FKs).


    The barcode scanner can scan a name and then an item which creates a record. The way it works at the moment I need to scan a name for each item and repeat.


    I would like it so i can scan (or select) a name. and then keep scanning items assigning them to that person. Until I select complete or similar way to finish.
    This should create multiple records assigning each item to that person.


    I have looked at multiple different ways online to achieve this from copying text values to temporary tables. Seems that either way i need to write some SQL code.


    Do you think this is an easy thing to achieve with access? I know its not the best DB software but if I can set something up that doesn't take much time be great. I can send over the database example if that helps - Not much to it at this stage though.


    Cheers. Paul

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,125
    Might take a look at the MS Lending Library database template.

    Yes, code will be needed. One approach is to set DefaultValue property of control you want value to carry forward into next new record. http://www.accessforums.net/showthread.php?t=52168
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  3. #3
    Pinchy is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    2
    Cheers mate Iíll take a look.

  4. #4
    Join Date
    Apr 2017
    Posts
    783
    tblItems: ItemID, ItemName, ...;
    tblUsers: UserID, ForeName, LastName (I used table name tblUsers, because additional to staff you'll have there also users like "Storage" or "Library", "archived item", etc.;
    tblTransactions: TransactID, TransactDate, ItemID, UserID.

    You have a main single form based on tblUsers (fUser), where you can select existing user, or add a new one.
    You create a continuous form based on tblTransactions (fTransactions).
    You insert fTransactions as subform (sfTransactions) into fUser. Usually (at least when linking indexes in both tables have same name - UserID) both forms will be linked automatically. When not, then you have to manage links manually.
    In form fTransactions, you set the default value for control (NB! For control, not for table field!) with TransactDate as source to "=Today()" - or "=Now()" when there may be several movements for same item in same day. It is for you to decide, do you allow the user to edit the date/datetime field after the entry is created.

    Whenever you register a new movement (manually or using code reader or whatever) in subform, ths entry will automatically linked with user in main form. And the registering time is entered atomatically too.

    NB! UserID control in fTransactions must be at least disabled, or even better set invisible. To allow users to edit this is a sure way to disaster!

  5. #5
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,281
    I have the system grab the users ID and hold it in a locked field:
    Code:
    sub Form_Load()
    txtUser = Environ("Username")      'get user id
    end sub
    then all queries , checkouts, etc, records their ID.
    my tLog table has a Date field with default value = NOW()

  6. #6
    Join Date
    Apr 2017
    Posts
    783
    What happens when the used gives the item back? By setup I described you select storage/library as user, and register the movement. The same with items you want to scrap/write off. You select "archived device" as user, and register the movement.

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

Similar Threads

  1. Replies: 2
    Last Post: 07-16-2015, 11:26 AM
  2. Replies: 6
    Last Post: 06-14-2015, 05:31 PM
  3. Replies: 4
    Last Post: 06-26-2013, 10:48 AM
  4. Need help designing traffic sign inventory DB
    By fredep57 in forum Database Design
    Replies: 3
    Last Post: 12-30-2011, 03:05 PM
  5. Replies: 35
    Last Post: 09-19-2011, 09:13 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
  •  
Tech Forums: Microsoft Office Forums