Number Range Requirement

[davies107]

hello everyone,

i have a database that needs a manual input from the user for X, Y, Z range. i need help on deriving a code ( "maybe" in query for criteria? ) that tell if the user exceeded on the allowable number range of either X, Y, or Z.

for example:
X = should have a value of 5 up to 25... if the user input 10 it is accepted, but if they will input 3 , 4 or below 5, or 26, 27, or higher than 25 it will prompt me a notice/message that you have exceeded the value limit. same goes with Y and Z...

can you help me also come up with a code the give warning/message that will pop-up if the number inputted exceeded. like "A user has exceeded the allowed value".

photo is attached.


Thanks everyone!

 

[jdraw]

Why not show us the code behind the form that is displaying the msgbox?

 

[davies107]

there is no msgbox yet. i havent started working on it. the photo attached was done in Excel not Access and thats the concept of what i want to do.

 

[John Big Booty]

Is this going to be a bound or unbound form?

 

[jdraw]

What is your experience level? Do you work with Access much at all? Do you know how to program vba?
Have you designed a Form?
What are the names of the txtBoxes?

You should research the BeforeUpdate event of a textbox; this event will allow you to cancel user input and request an appropriate value before moving on.

 

[davies107]

Is this going to be a bound or unbound form?

unbound i guess.

its kinda simple to think on my part (at least) but when i tried i cant get it right. its just inputting values by the users that should not exceed to the assigned values if it exceed it will prompt a warning or a messagetextbox.

 

[davies107]

What is your experience level? Do you work with Access much at all? Do you know how to program vba?
Have you designed a Form?
What are the names of the txtBoxes?
.

my experience level? hmmmm, i should say beginner but i worked with MS Access for sometime.

programming is my weakness, thats why i do research on some samples then try on my own then apply it on my database.

yup i tried designing a form (with or without splashscreen, password screen, graphics, etc). i tried creating tables with or without calculations (multiple fileds), and also queries (with criteria, and others).

names would be X, Y, Z. it depends, i can change it whatever i want.

You should research the BeforeUpdate event of a textbox; this event will allow you to cancel user input and request an appropriate value before moving on.

yah maybe, thanks for that. but i believe lots of experts here in this forum who experienced and solved my difficulties right now and will share that experience with me.

 

[John Big Booty]

I guess you will use a Command Button to commit the entered values to your underlying table. So in it's On Click event you could use some code along the lines of;

If [URL="http://www.techonthenet.com/access/functions/advanced/nz.php"]Nz[/URL](Me.YourXField, 0) < 5 Or Nz(Me.YourXField, 30) > 25 Then
     MsgBox "Please enter a numeric value between 5 and 25"
     Me.YourXField.SetFocus
     Exit Sub
End If

You would need a similar piece of code to test each of your other fields. Followed by the code you will use to commit the values to your tables.

 

[davies107]

I guess you will use a Command Button to commit the entered values to your underlying table. So in it's On Click event you could use some code along the lines of;

If [URL="http://www.techonthenet.com/access/functions/advanced/nz.php"]Nz[/URL](Me.YourXField, 0) < 5 Or Nz(Me.YourXField, 30) > 25 Then
     MsgBox "Please enter a numeric value between 5 and 25"
     Me.YourXField.SetFocus
     Exit Sub
End If

You would need a similar piece of code to test each of your other fields. Followed by the code you will use to commit the values to your tables.

okay will try this in a while. though im not sure about the command button since people will input in a table-like in a form without a button. i attached a photo for this. thanks John, will just take a break, hehe.

 

[John Big Booty]

Your users should have NO direct access to the underlying tables. All interactions with the tables, and the data store therein, should take place through the "filter" of a form. In this way you are able to control how the users see and interact with that data.

 

[davies107]

Your users should have NO direct access to the underlying tables. All interactions with the tables, and the data store therein, should take place through the "filter" of a form. In this way you are able to control how the users see and interact with that data.

what if the users are the one who is responsible for the updating of data? all i do is to create the database or system. should i still follow your suggestion?

 

[John Big Booty]

what if the users are the one who is responsible for the updating of data? all i do is to create the database or system. should i still follow your suggestion?

I will reiterate

... All interactions with the tables, and the data store therein, should take place through the "filter" of a form. In this way you are able to control how the users see and interact with that data.

 

[davies107]

okay i got it. thanks again.

 

[davies107]

i got this already. but, i did not apply the programming/coding or any property sheet event ... what i did was just a simple validation rule and validation text in the Tables in the Design View mode... so far, it gives me the right values and it satisfies the users...

Thanks.

 

[MatthewMc]

I guess you will use a Command Button to commit the entered values to your underlying table. So in it's On Click event you could use some code along the lines of;

If Nz(Me.YourXField, 0) < 5 Or Nz(Me.YourXField, 30) > 25 Then
     MsgBox "Please enter a numeric value between 5 and 25"
     Me.YourXField.SetFocus
     Exit Sub
End If

You would need a similar piece of code to test each of your other fields. Followed by the code you will use to commit the values to your tables.

Thanks John for this example. I am a beginner at Access but a quick learner. I'm sure I am like many others (I self learn; reverse engineer) Great examples like this help a lot. I was able to use this in an AfterUpdate in VBA. I am slowly learning VBA, this helps me understand so much more.

 

[davies107]

Hello everyone,

just a follow up questions.

how to create multiple validation rule either on Tables or Forms, like for example:

if the user will click Mechanical, it will create 4 validation rules since Mechanical has 4 subcategories (Deck, Flareboom, LSF and LQ).


Happy New Year everyone!!!

 

[jdraw]

Not sure I understand, but you might research Cascading combos.

My guess is you are looking at specific edit/validation routines in vba.

In plain English what are you trying to do?

 

[davies107]

Not sure I understand, but you might research Cascading combos.

My guess is you are looking at specific edit/validation routines in vba.

In plain English what are you trying to do?

i have an idea about cascading combos since my database is using it already.

my first post is what im trying to do plus the follow up question:

if the user will chose Deck (in the combobox) theres a code that will execute the validation rule like data entry should be greater than 5 but less than 25, since the user should do a manual entry here. If the user will chose Flareboom (in the combobox) theres is another code that will execute the validation rule like data entry should be greater than 10 but less than 75.... and so on.... its like, each record in the combobox has a different validation rule.

 

[jdraw]

1-You would use some sort of if statements, along these lines (untested just for example)

if me.combo.value ="value1" then
do you validation for value1 here
else if me.combo.value = "value2 then
do your validation for value2 here
....
else
end if

2-You could use a select case

see http://msdn.microsoft.com/en-us/library/office/gg278454.aspx for examples

 

[davies107]

im NOT really good with programming but i will try this one. i hope i will get this right this time.

Thanks jdraw!