Access good for inventory software

[SSBrando]

Hello all, I'm new to MS Access. I just finished a book on it, and I was hoping to find 2 things that maybe can't be done? My purpose is to use this software for inventory management.

First:
When you are in a form,the only way to go from one item in a form to another is by clicking the arrow. What if you have hundreds of items and the ones you need are not close to each other when using the arrow to go from one item to another. I was hoping the lookup wizard could solve this problem. Please correct me if I am wrong, but it looks like my fundamental problem is that I would have to have the table kind of "look" at itself for any real solution. Either that or have 2 identical tables which I won't do for data integrity reasons. I find that everytime I think I have a solution for it, it seems it will only work with a different table. Whether it's a drop down menu or sub form doesn't matter as long as I have a way to see all of the items and be able to pick them (that's the key, to see them and then pick them so I can update the inventory on that item). Kind of like switching to datasheet view, high lighting the item and then switching back to form view.


The other thing I was hoping to do was to "gray" out some of the fields. For instance, my inventory form has a field for the name of the item, description, threshold for amount in stock, reordering amount, etc. I want it set up so that if I have somebody else updating the inventory, they can see all of the fields, but can't accidentily change the information. The only field I want them to be able to change is the current inventory field.



I really hope that Access can do these things. I'm not sure if MS Access is often used for inventory, but if it is, if somebody can point me in the direction of sample databases, tutorials, even a class that might be helpful, it would be grately appreciated.

Thanks.

 

[randy700]

For the first part of your question, take a look at how to build queries. You can base your form on the results of a query and still be able to update the data as necessary. The query can sorted alpha-numerically on any field. Additionally, look into the Filter and FilterOn properties of the form. And, using the wizard, you can add a search button to your form.

For part 2... set the Enabled property of the control to NO to "gray out" the control. Another option is to set the Locked property to YES. You could then set the colors of the "unavailable" controls however you want.

An excellent sample database is packaged with Access. It's called Northwinds.


Randy

 

[67peshawar294]

brando, there are several ways to approach this. I take it you do not have Access installed? I use it for inventory of Eyeglass frames. I don't sell tremendous quantities of one item. (It is used for a retail optical shop, usually only one item per order.) and I have included barcode capability (UPCA, Make and or print) that allows me to keep track of the items sold and update inventory when the end of day report is printed.

As for the "Scrolling to an item" in one of my forms, I click in a subform, type a letter or number and the form displays the first item in inventory list that begins with the letter or number and also appears on the order form.
So yes all of the criteria you are concerned with can be accomplished with Access.

There is a link

http://www.microsoft.com/downloads/...72-8DBE-422B-8676-C632D66C529C&displaylang=EN

which is a sample database. I don't,however, think you can view it without Access installed on your machine. I have Access97 and it says it is for Access2000. I downloaded it and it installed okay.

 

[SSBrando]

Thanks - Randy:

I'll look into the queries and see if that solves my problem. I took a look at Northwind, and as far as I can tell, as far as I can tell, you have to click the arrow to go from one item to the next.

Also, where are these controls for graying out the fields? Are they in Design view?

Thanks.

 

[SSBrando]

Thanks for responding xaf294 -

For my inventory I have the item form as the main part of the form and the order form as a sub form.

In regards to what you do to find an item without using the arrows, are the subform and mainform the same table?

If so, how did you do that?

If not, what is the subform that you click on it and it opens the same information in the main form?

 

[SSBrando]

Hey Randy700 - got a quick questions for you. In the Northwind db, if you go to Products and then select the Preview Product List button, it opens the list in what looks like a report.

What I would like is basically the same thing, but instead of displaying a report, it display a list of the items, and I am able to click on the next item I want to change the inventory on. When I click on the inventory item I want, the item in the form I was just working on changes to the one I just clicked on. This can be done with queries? - Just want to make sure that you know what I was trying to do.

Thanks for your help.

 

[SSBrando]

Hey Randy, I found the controls for enabling and locking. I was looking in Design mode of the table, I just found the ones you were referring to in design view of the form.

Thanks.

 

[pweegar]

As mentioned, take a look at creating a query to locate your item9s). I would take the query one step further and build a parameterized query. This would allow you to use wildcards to display similiar items.

 

[SSBrando]

I'll do that. Sounds like query is the way to go.

Thanks to all for your help. I'll let you know how it works out.

 

[67peshawar294]

Brando,
the form i use is based on an input table, separate from "both" of my other tables.
The subform is a "listbox" based on a "Frames" table.
After the report is printed, a command button is pressed and the info is copied to a "patients" table and the inventory is updated.
To update or add to inventory another table is used as an input table. On scanning a UPC for a product, if a match is found a "popup form" appears, based on the "frames" table, and says the item is already in stock. We just update the quantity. If I had someway to "Bullet Proof" the db I would probably sell it but there are some events that I am not able, with my limited VB knowledge, to master!
If you have the northwind installed go to the orders form and click in the subform. select one of the "products" and you will notice that all are displayed. If you "select" the entire product and type a letter the display will begin with a list of the products that begin with that letter.
jim

 

[SSBrando]

We don't have a bar scanning solution, but in the future, that will probably be necessary.

Thanks for the tip on the third table used for input.

 

[67peshawar294]

brando,
If,I hope, all of your inventory items have barcodes then setting up a system is very easy. All you need is a barcode scanner with a "wedge" that connects between the keyboard and the computer. I picked up a couple of great scanners on ebay for really good prices. Less than $50 for each. (I did find that the laser scanners function better than the ccd models.)I even picked up a cordless scanner and base, with wedge for $225. It has been a godsend since I can operate it a distance away from the computer.
jim

 

[SSBrando]

That's great. Once I work out what I want to do with the queries, I think that might be my next step.

I'm glad Access has the answers I need.

You guys have been great, thanks for your help.

 

[SSBrando]

We had some high-priority issues come up, so I am just picking up with the Access Inventory DB today.

I've been looking the stuff over all day, and I can't seem to get what I want (my menu of items). I've tried using Macros, Lookup Wizard and queries, but I can't get what I am looking for. I think the problem might be that I have to create a second table with just the item names and the a unique number for each item that I can tie to the item names on the original table. I came a little close, I made a macro, based on a query that pulls up all of the item names. I then linked the macro to a button on the form. When I click the button, it brings up all of the items, but it brings them up in a different screen. And I can't do anything with that information once it is up.

What would be ideal is if when I pull up the the list of items:

1. It be in a smaller window to the right of my form, as opposed to opening a new window.

2. If it has to open a new window, that I simply double-click on the item and it brings me back to the form view, with the item I just double-clicked on up.


I guess the first thing I have to verify with you guys is if I should create that 2nd table. I was really hoping to avoid it, just to keep things simple and leave less factors for problems, but if the only way to achieve my goal is to have the 2nd table, I am willing to do it.

What I have right now, is good, but I want to make it as user-friendly as possible. I also used the "find" feature, and that was cool too, but a list to choose from would be a lot more user friendly.

Thanks.

 

[KenReay]

Hi

For menu see Northwind, switchboard, there is a table tblSwitchbordItems which works exactly as you describe

I do not know which book you read, but I would recomend Access Developers Handbook it would have answered all of the questions you have asked so far

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[SSBrando]

The sample Northwind that I have doesn't have a table tblswitchboarditems. What I found is it doesn't have a form for the switchboard...

I'm not sure if you referring to this, but a good example of what I want is in the products section of that database. There is a button for Preview Product List, if you click it, it brings up what looks like either a report or the results of a query in report form. It shows all of the products. The only catch, is from that list, I would like to be able to select a product from the list and have it come up in the form. I don't know if it is possible to do that without making a separate table.


The book I am using looks like it was from a course somebody took, one of the techs had it in his office so I took it for reference. I'll look into that Access Developers handbook.

 

[SSBrando]

I think I might have solved my lookup/quick list issue. And I am able to do it from one table. In the design view of my form, i inserted a List Box, but choose the option:

Find a record on my form based on the value I selected on my List Box.

It did exactly what I wanted it to do, its a list of all of the items (records) from the table, when I click on the record I want, it changes the fields to the current record.

Does anybody know of any drawbacks to using the List box for this purpose?

 

[wdgriggs]

Hello SSBrando....

Drawback? Probably a logic error on my part...
I use one form for my data entry and the same form serves as my history information (IE: add an invoice which includes inventory items). When an inventory item is marked obsolete, so that it doesnt appear in the list box for future invoices, then it is automatically filtered out of the history information (not deleted - just displays blanks).

Most databases would seperate past history though, so you wouldnt have that problem with using List Box as a lookup.

 

[SSBrando]

wdgriggs - If I'm not mistaking I have my item form set up the same way. The item information is the main form and the orders related to that item are the subform (serving as a history of the people that have placed orders for that item, along with the date and quantity - not really concerned with order numbers, etc).

I've automated a few things, for instance, instead of using the tool bar in the subform to create a new record, I made a button on the main form to open a new order record. It has a few buttons to save, close, undo, etc. The only thing I've been stuck on for a few days is editing the amount in stock for that item. For instance, if somebody is ordering one of the items, you click the new order button and it opens a form for the new order, you enter:

1. The person ordering the item

2. The item (it won't automatically bring up the item you are in the new order form - no biggie, but it would have been cool if it did that, but it is simply a new record so I understand why it doesn't do that)

3. The quantity

4. The date (if anybody knows how to make the date come up automatically when I click in the field, that would be great)

And when you click the save command button, it saves the record. Now, what I can't figure out is how to have the quantity of that order that was just saved, subtract from the amount in stock of that item. So the amount in stock is automatically updated, not relying on the user to edit it manually.

My goal is to not have the calculation done until the save command button is used (I'll probably need a macro for this, once I figure out what method to use, that should be easy), just in case they need to go back and edit something. I've tried using queries, but I can't seem to figure out how to get it right. I'm wondering if VBA will be necessary for this task. If I have to have a separate table that is temporarily used for holding the information while it is being calculated, that's cool. If anybody knows of the direction I should head in, that would be great.

Also, does anybody know if editing the colors, etc is possible on a form with a subform? The option to fill backgroud color is there like in a normal form, but it never changes color.

 

[DUF]

Hi SSBrando,
Useing a listbox as you have, there is no drawback only if you have a lot of products in the table it will take time to load listbox.You should have the listbox tied to a Quary that might be able to filter what is loaded in to the listBox.
I have designed a Point Of Sale for two Shops and I use the listbox way to get info on customers accounts. There are over 500 customers and to load this amount into a listbox would take time so I filter the customer my by placing a button for each letter of the alphabet on the bottom of the form and when the user clicks a button the listbox will load all the records for that filter button.

This will take VBA to work, and also VBA should be used to update the Qty that you require.

I think I have a sample of the method I used that I used and modified to suit my needs If you are interested I can try to find it and email it to you.

Keep try I was like you did a course bought lots of books and many hours at the computer.
I now have a POS that works in Locations for the last year and half and no problems.

Regards
Duf