Limit a Macro's usage (kind of) (1 Viewer)

emus

New member
Local time
Today, 14:26
Joined
Nov 17, 2021
Messages
8
So, my database has a table for products, where the product amount is also recorded. There is also a transactions table, that looks like this:
transaction table.PNG


In a SALE, it wouldn't make sense for ProductQuantity in the Transactions table to be more then the amount of product in the inventory. To prevent that I have this Before Change macro in place:

macrobeforechange.PNG

The problem is, I want this to aply only when the Purchase/Sale field in the Transactions table is "Sale", not when it is "Purchase". I don't know how to implement that into this macro. Thanks in advance!
 

oleronesoftwares

Passionate Learner
Local time
Today, 04:26
Joined
Sep 22, 2014
Messages
1,159
Click on "Conditions" on the top of the macro , this will make the condition cell visible, then type forms!formname!controlname!="Sale"

form name is name of form
control name is name of the control on the form
 

emus

New member
Local time
Today, 14:26
Joined
Nov 17, 2021
Messages
8
Click on "Conditions" on the top of the macro , this will make the condition cell visible, then type forms!formname!controlname!="Sale"

form name is name of form
control name is name of the control on the form
I'm sorry, I am doing this for a school project so I don't have much knowledge of MS Access or coding. By "Conditions" do you mean to edit the "Where Condition"? because there is no "Conditions" on top of the macro as far as I can see. Also, I don't know what you mean by the control on the form but I assume you mean the combo box "Purchase/Sale". Here is a screenshot of my Access:
screnn.PNG
 

June7

AWF VIP
Local time
Today, 03:26
Joined
Mar 9, 2014
Messages
5,423
I wouldn't use a data macro for this. I would use BeforeUpdate event of textbox for ProductQuantity.
Should be using forms and not working directly with tables/queries for data entry/edit.

Ideally, inventory balances should not be saved to table, but calculated when needed. http://allenbrowne.com/AppInventory.html
 

oleronesoftwares

Passionate Learner
Local time
Today, 04:26
Joined
Sep 22, 2014
Messages
1,159
I'm sorry, I am doing this for a school project so I don't have much knowledge of MS Access or coding. By "Conditions" do you mean to edit the "Where Condition"? because there is no "Conditions" on top of the macro as far as I can see. Also, I don't know what you mean by the control on the form but I assume you mean the combo box "Purchase/Sale". Here is a screenshot of my Access:
macro conditions.PNG
 

June7

AWF VIP
Local time
Today, 03:26
Joined
Mar 9, 2014
Messages
5,423
What version of Access is that? I am not seeing those ribbon items in Access 2010 and macro builder is completely different. I seem to remember it looked like that in 2003.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 11:26
Joined
Sep 21, 2011
Messages
14,038
What version of Access is that? I am not seeing those ribbon items in Access 2010 and macro builder is completely different. I seem to remember it looked like that in 2003.
I have that view in 2007?

1642972100014.png
 

oleronesoftwares

Passionate Learner
Local time
Today, 04:26
Joined
Sep 22, 2014
Messages
1,159
I'm sorry, I am doing this for a school project so I don't have much knowledge of MS Access or coding. By "Conditions" do you mean to edit the "Where Condition"? because there is no "Conditions" on top of the macro as far as I can see. Also, I don't know what you mean by the control on the form but I assume you mean the combo box "Purchase/Sale". Here is a screenshot of my Access:
Watch this video in the link below

 

June7

AWF VIP
Local time
Today, 03:26
Joined
Mar 9, 2014
Messages
5,423
Video opens with Access 2010 logo and title says 2010 but the db used is 2007. I can see it in the db header. Bad product.
 

June7

AWF VIP
Local time
Today, 03:26
Joined
Mar 9, 2014
Messages
5,423
So how and why would I do that? Backwards compatibility I suppose?

The video is teaching 2007 tool, not 2010. As I said, 2010 macro builder is completely different.
 

oleronesoftwares

Passionate Learner
Local time
Today, 04:26
Joined
Sep 22, 2014
Messages
1,159
I'm sorry, I am doing this for a school project so I don't have much knowledge of MS Access or coding. By "Conditions" do you mean to edit the "Where Condition"? because there is no "Conditions" on top of the macro as far as I can see. Also, I don't know what you mean by the control on the form but I assume you mean the combo box "Purchase/Sale". Here is a screenshot of my Access:
So how and why would I do that? Backwards compatibility I suppose?

The video is teaching 2007 tool, not 2010. As I said, 2010 macro builder is completely different.
The IDE of the macro environment shown in the video is 100 percent the same as that which the OP shared, that is the reason i chose the video.
 

June7

AWF VIP
Local time
Today, 03:26
Joined
Mar 9, 2014
Messages
5,423
Really? The OP's images in this thread look different from the video. OP environment is "2007 - 2016 file format", the video shows "Access 2007" in the tutorial. The lead-in logo is misleading.
 

oleronesoftwares

Passionate Learner
Local time
Today, 04:26
Joined
Sep 22, 2014
Messages
1,159
Really? The OP's images in this thread look different from the video. OP environment is "2007 - 2016 file format", the video shows "Access 2007" in the tutorial. The lead-in logo is misleading
i am referring to the macro window, not access window
 

June7

AWF VIP
Local time
Today, 03:26
Joined
Mar 9, 2014
Messages
5,423
Okay, the macro window images are different.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:26
Joined
Oct 29, 2018
Messages
21,357
The problem is, I want this to aply only when the Purchase/Sale field in the Transactions table is "Sale", not when it is "Purchase". I don't know how to implement that into this macro. Thanks in advance!
Hi. Sounds to me like all you need to do is as the first action in your macro, check if the current record is for a Sale. If so, proceed to the rest of the macro. Otherwise, stop the macro and move on. Just a thought...
 

RogerCooper

Registered User.
Local time
Today, 04:26
Joined
Jul 30, 2014
Messages
277
In a SALE, it wouldn't make sense for ProductQuantity in the Transactions table to be more then the amount of product in the inventory. To prevent that I have this Before Change macro in place:
It makes perfect sense for the quantity on a transaction to exceed the inventory. The transaction may be putting inventory into stock. You need to distinguish between inventory transactions and sales transactions.
 

Users who are viewing this thread

Top Bottom