Macro Not Executing Correctly

[MelF]

Here's my daily dilemma: I want a Message Macro to display when one of my product's (Quick Start Cards) Units Available drops below 11. So I wrote a Message Macro with the following expressions in the Condition field:
[Forms]![Products]![ProductDescription] In (&quot;Quick Reference Card - Up & Running&quot And Len([Forms]![Products]![UnitsOnHand])<11
I then put this macro in the Properties of my Units Sold control,the Events Tab, in the After Update field. (because the Units Sold is the field that subtracts from my total Units On Hand.)

Now whenever I enter any number into the Units Sold field, this message pops up, even if my Units Available is not yet below 11. Should I put this macro somewhere else? Any ideas? Thanks!!

 

[Robertd]

Hi Mel,

get rid of the Len() this function returns the number &quot;count&quot; of characters in a string not the value so

[Forms]![Products]![UnitsOnHand]<11
is true if UnitsOnHand < 11, ie 10 or less

Also

&quot;Quick Reference Card - Up & Running&quot; because you have inverted commas arround these it becomes a string, i don't think this is what you might of intended,

what does Quick Reference Card AND Up & Running refer to are they controls like text box's on the form? if so this statement needs to evaluate in a True or False result because you are ANDing the statements together ie saying

<Something> AND <Anotherthing>

must result in True for the message box to be displayed and False for no masage box to be displayed

so if they are controls you must specify a condition that results in True OR False

HTH

Robert Dwyer
rdwyer@orion-online.com.au

 

[MelF]

Robert - Thanks for responding! I am new at this, so please have patience!! I did find out yesterday that the Len was not what I should have used. But - Here's some more explanation: I want to say if the Product Description field says Quick Reference Cards - Up & Running and the Units On Hand is <11 then to display the message box. It's been suggested that I write it in code as:

If Me.UnitsOnHand - Me.UnitsSold < 11 Then
MsgBox &quot;ORDER MORE QUICK REFERENCE CARDS!!&quot;, vbOKOnly
End If

and put it as an After Update Event in my Units Sold properties, which I've done, and it works fine, but now I have another problem: That control is for numerous products, as each product is a record that displays on the form. (You access the different records by arrow buttons, so that only one product displays at a time). I only want this to apply to one of my products - My Quick Reference Cards. ???

 

[Robertd]

Hi MelF,

Ahhh,.. the plot thickens ;-)

thats OK,

if Me.UnitsOnHand - Me.UnitsSold < 11
if me.Description = &quot;Quick Reference Cards - Up & Running&quot; Then
msgbox &quot;your message&quot;
end if
end if

here the <Description> should be the name of your Product Description field

to show the logic here consider this, first we look to see if the stock quantity is below the minimum required

Me.UnitsOnHand - Me.UnitsSold < 11

then if it is we check that the product description is the one we want

&quot;Quick Reference Cards - Up & Running&quot;

now both criteria must be meet before the message will be displayed.

I would probably actually make this a sub function and call it from both the units sold and also in the on current event form the form.

I have to say that i don't know much about macros don't even use them. i certianly like using code though, not that macros arn't any good or anything i have seen applications that use a lot of macros and they work well. You will find that VBA gives you a lot of control and allows you to do all sorts of things it will be a steep learning curve but the rewards are worth it.

HTH


Robert Dwyer
rdwyer@orion-online.com.au

 

[MelF]

Robert - Thanks so much for your input! I'm trying that code write now. Running into a problem, though: When I type Me. and the list of fields comes up for me to choose from - My ProductDescription field, as well as some other fields are not on this list, and if I just type it in, it gives me an error message. Any idea why these fields wouldn't be there but they ARE on my form?

Thanks!!
-Melody

 

[Robertd]

Hi Melody,

If you open the form in design view select the field for product description, (if the properties panel is not displayed, right click then select Properties) then look in the properties under the &quot;Other&quot; tab it will have an entry for the &quot;Name&quot; of the field. If you draged and droped the Product Description field onto the form from the &quot;field list&quot; this would by default have the name of the source field, on the other hand if you placed a textbox on the form, and selected the data source for this it will have a default name of &quot;Textnn&quot; the nn is a number.

So check this to see what &quot;Name&quot; it has, the actual name that appears in the Properties->Other->Name is the name that access will refer to this control as. The &quot;Me.&quot; is a reference to the &quot;current&quot; object and in this case is a shorthand for Forms![YourFromName]

a question, the form is only a single form, ie there are no subforms involved?

Let us know how you get on




Robert Dwyer
rdwyer@orion-online.com.au