Inventory Program

[Altecjjf]

Hey Everyone
I need some help in building an Inventory program. What i need to do is take an order that would subtract quantities from the database. That way when the level hits the reorder level i would know to reorder the product. I am using access 97 with VB5. I would like to know if a SQL could be used or something else is out there that i Do not know about. I have smoked my brain on the easies way to do this, but i have not come up with anything. Any ideas let me know.

Thanks in advance

Altecjjf

[Angus]

In most databases you would have a trigger than would prompt some action when a value was reached or a record updated.

I don't know if this is possbile in access but you could write code to do it at the front end, where the information is displayed(checking the value in a text box from instance)

[Altecjjf]

Hey angus
Thanks for the reply. What i am doing is creating a database in access97 that use VB5 to access the database. The problem that i am running into is that i am going to have sales sheets that need to deducted from the database of inventory. So i was think i could do it using a DBGrid to list the product, price list, and quantities. Then use a SQL statement to perform the action. It's kinda like "NORTHWIND" sample database, but that database keep track of sales in one table and it's product in another table. So there is no way to tell what you are currently hold in inventory. I hope i explained what my problem is.

Thanks
Altecjjf

[JasonGS]

You could update the inventory table after each sale. Assuming that you are using ADO...

UPDATE inventory SET instock = instock - 1 WHERE productid = 0418234

For a reorder report, you could use the following SQL statement to print this report for you...

' Anything that you have less than 10 of.. REORDER IT
SELECT * FROM inventory WHERE instock < 10

[Altecjjf]

Hey Jason
Hey the UPDATE command may work i will work at for a few days and see what happens. If i get stuck i will be back on here. Thanks again

Altecjjf

[Altecjjf]

hey phaldo
I was going to do something along those lines for reodering, so that in the morning a guy would know what to order. So then how did you handle the inventory?? Did you run a program at night that would take all the orders and subtract them from what is currently in the inventory. I was going to use the sales date to group all the sales together so that the program would not subtract from every sale for the whole year. What do you think? Also, did you write the other program in vb too??

Thanks

Altecjjf

[phaldo]

I had it automatically subtract the inventory once a sale was complete. I could have very easily had it tell the employee right there if they were low on that stock, but for a few reasons i didnt. 1) assuming one of the managers stupid employees was working the register at that point, they might not know how to handle making orders. 2) it's easier to look at a list of items you need than to do them one by one. and a few more small reasons. When the manager would logon in the morning, an option would pop up asking him if he would like to view the low-stock.
My program was a bit different though. I made a "smart" low-inventory generator. Of course, it was an option that you could turn on or off, but what it did. In a CD store, you have a lot of CD's that never sell. And if you do sell a "Joe Blows Greatest Hits" you probably dont want another one in your inventory. My program checked the status of the product, determined if it was doing well or not, and then determined which ones the store needs should reorder.
I wrote it in VB6, using Access as my database, and Crystal Reports as an invoice and report generator. I used a lot of SQL querys though. Those were basically the whole program, as a form of gathering the correct data.

oh, yeah, I wrote the other program in VB also. actually, it's the same program. It would automatically run at a certain time each night. I had the employee click a button that "made it ready to search", that way it doesnt check for that certain time every minute, that slows the speed down just a tad.

Are you doing this program for fun or business?
-phaldo

[Altecjjf]

Hey phaldo
I am doing this program for a business that am i planning to buy. It deals with a mom and pop small business engine repair and parts store. Currently the whole inventory is on paper and i want to move it to a database. They have some type of computer system now, but do not use it too often and from the looks of things it is running a database that is not too friendly. I basically have to bring the whole place up to the 21st century.
Now when you had it automatically subtract the inventory once a sale was complete, did you have it in a command button that would run it and print a sales invoice? Also if you did, did you have anything coded so that if the employee hits the button twice it would not update the inventory twice??
I am going to work on the "smart" low-inventory generator, that is a good idea. Thanks!

Altecjjf

[phaldo]

Altecjjf,

I had it subtract it automatically. The employee clicked the button "Finish Sale" or whatever I called it. It closed the Invoice window,printed the invoice, via Crystal Reports 8, then subtracted the inventory via Access. There would be no way the employee could click the button twice because the initial time they hit the button it would close the window. Also, what I did was have a "previous invoice" window. Where via the mainmenu the employee could look up a previous invoice.

And uhm. stop stealing my ideas :P

[Altecjjf]

hey phaldo
Hey i am not stealing your idea's, i am picking your brain ;> "Great minds think alike" I am getting vb6(I have 5 now) so i am going to start coding the new program in that when i get it. How long did it take you to write your program?? I have been working and thinking for a few months and have some of it written, but i am far from done. Also i have limited time too!! (Doing it in my soare time.) Was there anything in your program that you thought was tough and got stuck on?? Just wanted to know if i am going to run into any big problems?

hey phaldo thanks for everything
Altecjjf

[phaldo]

It took me about 5 months, but that's not 5 months straight, I did it between school and work. I didn't find any part *hard* per se. There were parts I got stuck on, but I knew where to look to find the answer. The program is also really huge, I keep adding stuff when I think of it. I'm trying to learn how to write my own DLL files. A lot of algorithms I write I do for every program, and it will just save time to be able to run certain functions from a program without having to write the code each time. Finally, You should use the registry to save and retrieve settings. It's much more efficent than text files or INI files. I only use INI files for the location of the inventory database.

[Altecjjf]

hey
Well once i get the program up and running i want to integrate a hand held bar scanner. Most of the parts have bar codes, all i need to do is get the scanner to talk to the program. At one of my jobs a kid got it to work with his VB prgram. i don't think it will be that hard. So you write your own .DLL, is that hard?? I have never looked into that, mainly because of one reason i don't have enough time. So by you making your own .DLL what do you gain from it?? Speed, prgram size, or something else??

Altecjjf

[phaldo]

making the VB program barcode scanner ready is easy. The scanner is an input device, just like the keyboard. Most scanners dont even use batteries, they get their power the same way the keyboard does. If you plug a scanner in, you can use it all over, even in DOS, just scan a barcode and you'll see about 10 numbers display on the screen. Then you have to do something with that, you want it to scan and print info on that item right away. that's simple. but dont look to me for that help :) I've given you too many of my secrets, I did it on my program, it's really simple though.

I havent made a DLL yet, I'm still trying to figure out how in VC++. It doesn't give me anything other than the fact I don't have to type a lot of routines over and over and over. I can just write it once and then just call the function everytime after that. The program WOULD be smaller if i didnt embed the DLL into the EXE.
I wish you could make DLLs in VB.