Command Button action based on condition 1

[meagain]

I have a command button currently set up in a form, that runs a macro when the user clicks on it. The macro simply sets the value of a field to zero and another field to "Y".
I now need to have the system determine whether to run the macro or display a message. This decision is based on the value in the "remaining" field for the current user, in the "limit" table.
If the value is >0 the macro should run.
If it is <=0, a message should be displayed telling the user that they are over their limit.
I am an experienced Access 97 user, however I am a virgin when it comes to modules and I am assuming that is what is required here. Please be gentle...
Thanks in advance for your expertise and wisdom.

 

[jebry]

Hi!

Try this in the click event for the command button:

If DLookUp(&quot;Remaining&quot;, &quot;limit&quot;, &quot;User = '&quot; & CurrentUser & &quot;'&quot > 0 Then
DoCmd.RunMacro &quot;YourMacroName&quot;
Else
Call MsgBox(&quot;You are over your limit&quot
End If

You didn't say how you determined the current user so I pu something generic in there.

hth Jeff Bridgham
bridgham@purdue.edu

 

[meagain]

Thanks Jeff,

I have entered the following in the click event;

If DLookup(&quot;Remaining&quot;, &quot;LIMIT&quot;, &quot;USER ID&quot; = &quot;&CurrentUser&&quot > 0 Then
DoCmd.RunMacro &quot;MyMacroName&quot;
Else
Call MsgBox(&quot;Sorry, you have reached your monthly limit&quot
End If

It runs without error, however the response is always the message. Why do you suppose the macro never invokes?

In answer to your question about the current user, within the table called LIMIT, there is a field named USER ID. The user ids in this field are identical to those set up in Access security. As you know, when the user logs on they become the CurrentUser. The LIMIT table maintains their established limit, a running total of the $'s used for each user, and the remaining balance(established - used).

Your help is greatly appreciated!!

 

[jebry]

Hi!

I think the trouble is in the DLookup, try this:

DLookUp(&quot;Remaining&quot;, &quot;LIMIT&quot;, &quot;[USER ID] = '&quot; & CurrentUser & &quot;'&quot

hth Jeff Bridgham
bridgham@purdue.edu

 

[meagain]

Right on!!

It works great. Thanks so much Jeff.