auto decrement

someone plz let me know how to do an auto decrement function for an inventory between 2 tables. one is an item detail table - part id,description,location,inventory and critical level, and the other table: a usage table -date,part id,quantity taken out.when an item is taken out, the inventory -first table should decrease automatically.
please help

Use DAO or ADO code to open a recordset from the first table and subtract the amount used from the current existing balance

if you would like help on the specifices, post a sample of your DB and I will walk you through the code

S-

Ive tried a few times to send this.hope it works this time.thanks sbaxter.am only sending 2 tables.one the item list and the other is the usage table.
thank u.

i don;t know what DAO and ADO are.am actually just getting started with this.plz help.

Include the form where you are recording the usage.

S-

i cant send it in a zipped file, it's too big.
and i cant send just the form alone,right?it wont work without the table and the query.

plz dont check the other database db3 cos it doesnt make sense.
am sending u this,just the table [with total value], and the query for it in the SQL view is as below:

UPDATE [with total value] SET [with total value].Totalvalue = [Quantity]*[Value];

from here you can generate the form with from wizard. its too large to send it.

but thank you sbaxter.

Check for a Private message from me. I have given you FTP info so you can put your DB with your forms there,

S-

Which form are we look at that has the problem?

What do you need it to do?

S-

the forms are 'usage with totals' and 'item list'. when i enter a part number with its details in the usage form, the 'inventory' field in the item list should automatically decrease its value by the amount of items taken out.
thank you.

First off.

I would not use a form property-default view of continous form. It doesn't make sense for this kind of data entry

Second.

In order to decrement the qty of a field in a table not bound to the form you need an event to intiate the action. whether pressing a save button or whatever you need some kind of event


I would add a ave button to you form for this purpose.

Third either option 1 or 2

Option 1
You are going to have to write DAO or ADO code and SQL code to open a recordset of data from the table ItemList where you will take the qty currently stored there, subtract the amount then pass that amount back into the table.

Option 2
Write an update query that you will intiate with you button


I have attached a sample db that shows you sample code for both DAO and ADO

look at it, decide which avenue you wish to take and I will assit you the best I can.

Understand that I my perference is to not use bound forms

Also you have to recongize that your current design allows the editing to orders placed. If you decrease the amount order you need to add that qty back to the amount on hand.

Without understanding you business process (could be point of order system), you would be best off to not decrease the on hand amount untill you invoice the amount sold. I would increase an amount allocated, then when you invoice the order decrease both amounts by the invoiced qty.

What ever you decide, let me know how I can help you and lets be on our way.

S-

S-, i don't see the attachment. d one on the ADO and DAO codes...i dont see it...sorry.

My Bad, I forgot

here it is

S-

S, i managed to see the ADO and DAO codes, but am not so clear abt what they do, and specifically the difference bet them. no knowledge of visual basic / VBA.

so plz give me some time to read them up and understand the concept, and I will get back to you.

another thing.... when an item is withdrawn (usage table), is it possible just to key in the quantity and the name of the machine it is being used for? instead of keying in the description, the part number, unit price (all the details)...since i already have these details stored in the ItemList. so for example, if somebody wants to withdraw an item, they click on the item, and just have to key in the quanitity, their shift (a,b or c), and the machine number is it being used for. then the record will be updated into the usage list.

about the part where u said ," would be best off to not decrease the on hand amount untill you invoice the amount sold. I would increase an amount allocated, then when you invoice the order decrease both amounts by the invoiced qty."

are u talking about the INventory and the total price of the item being decreased together?

actually that's what its supposed to do.

another thing : just to tell you what exactly my database is for. it's for a store. a spare part store in a company.
so the item list tells us all the parts that are available.
the database is going to be used by the technicians whenever they have to replace parts for the machines.

they have to do a search,and then if possible, double click on an item that they want, and key in the quantity and which machine number it is beign used for.

apart from that, the administrator must be able to check the current value of parts in the store, and the record of usage of parts from a selected date to another selected date.

currently have already passed the first stage, the search stage, that is. hope what i mentioned is clear.

thank u.

i've looked at the vbaunbound.zip
Update using ADO doesnt seem to be able to delete a record, the msg box appearssaying that the record is deleted, but in the drop down box, the deleted record is still there. but adding and editing is ok with ADO.

Using DAO, it works. adding editing and deleting.

how do i go about it now S.