A small (open source) Inventory Control application using Ms Access.

[Avelino Sampaio]

Hello everybody

Update: The final file is available for download from Reply #25.

It is not new that nowadays it is necessary to have a reasonable stock of components, both for our experiments and for eventual repairs.

Losing Control - As my inventory grew, I lost control. Buying components already in stock and wasting time in localization were the two factors that bothered me the most.

Using Microsoft Access – As I have a good experience with Microsoft Access VBA programming, I decided to create a small application for inventory control and thus solve my demand.

Sharing the app – I will share this little app with you. It will be open source.

Some images - I'll start by showing some images of the application, so you can analyze whether it will meet your demand.

Google translator – my English is very bad. I'm using the google translator. I apologize in advance if it becomes incomprehensible.

Image 1 – Application main screen. Note that I already have one thousand registered components. Locating the component (watch the arrow) quickly was my main mission with this project.



Image 2 – using the filter fields, I quickly reach the desired component.



Image 3 – I can print a list of all registered components or I can print a filtered list. Notice in the image that I filter the PICs. Image 4 shows the report resulting from the filtering.

[intabits]

I need to do something similar to manage the stuff I've got stored all over the place, and obtained from all over the place.

As well as the data you are keeping, I'll be adding:-
* A general category identifier (opamp transistor module switch hardware etc) to enable searching by general class type. Nothing very specific, but to help eliminate irrelevant items. (looks like you are doing something like this)
* A source or supplier identification
* A link to the invoice/packing list or other document for the order that included the item.
* Possibly the supplier's stock code for the item as well (or just use the document above)
* A reference number or other key to identify the order
* The physical item(s) will also be labelled with that reference number/key
* Notes & comments (what the hell did I order this for? etc)
* Links to datasheets, schematics, manuals or other useful information relating to the item, that can be opened with a click.

Whenever I buy stuff on eBay or online, I create a pdf or screen shot image of the order to serve as the document mentioned above. The supplier might provide this as well, but I keep both since the order capture may include information (such as images) that often are not in the supplier's document.

I have written a very crude form of such a system that is in use currently. It allows me to process activity/summary CSV files downloaded from Paypal (mostly for ebay orders) and then assign/connect an order document to the paypal order.

The Paypal files contain descriptions of the items in the order, so I can use my crude app to search them, and then click on a search result to open the PDF for the order. I can then see if it's what I was looking for, who I got it from and when, and what else I bought at the same time.

Making this work for all items from everywhere will be hard to impossible, since the required information is scattered about in many different formats. I may be able to get some joy out of extracting the data from the document PDFs, at least where many orders are in some consistent format... 

[Avelino Sampaio]

Intabits

Thank you for your participation. Thanks also for sharing your good ideas. Keeping everything cataloged as you plan will require a good deal of discipline.

Features – I have collected some of the main technical features of the BJT and Mosfet transistors. Creating these lists has been very helpful for me. Let's go to them:

Image 1 – I search for IRDF6218 and with the right mouse button I click to open the registration form for its features (image 2).

Image 3 – List of registered Mosfets.

Image 4 – Observe the applied filtering. Mosfets IRF, Channel N and Vds greater than 99v.

Image 5 – Report view, as applied filtering.

[intabits]

I may have misunderstood the purpose of your application, or at least part of it.

You seem to be maintaining detailed device specs and parameters in your database. That seems like a lot of work, not only if it's entered manually, but also because the app needs detailed screens and processing specific to each type of part. So for example, opamps will require dedicated screens and code that is completely different to the stuff that must also be maintained for BJT transistors, and so on for everything else.

My idea is simply a way to keep track of the stuff that I have, without having to physically search through boxes of packages to see what I've got. For specifications etc, clicking a link to the pdf is enough for me.

[Avelino Sampaio]

Intabits

Yes, I'm having a certain degree of work to register the features of each transistor, but I don't intend to go further in detailing other components.

Image 1 – Transistor listing form. With the right mouse button I can access the component's datasheet or open the features form (image 2).

Image 3 - Here we can also apply filter combinations and print the filtered list.

[Avelino Sampaio]

Inventory Write-off - I have recently entered the write-off inventory. Now I can print a report with the total amount spent on a given project.

Image 1 – Write-off control form.

Image 2 – I select the desired project in the filter field, to print the desired list.

Image 3 – Report with the total amount spent, referring to the selected project.

[Avelino Sampaio]

1- The application is divided into two files. The sysInventory_be.accdb (backend) file stores the tables and the sysInventory_v_1.accdb (frontend) file contains the forms, reports and codes used.

2 - You will receive a zipped file, containing the files mentioned above and a folder containing a few images of components. No installation will be required. Just unzip into a folder of your choice.

3 - The file you will be downloading contains my database. The goal here is for you to be able to proceed with preliminary tests. Later I will be providing the application, with the clean database, for your own use.

4 - For it to work, you must have Microsoft Access installed.

5 - Before running the application, it will be necessary to configure Microsoft Access, informing that the folder containing the files is trusted (trusted folder). Watch the video I found on youtube, showing how to proceed with this configuration.



6 - After configuring the folder as trusted, you can run the application sysInventory_v_1.accdb (fronted).

7 - The app is available to you at the link below.

[Renate]

It's been years since I've used MS Access and I was glad to say goodbye to it.
The most tedious part was making forms.
I presume that they have some way now that you just say, "Design a form for me"?

For some of your parameter fields you might want to use Null when there is no value specified?
Or not, Null is very contentious.

I like to put stuff in databases, anything that's SQL semi-compatible.
I use MS SQL Server, SQLite and ODBC on Excel files or CSV.
I use auto generated forms for data entry, I can't be bothered to mouse little boxes.
Ok, it does not have the cohesiveness of one application that contains everything.

[Avelino Sampaio]

Renate

yes, just use the "is null". Example:

SELECT *
FROM tblMosfets
WHERE Channel Is Null;

MSAccess backend supports up to 2GB. For this project, I can say that we can store more than 100,000 records without any performance degradation. The secret here is not to store the images in the database. Images are stored in a folder and then linked.

[Renate]

No, I meant that I saw a lot of parameters that said zero.
It might be more helpful to have that as blank if unknown/unspecified.
Of course, having "null" displayed is even less helpful!

Images are stored in a folder and then linked.

Saving images internally as blobs has advantages (& disadvantages).

I just think that MS Access is a dead end tying you to Microsoft for the rest of your life.
Even using MS SQL Server doesn't tie you down as much if you don't use a lot of procedures/functions.
You can migrate to MySql, PostGres, SqlLite (which I'll admit is awfully cheesy).

[Avelino Sampaio]

Renate

I chose Ms Access because it is part of the MS Office package. I think most people here subscribe to the MS Office package. Another problem that weighed heavily on my decision was that the user didn't need to install anything. I think this brings more security, for the user who wants to try the offered application.

[Avelino Sampaio]

Hi

The application offered works on Ms Access 2010, 2013, 2016 and 365 versions.

[Renate]

Ok, but Access used to be part of MS Office Professional.
They changed the naming of all that stuff, but I don't think that most people who have Office have Access.
I don't know how many (or why) people here would even have Office.

Now, if you had done this in LibreOffice Base, that's free.
I've never tried LibreOffice Base. I don't know what they have for forms/navigation.

[Zero999]

I certainly don't have MS Office. This would be more accessible if it were done with LibreOffice Base.

[Avelino Sampaio]

For those who do not use the MS Office package, you have the option of downloading the reduced and free version of MS Access.

The Microsoft Access 2016 Runtime enables you to distribute Access 2016 applications to users who do not have the full version of Access 2016 installed on their computers.

https://www.microsoft.com/en-us/download/details.aspx?id=50040

[kripton2035]

I certainly don't have MS Office. This would be more accessible if it were done with LibreOffice Base.

and it would be even more accessible if it were done using sqlite database ...

[Zero999]

For those who do not use the MS Office package, you have the option of downloading the reduced and free version of MS Access.

The Microsoft Access 2016 Runtime enables you to distribute Access 2016 applications to users who do not have the full version of Access 2016 installed on their computers.

https://www.microsoft.com/en-us/download/details.aspx?id=50040

No good, if you don't use Windows though.

[Bassman59]

I certainly don't have MS Office. This would be more accessible if it were done with LibreOffice Base.

and it would be even more accessible if it were done using sqlite database ...

I applaud this guy's work.

If you want it done in sqlite or anything else, feel free to have at it.

[Avelino Sampaio]

Hi

I decided to implement the "Entry Inventory".

Image 1: Notice that in this example I'm checking in 6 more units from the LM311. I adopted the Weighted Value as a rule, to update the Unit Value of the part. See in this example that the LM311 has 4 units in inventory at a unit value of 1.57 and I am entering 6 units with a unit value of 2.00. The app will calculate as follows:

((4 * 1.57) + (6 * 2.00)) / (4 + 6)  :::> 1.83

Image 2: Look at the updated value of the LM311, with the weighted value (1.83).

Image 3: Note here that I use the supplier filter field, to return only with digi-key. Now just print the filtered listing

Image 4: report resulting from filtering

[Avelino Sampaio]

Hi

The images saved in the Images folder follow two rules: 1st - JPG or PNG extension / 2nd - The component name must be exactly the same as the identification name, registered in the stock.

[Zero999]

Hi

The images saved in the Images folder follow two rules: 1st - JPG or PNG extension / 2nd - The component name must be exactly the same as the identification name, registered in the stock.

How about changing it so multiple components can use the same image?

There are a huge number of components with exactly the same pin-out. Your screenshot demonstrates that very nicely: LM358, LM393, TL072, NE5532 are all identical.

One way to do this, without modifying your code, is to have one image file, say dual op-amp.png, in a master directory and use symbolic links to refer to it. You could create a nice GUI to make this easier.

[Avelino Sampaio]

Zero999

Thanks for the tip.

Image 1 and Image 2: My components are stored in drawers, pots, cases, boxes, ....

Image 3: I have many used parts. Sorting, storing and registering is a very boring task. How to have control of it all, without the help of an application?

Tell me how you handle this issue. What program do you use? What method do you use? How has this bothered you?

[Avelino Sampaio]

Hi

Image1 : the backup system saves the backend (sysInventory_be.accdb) to the location you want (pendrive, external hd, ...). The default location saves the file in the folder where the front-end is located.

Image 2: If you have Winrar installed and want to opt for compression, just check the option.

Image 3: Notice how the name of the compressed file looks in the backup folder.

[Avelino Sampaio]

Hi

Image 1: One more feature included. It will be possible to establish the minimum value for each registered item.

Image 2: The main forms will indicate how many items are below the minimum. On the ribbon you will have access to the list of items below the minimum

Image 3: Report of items that are below the minimum.

[Avelino Sampaio]

Hi

Image 1: When registering the component and it does not appear in the list, click on the small button beside the field (arrow 2).

Image 2: Enter the new component and click the Save button

Image 3: Note that as you type the main list it is filtered. This technique helps us not to duplicate already registered names.