How to choose from list of serial numbers for Laptops

[shod90]

Dear all,
I have an small inventory system that control our IT stock.


I need to insert laptops with their serial numbers , and when i issue a laptop to a user i have to choose item which like "HP elite book 840 G5" then choose one of the serials available and when i choose it it disappears from the list , Again when the user return the laptop it appears again in the list of serials.
Attached herewith the database schema which i am sure it needs to be edited or should be replaced with a new one .

[Bob Fitz]

I would suggest that you will need at least the following tables.
tblAssets, tblUsers, tblAssetsUsed.

Use tblAssets for details of asset.
Use tblUsers for details of users.
Use tblAssetsUsed as a junction table. Each record will keep the Pk value of the user that has the asset and it will keep the PK value of the asset that is being used.

You will probably need other fields in the first two tables but only you will know exactly what details you require.
See the db attached.
If you need more help post back. I'm always willing to help if I can and the bonus is that I'm very very cheap. I'm FREE

[moke123]

heres a simple example showing how to move the items between the lists
note the query - "QryExclude" and its use in the rowsource of lstInventory.

[ArviLaanemets]

I have designed an devices management app which works similar with what you want.

I have a table tblDevices: DeviceID, DevGroup, ...;
I have a table tblDeviceGroups: DevGroup, GroupName, ..., CurrUserID, CurrUserLevel, GroupNo (GroupNo is used to order groups e.g. in combos, CurrUserID and CurrUserLevel are updated whenever device user changes - generally not best practice, but here they are needed);
I have a table tblUsers: UserID, UserLevel, ForeName, LastName, ... (UserLevel is smallint value used to group users. E.g. levels 1-9 are for various user classes [employees, group users like departments, non-employees, etc.] in one factory, 11-19 classes for other other factory, ets. Levels 90-99 are specific application level users, like 99 - 'archived device, 91 - 'free device', 92 - 'broken device', 93 - 'in repair', etc.)
I have a table tblUserLevels: SelectionNo, SelectionName;
I have a table tblTransactions:TransactID, TransactDate, DeviceID, UseID, ...;

The main form for devices management is based on table tblDeviceGroups. Selecting/Changing of active DeviceGroup is possible only using a combo box at top of form. At top of form is also another combo with tblUserLevels as source.

Main form contains a single type subform based on tblDevices. The subform is linked with main form through DevGroup - i.e. only devices of specific group are displayed. The other combo on main form determines the filter set on CurrUserLevel for devices subform. The initial setting for filter is 'CurrUserLevel < 99', i.e all devices not archived are displayed. Changing the selection in combo allows the user to display all devices used in specific factory, or all free devices, or devices used in specific country, etc.

The Devices form has his own (continuous) subform, based on tblTransactions. Devices and Transactions forms are linked through DeviceID. There user can see the whole history of all movements of active device, and change the user for active device adding another entry into tblTransactions.

On devices form and on its transactions subform, all changes except record selection controls are disabled. A button in Devices form allows to enable the editing of current record in subform. When in editing mode, another click of button checks integrity of data, updates CurrUserID and CurrUserLevel for device, saves all changes, and then sets controls and subform disabled again. Leaving the record in Devices form when in editing mode otherwise, discards all changes and sets back disabled setting for controls and subform.

[shod90]

I would suggest that you will need at least the following tables.
tblAssets, tblUsers, tblAssetsUsed.

Use tblAssets for details of asset.
Use tblUsers for details of users.
Use tblAssetsUsed as a junction table. Each record will keep the Pk value of the user that has the asset and it will keep the PK value of the asset that is being used.

You will probably need other fields in the first two tables but only you will know exactly what details you require.
See the db attached.
If you need more help post back. I'm always willing to help if I can and the bonus is that I'm very very cheap. I'm FREE

Thanks for your fast response , But what if i want to add a serial number for each item ? I Should create another table for serial numbers ? And also i want when this item is out of my inventory should be not visible while choosing .

[Bob Fitz]

Thanks for your fast response , But what if i want to add a serial number for each item ? I Should create another table for serial numbers ? And also i want when this item is out of my inventory should be not visible while choosing .

No. You don't need a table for serial numbers. They would be a field in tblAssets. Each asset can only have the one serial number.

[ArviLaanemets]

The only thing you need to register an free asset for user is a list of free assets of given group.

In case you have an asset registered to previous user, and you need to register it to new user, then the easiest way is do like we did it with our app: we had a label with DeviceID printed on it on every device . We read the ID from label, navigated to this device in Devices form, and added a new entry with new user into Transactions subform for this device.

We didn't use serial numbers as DeviceID's because:
1. Some devices didn't have serial numbers;
2. Sometimes you had to disassemble the device partially to get access to serial number;
3. Sometimes a device was installed into some rack, or mounted somewhere, and serial number was not accessible easily;
4. There is always a possibility that devices from different producers may have same serial numbers.