> or < Trying to constrain an entry between two values

[GAccess]

Is there a way to place greater than and less than parameters within vba access? I have tried so many ways for hours today trying to meet my objective.

Objective1: When the user clicks vbno on a message box display.. I want the user to have a constraint in the text box between 0 and 100, and the value cannot be 0 nor 100, simply between.



Background: This same text box has different constraints, where when yes is clicked. The value is 100. The textbox also has a must be 0 constraint when nothing is typed.

Objective2: I would like, when the user clicks the next record button, to have an error message when the user's entry is not between 0 and 100 once vbno is clicked.

Can anyone help?

I was attempting to use the option of calling a subroutine, however, access would not give the user enough time to enter data before an error message is displayed.

I am stumped.

I even tried to see if I could call the vbno function during the click event for new record. I do not know how to do that.

[John_G]

Hi -

I'm not sure I understand your question correctly, but I think you need to use the MsgBox function and the InputBox function.

From what I understand, you need something like this:

The user is asked a question - they answer Yes or No (MsgBox)
If they click Yes, then "the value" (of what?) is 100
If they click No, then they need to enter a value >0 and <100

But how do they specify 0? You said that "...must be 0 constraint when nothing is typed..." - when nothing is typed where?

Here is some code you could try:

Dim Answer as Integer, Response as Interger

'
' Use MsgBox to get ans Answer of Yes or No
'
Answer = MsgBox("This is the question..", vbYesNo)
if Answer = vbYes then
Response = 100 ' User clicked Yes
else ' User clicked "No"
Response = -1
while Response <= 0 or Response >= 100
Response = InputBox("What should the value be? (>0 and <100)")
wend
endif

But, as I said before, there is nowhere to get a value of 0 in this. Your objective1 and Background are contradictory.

Just a note - vbNo is not a function - it is a built-in MS Access constant.

Please clarify how the 0 is entered, and what is done with the response.

HTH

John

[GAccess]

Thanks for your help.


User has to choose whether not to purchase an item.

User has two buttons: Purchase and Do Not Purchase,

beneath those buttons is a textbox that has Payment Amount.

When user decides not to Purchase, Do Not Purchase is clicked and automatically the payment is $0.
When user decides to Purchase, Purchase is clicked and the user is prompted witha Msgbox command containing vbyes and vbno. The question is.. Are we taking the item out of the story today?

If this answer is yes, the user will click yes and the full cost of the item will be populated in the Payment Amount textbox. (Let's say $100)
If this answer is no, the user will click no and has to place a value that is more than 0 but less than the purchase amount(Let's say $100); we are assuming that this item is on layaway, and small payments will be made.

However, I see a potential error that could occur to affect the quality of information. The user could go to the next form without entering an amount between 0 and the purchase amount.

So I would like to meet the objective where the user cannot go to another record after clicking vbno unless they enter a payment between 0 and the purchase amount.

I hope that I made this clearer.

[GAccess]

I went ahead and placed -1 as a automatic layaway amount so my objectives have changed.

In other words if Purchased is click and vbno is the answer to "Are we taking the item out of the store" then the amount will automatically show as -1.00.

Objective: which command will stop the save new record until the value has changed?

Can you help with this?