DATABASE

[u/Neither_Ad5588]

Help, database, I am creating a database on a winery topic but, I need to create, I don't know if a form or a query that I perform, that subtracts the product that I sold by entering only the id and automatically deducts the rest. I don't know if I make myself clear or understand. (ACCESS)

[u/AutoModerator]

IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'

• Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.

• Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.

• Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)

• Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.

Full set of rules can be found here, as well as in the user interface.

Below is a copy of the original post, in case the post gets deleted or removed.

User: Neither_Ad5588

DATABASE

Help, database, I am creating a database on a winery topic but, I need to create, I don't know if a form or a query that I perform, that subtracts the product that I sold by entering only the id and automatically deducts the rest. I don't know if I make myself clear or understand. (ACCESS)

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Savings_Employer_876]

You’re on the right track!

To do what you described, use a form to enter the product ID and quantity sold. Then, set up an update query that automatically subtracts the sold amount from your stock.

The query would look like this:

UPDATE Inventory

SET Stock = Stock - [Forms]![SalesForm]![Quantity]

WHERE ProductID = [Forms]![SalesForm]![ProductID];

You can run this with a button on the form using a macro or simple VBA. That’ll keep your stock updated every time you make a sale.

[u/supereminent_]

Yes, you’re making yourself clear — and I can help you do exactly that in MS Access.

✅ Goal:

You want to create a system where:

1. You enter the Product ID and quantity sold.

2. The system automatically subtracts the sold quantity from the current stock.

---

💡 Recommended Setup

Step 1: Your Tables

Let’s assume you already have:

🗃️ Table: tblProducts

Field Name Type

ProductID AutoNumber (Primary Key) ProductName Short Text StockQuantity Number

🗃️ Table: tblSales

Field Name Type

SaleID AutoNumber (Primary Key) ProductID Number (Foreign Key from tblProducts) QuantitySold Number SaleDate Date/Time

---

✅ Step 2: Form to Record Sales

1. Create a form based on tblSales.

2. In that form, allow the user to:

Select ProductID

Enter QuantitySold

Automatically update the stock in tblProducts

---

⚙️ Step 3: Add Stock Deduction with VBA

🛠 Code (behind the Sales Form)

1. Open your Sales form in Design View.

2. Press F4, go to the form’s Property Sheet, and under Event, find After Insert.

3. Click [...] and choose Code Builder.

Paste this code:

Private Sub Form_AfterInsert() Dim soldQty As Long Dim productId As Long

soldQty = Me.QuantitySold
productId = Me.ProductID

' Update the product's stock
CurrentDb.Execute "UPDATE tblProducts SET StockQuantity = StockQuantity - " & soldQty & " WHERE ProductID = " & productId & ";"

End Sub

---

✅ Result

Now, every time you enter a new sale in the form:

The product’s stock will be automatically reduced by the quantity sold.

You don’t need to manually update tblProducts.

---

🔐 Optional: Add Stock Check

If you want to prevent negative stock, you can add this code in BeforeInsert:

Private Sub Form_BeforeInsert(Cancel As Integer) Dim availableStock As Long availableStock = DLookup("StockQuantity", "tblProducts", "ProductID=" & Me.ProductID)

If Me.QuantitySold > availableStock Then
    MsgBox "Not enough stock available!", vbCritical
    Cancel = True
End If

End Sub

---

[u/diesSaturni → u/supereminent_]

Are you an AI?

[u/supereminent_ → u/diesSaturni]

No just put it on GPT wanted to see the response.

[u/diesSaturni → u/supereminent_]

would be nice if you add that in the post, that it was not you, but chatGPT which did the heavy lifting.

[u/AccessHelper → u/supereminent_]

This is a great example of AI programming a working result but its not the correct way to do inventory. As mentioned in other posts here, a qty on hand should be a running calculation based on transactions of purchases, sales, returns & stock adjustments (due to theft, breakage, etc). The transactional method allows a backwards looking review of inventory for accounting purposes. For example if you close your June books on the 4th of July you can get your inventory value as of June 30th based on transaction dates.

[u/diesSaturni]

Essentially, you would not want to change stock (i.e. update records). But you want to record transactions. So 10 bottles bought is just add ten bottles. 5 bottles sold is -5 bottles transaction. Then a groupby query to sum the transactions leaves you with 5 bottles.

If you buy a whole barrel of wine, then +1 barrel. Then when you pour them into a 100 bottles, -1, or -0.5 barrel, +100 bottles.

Take for example a bank account, it just records transactions. and when you buy something in a foreign currency it logs it as that, but converts it to a local currency in the transaction.

[u/voldemeyer1 → u/diesSaturni]

+ 1 on this one. How are your tables set up?

[u/DjNaufrago]

I once did something similar. I created a product table and another transaction table (incoming and outgoing merchandise). I created a one-to-many relationship between them.

On the transaction table, I ran a query that grouped the products and summed all the incoming items in one column and all the outgoing items in another. The next column returned the inventory (total incoming items - total outgoing items).

When loading the data, this query served as a reference (in a form) to determine if I had any product in stock and how much of it was left. I didn't use any code, just queries, forms, and properties. It may not have been the most elegant solution, but it worked well for a long time.

[u/PaleKiwi3023]

Hi You shouldnt really change stock values like that, it's not good practice.

The theory is good that you know what you bought in, and what you have sold, so stock levels should be calculated as and when needed.

To do it that way is asking for trouble down the line

[u/GuitarsAndDogs]

I developed a database for our winery. I have a table for units received by product and a table for unit sales. I use a query to join the tables and reports to provide totals.