Problems with an If statement

[slenish]

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

 Dim number, zipCode As Integer 
Dim error As String 
 
number = 0 
zipCode = 5 
  
If number < zipCode  
error = " Not enough numbers. \n" 
  
ElseIf number > zipCode Then 
error = " To many numbers in field. \n" 
End If 
'End If

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.

[missinglinq]

The OnEnter event fires when you enter the control, which makes it inappropriate for this task. This code will do the validation you want:

Private Sub NumberBoxName_BeforeUpdate(Cancel As Integer)
 If Len(Me.NumberBoxName) <> 5 Then
  MsgBox "Number must be 5 digits long"
  Cancel = True
 End If
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:

Private Sub NumberBoxName_BeforeUpdate(Cancel As Integer)
 If IsNumeric(Me.NumberBoxName) Then
 
   If Len(Me.NumberBoxName) <> 5 Then
    MsgBox "Number must be 5 digits long"
    Cancel = True
   End If
 
 Else
  
  MsgBox "This field requires Numbers only!"
  Cancel = True
 
 End If

End Sub

You need to replace NumberBoxName with the actual name of your textbox control.

Linq ;0)>

[patjones]

You could also just set the input mask on the box to "00000". Then in the error handler for the form...

Private Sub Form_Error(DataErr As Integer, Response As Integer)

On Error GoTo Err_Form_Error

If Screen.ActiveControl.Name = "text box name" And DataErr = 2279 Then
     MsgBox "Please type in five digits for the zip code!", vbOKOnly + vbExclamation, "Zip Code Entry"
End If

Response = acDataErrContinue

Exit_Form_Error:
    Exit Sub
    
Err_Form_Error:
    MsgBox "Error " & Err.Number & ": " & Err.Description & ". Subroutine Form_Error..."
    Resume Exit_Form_Error

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

[slenish]

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??

[TheSmileyCoder]

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.

[slenish]

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

[missinglinq]

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)>

[slenish]

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;

If Screen.ActiveControl.Name = "text box name" And DataErr = 2279 Then

where it says ActiveControl.N ame (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:

[NeoPa]

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;

If Screen.ActiveControl.Name = "text box name" And DataErr = 2279 Then

where it says ActiveControl.N ame (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:

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.

[TheSmileyCoder]

The "text box name" is where you should put the name of your textbox.

[patjones]

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:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
 
On Error GoTo Err_Form_Error

Select Case DataErr

     Case [B][I]some_error_num1[/I][/B]:

           'Code to handle some_error_num1

     Case [B][I]some_error_num2[/I][/B]:
           
           'Code to handle some_error_num2

     Case Else

           'Code to handle unforseen errors

End Select

Response = acDataErrContinue
 
Exit_Form_Error:
    Exit Sub
 
Err_Form_Error:
    MsgBox "Error " & Err.Number & ": " & Err.Description & ". Subroutine Form_Error..."
    Resume Exit_Form_Error
 
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

[NeoPa]

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.

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.