By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,039 Members | 1,824 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,039 IT Pros & Developers. It's quick & easy.

Problems with an If statement

100+
P: 283
Im trying to write an if statement for a number box on my form. I want to set it so you can only enter in a set number of characters in the box. For example you cant enter any more or less than 5 numbers. If you do you get an error. The code that I wrote is half working any suggestions would be greatly apperciated.

I put this in the onEnter

Expand|Select|Wrap|Line Numbers
  1.  Dim number, zipCode As Integer 
  2. Dim error As String 
  3.  
  4. number = 0 
  5. zipCode = 5 
  6.  
  7. If number < zipCode  
  8. error = " Not enough numbers. \n" 
  9.  
  10. ElseIf number > zipCode Then 
  11. error = " To many numbers in field. \n" 
  12. End If 
  13. 'End If 
  14.  
The part that seems to work right now is if you enter in more than 5 numbers. For some reason I can not get it to error out if you enter in less than 5.
Mar 11 '10 #1
Share this Question
Share on Google+
11 Replies


missinglinq
Expert 2.5K+
P: 3,532
The OnEnter event fires when you enter the control, which makes it inappropriate for this task. This code will do the validation you want:
Expand|Select|Wrap|Line Numbers
  1. Private Sub NumberBoxName_BeforeUpdate(Cancel As Integer)
  2.  If Len(Me.NumberBoxName) <> 5 Then
  3.   MsgBox "Number must be 5 digits long"
  4.   Cancel = True
  5.  End If
  6. End Sub
But you have to understand that this only checks for how many characters are in the field.

If the field is defined as a Number Datatype in the underlying table, Access will insure that if anything except digits (numbers) are entered a message will inform the user of this.

If the field is defined as a Text Datatype in the underlying table, you'll need to check for this. You can do that with this modification of the previous code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub NumberBoxName_BeforeUpdate(Cancel As Integer)
  2.  If IsNumeric(Me.NumberBoxName) Then
  3.  
  4.    If Len(Me.NumberBoxName) <> 5 Then
  5.     MsgBox "Number must be 5 digits long"
  6.     Cancel = True
  7.    End If
  8.  
  9.  Else
  10.  
  11.   MsgBox "This field requires Numbers only!"
  12.   Cancel = True
  13.  
  14.  End If
  15.  
  16. End Sub
You need to replace NumberBoxName with the actual name of your textbox control.

Linq ;0)>
Mar 11 '10 #2

patjones
Expert 100+
P: 931
You could also just set the input mask on the box to "00000". Then in the error handler for the form...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Error(DataErr As Integer, Response As Integer)
  2.  
  3. On Error GoTo Err_Form_Error
  4.  
  5. If Screen.ActiveControl.Name = "text box name" And DataErr = 2279 Then
  6.      MsgBox "Please type in five digits for the zip code!", vbOKOnly + vbExclamation, "Zip Code Entry"
  7. End If
  8.  
  9. Response = acDataErrContinue
  10.  
  11. Exit_Form_Error:
  12.     Exit Sub
  13.  
  14. Err_Form_Error:
  15.     MsgBox "Error " & Err.Number & ": " & Err.Description & ". Subroutine Form_Error..."
  16.     Resume Exit_Form_Error
  17.  
  18. End Sub

Also, since zip codes can start with "0" and aren't exactly numbers in the sense of Access data types, you would want to make sure the zip code column in the table is designed to hold text values of length five, as missinglinq mentions.

Pat
Mar 11 '10 #3

100+
P: 283
Thank you both missinglinq and zepphead,

I have been racking my brain over this for 2 days now. I would set the mask type which would be way easier as mentioned by zepphead but the problem is the version I am using is a barebones version so i can not even use the mask type at the moment which is really limiting things I can do. But on the upside I get to learn a lot more code which I want to learn anyway.

I reset the box to a text box instead of a number box. Realized that later on I could not enter in starting zeros as a number field. (You would think they would change that in future updates or versions of Access). I plugged in (linqs) code and it works great :D thank you so much for the help. I did figure out that I could change the field size limiting it to only 5 characters to be entered but I want to make sure that you can not enter in less than 5 as well, which the code does. Very nice thank you so much.

Just wondering is there any kind of book or very detailed site where I can read up on how all of this works more that either of you would recommend?? Or do you just have to learn it all from other people and trial and error??
Mar 12 '10 #4

TheSmileyCoder
Expert Mod 100+
P: 2,321
I can recommend the VBA for Dummies. Thats the only VBA book ive ever read, but I have been on a 2 day course in VBA programming for Access. That was enough to get me started, and since then I've just expanded my knowledge with the projects I've worked.
Mar 12 '10 #5

100+
P: 283
Cool thanks TheSmileyOne! There are so many books out there its hard to judge which is good and which is just ok.

Using VBA is new for me, most of my experience is with C++ so trying to get VBA to do what I want is not always working.

Apperciate the help :D
Mar 12 '10 #6

missinglinq
Expert 2.5K+
P: 3,532
In addition to Smiley's suggestion, I recommend reading Access XXX All In One Desk Reference for Dummies.The single biggest problem we run into in trying to help newbies is that they don't even have enough understanding of the ABCs of Access to frame a proper question. They frequently don't know what objects and controls are actually called, or what each one is used for, and this muddles the waters when asking for help! Knowing the basics is first priority in becoming proficient in Access/VBA. Access

VBA is an incredibly complex language. Developers who are considered "very experienced," if they're honest, will tell you that even after years of developing Access databases, they learn new things all the time! To me, this is what makes it an exciting area to work in!

Linq ;0)>
Mar 12 '10 #7

100+
P: 283
Thanks Linq,
I will look for that one as well :) I am going to run to the book store tomorrow or see if i can find both of these to download.

I think with any kind of programming no one can know it all. There are so many ways to create things with programming and so many different ways you can use it the possiblities seem limitless. Like you said that is what makes it exciting is because there is always something more to learn or figure out.

I have a question for Zepphead or anyone I was looking over the code you wrote for the error box and it looks great im just trying to understand how it works a little better. In this line;
Expand|Select|Wrap|Line Numbers
  1. If Screen.ActiveControl.Name = "text box name" And DataErr = 2279 Then 
  2.  
where it says ActiveControl.Name (the .Name part do you put the name of the text box?) also the DataErr=2279 is that a pre-generated error or are you creating that error and then referencing it toward the bottom of the code? With the Err_form_Error:
Mar 12 '10 #8

NeoPa
Expert Mod 15k+
P: 31,494
@slenish
  1. No. In this case the name of the actual property being referenced is .Name.
    The name of the control, as you know it, is held in this.
  2. It is pre-generated. The actual error message associated with this error is something like :
    The value you entered isn't appropriate for the input mask '|' specified for this field.
Mar 13 '10 #9

TheSmileyCoder
Expert Mod 100+
P: 2,321
The "text box name" is where you should put the name of your textbox.
Mar 13 '10 #10

patjones
Expert 100+
P: 931
The code 2279 is just the error number for input mask violations; just about every error has a number associated with it, and what is really cool here is that the code I wrote above can be nicely extended to trap any error:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Error(DataErr As Integer, Response As Integer)
  2.  
  3. On Error GoTo Err_Form_Error
  4.  
  5. Select Case DataErr
  6.  
  7.      Case some_error_num1:
  8.  
  9.            'Code to handle some_error_num1
  10.  
  11.      Case some_error_num2:
  12.  
  13.            'Code to handle some_error_num2
  14.  
  15.      Case Else
  16.  
  17.            'Code to handle unforseen errors
  18.  
  19. End Select
  20.  
  21. Response = acDataErrContinue
  22.  
  23. Exit_Form_Error:
  24.     Exit Sub
  25.  
  26. Err_Form_Error:
  27.     MsgBox "Error " & Err.Number & ": " & Err.Description & ". Subroutine Form_Error..."
  28.     Resume Exit_Form_Error
  29.  
  30. End Sub

You can find the error number list in many places online, and trap whatever error you think could occur in your form.

Pat
Mar 13 '10 #11

NeoPa
Expert Mod 15k+
P: 31,494
@NeoPa
By the way, I should have added earlier that it's always a good idea (indeed expected) to post the error message with any error reported. As Pat said, we can always dig it up, but people rarely appreciate being put in the position where that is necessary. Not a big deal this time, but please remember on future occasions.
Mar 15 '10 #12

Post your reply

Sign in to post your reply or Sign up for a free account.