In the product code I have a combo box. Now what i need to do is to less the quantity from the "in stock" in the record of the product(find it using product code selected in combo box) in the products form
In the product code I have a combo box. Now what i need to do is to less the quantity from the "in stock" in the record of the product(find it using product code selected in combo box) in the products form
1) Open the clone and update the value of "in stock" with the appropriate expression (current - out).
2) Keep the "in stock" value and create a log of "in and out" transactions relating to your stock "in's and out's". If you cycle through and report inventory on a monthly basis, then creating a log would be a good idea. Even with a separate table here, you can always calculate the current "on-hand" amount.
I prefer the second option, especially when reporting inventory numbers on a periodic basis. It allows for more storage of past information that is relevant, while also allowing you the ability to query current information.
i think that what i need is something like
find product by using product code(i think is something WHERE ...)
product code = me.cmbpc (pc = product code)
and less me.qty
What you really need is to have a commit button or something when you actually commit the sale. Otherwise, if you remove inventory right away you will have to deal with putting it back if you cancel the sale, which you can do but it will bring more complexity. For example, what do you do if you remove the product right away upon selection and your computer crashes. How do you tell it to add it back?
So, a some sort of Confirm Order might be the way to go with an update query that updates the stock based on the information for that sale ID.
ALMOST - If you do that, it will only update where the one product is selected. But, what I'm suggesting (and I used it in my last project) is to update based on the Sale ID for All products that are associated with that Sale ID (or other field that uniquely identifies the sale).
Yes, I know what you mean here, but I have seen the invoice, and it is fairly simple.
I believe the update to be done is in a table that has one record per product that also has a field called "qty". If that is the case, it's not complex, YET.
I think Bob and I have different pictures of what your tables actually look like.
If someone were to post a sample for you, they would have to see your structure first, so they know what they are dealing with...
Regardless though, it is bad idea to change your inventory numbers upon receiving an order. The commit button is great, so you can think about it before you run any code that cannot be undone.
actually i do not take orders, i sell things and the qty sold will be deducted when pressing the create report button ... i will post a sample for you to see....
this is my sample so that you can see my tables and the ivoice input form.... the code for deducting must be added on the show me how it looks button i think....
I'm going to have to hope ajetrumpet will be able to help as I have to go on a trip. I have to leave in about 5 minutes and won't be available until late next week, unless I have Internet access, which I'm not sure about.
Most of the contributors here are volunteers. Chances are that we can respond, but in most cases, a person's paid work needs to be done before the volunteer work. From what I've heard too, people don't respond well to comments about deadlines (just a little warning for you).
The following code answers your question about updating the quantities of "instock". Before you perform this on the sample though, you should put the "instock" field in the ITEMS table. The file you provided has the items' STOCK amount in the orders table. I don't think that is where you meant to put it. If you put "instock" into the ITEMS table like it should be, then the following sub should work. The function: It will UPDATE every item's "StockCount" in the ITEMS table when you click the "preview report button" (code belongs BEFORE the DoCmd Command)...
Code:
Dim db As Database
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("items")
With rs
.MoveFirst
Do Until .EOF
.Edit
Select Case ![Item]
[COLOR="Red"]Case Me.code1
If Len(Me.code1) > 0 Then
![instock] = ![instock] - Me.qty1
End If[/COLOR]
Case Me.code2
If Len(Me.code2) > 0 Then
![instock] = ![instock] - Me.qty2
End If
Case Else
End Select
.Update
.MoveNext
Loop
.Close
End With
Set rs = Nothing
The block in RED indicates that Access is checking to see which combo box value matches the ITEM of the current record. When it finds it, via the SELECT CASE statements, it performs the calculation on the quantity. As I said before when you initially set this up, doing things this way (having 10 separate lines of individual controls on the invoice form) would get complicated, if not just overwhelming.
At any rate, you need to repeat the block of code in red for 10 CASES (e.g. write 8 more blocks for the 8 remaining combo boxes), using each related sequential control with the next block. I wrote the block for the combo box "Code2" to show you the pattern). The code will certainly be a bit lengthly, but it sure does eliminate a lot of work (work that could be greatly reduced by a structure change).
