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

[emus]

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:

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:

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]

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]

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:

 

[June7]

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]

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:

 

[June7]

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.

 

[Gasman]

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?

 

[oleronesoftwares]

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]

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.

 

[oleronesoftwares]

Video opens with Access 2010 logo but the db is 2007

Yes, but its ms access 2010, using 2007 db format

 

[June7]

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]

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]

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]

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]

Okay, the macro window images are different.

 

[theDBguy]

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]

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.