Hello everyone: I have a sales form when I enter a quantity The quantity in the table decreases Only it doesn't work well With I have 10 in the tabl (1 Viewer)

yaron3500

New member
Local time
Today, 15:05
Joined
Feb 27, 2020
Messages
14
‏‏‫ת).png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:05
Joined
Oct 29, 2018
Messages
15,542
Hi. Why even look up the value at all? You should be able to update the table by simply subtracting the qty from the field.
 

Minty

AWF VIP
Local time
Today, 13:05
Joined
Jul 26, 2013
Messages
8,600
You wouldn't normally try an keep tabs on this in a field, simply calculate the value when you need it.

Also, I assume Products_Query is a query rather than a table, so you can't normally update a query per se.
 

yaron3500

New member
Local time
Today, 15:05
Joined
Feb 27, 2020
Messages
14
Allow a clearer answer thanks
 
Last edited:

Minty

AWF VIP
Local time
Today, 13:05
Joined
Jul 26, 2013
Messages
8,600
I think you need to show us your table structure, as you may well be trying to achieve this in the wrong way.
Can you either post a picture of your tables and relationships or upload a stripped-down version of your database?
 

Cronk

Registered User.
Local time
Today, 22:05
Joined
Jul 4, 2013
Messages
2,514
I would have thought that the quantity sold would increase after a sale, not decrease.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:05
Joined
Feb 28, 2001
Messages
20,224
OK, Yaron, I will try to offer a clearer answer.

The suggestion from theDBguy would be to change your SQL and not bother with doing the math ahead of time. SQL can do math so you can give it something like this, critical part in red...

Code:
sSQL = "UPDATE Products_Query " & _
    "SET Quantity_Sold = Quantity_Sold - " & Me.QtySold & _
    "WHERE Pro_ID = " & Me.Product_ID & " ; "
DoCmd.RunSQL sSQL

The other comments relate to the way inventories are normally managed. Keeping a static quantity in a table like this works until the day that things get out of synchronization for some reason. Typically, inventory-like systems store transactions and just use a summation query to provide the current quantity on hand. This quantity is the sum of all sales (written so as to reduce quantity on hand) and all supply actions (written so as to increase quantity on hand). There are probably hundreds of articles in this forum on the subject of inventory and they can be found doing a simple forum search for the one keyword, "inventory." What you are doing right now will work - until it doesn't. The first part of this post is what you do now. But long term, you might consider alternatives to your current design.

Please don't take this as an attempt at harsh criticism. Many of us have seen things go wrong with inventories that don't use this approach. It is up to you to take or leave the suggestions we offer. It is, after all, your project.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:05
Joined
Feb 28, 2001
Messages
20,224
Oddly enough, the code tags didn't highlight the color as I would have thought... so it wasn't in red after all. Darn it.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:05
Joined
Feb 28, 2001
Messages
20,224
@yaron3500 - you sent me a private message asking for specific help but I think you need to make that a separate new post with your new question - unless it already IS one and I just missed seeing it.

Making a formal post in an Access-specific forum section increases your odds of getting help. The more senior members who see your problem, the more likely you are to get help.

You should also know that I don't check this forum at all when I am working on my other projects, which means you might have a long delay. Since I do have some outside interests, I'm not the most reliable source of help when considering my schedule.

I'm not at all angry that you sent a message; it is rather flattering. However, in the final analysis, I want you to get the help you need and the best source of help might not always be me.
 

yaron3500

New member
Local time
Today, 15:05
Joined
Feb 27, 2020
Messages
14
Why Increase Quantity Decreases Because Eggs sell a commodity the same item went down to quantity
 

yaron3500

New member
Local time
Today, 15:05
Joined
Feb 27, 2020
Messages
14
I would have thought that the quantity sold would increase after a sale, not decrease.
Why Increase Quantity Decreases Because Eggs sell a commodity the same item went down to quantity
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:05
Joined
Feb 19, 2013
Messages
13,093
Oddly enough, the code tags didn't highlight the color as I would have thought... so it wasn't in red after all. Darn it.
unfortunately the code tags apply their own formatting and ignore any you have applied.
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:05
Joined
Sep 21, 2011
Messages
8,493
Oddly enough, the code tags didn't highlight the color as I would have thought... so it wasn't in red after all. Darn it.
Doc,
I aksed about this just the other day. If you choose Rich BB you can highlight code?
Rich (BB code):
Oddly enough, the code tags didn't highlight the color as I would have thought... so it wasn't in red after all. Darn it.

Or if you do the tags manually use Code=rich surrounded by square parentheses as the opening tag.

HTH
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:05
Joined
Feb 19, 2013
Messages
13,093
but does that work if you choose SQL code which will highlight the key words?
 

Users who are viewing this thread

Top Bottom