Results 1 to 12 of 12
  1. #1
    oleBucky's Avatar
    oleBucky is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Feb 2011
    Location
    Inside the Loop
    Posts
    100

    Simple Problem with Validations

    Simple Problem... yeah right... for those who know what the h*?# they are doing. I am trying to set a validation rule for a text box to allow null values or only integer values between 1 and 50 inclusive.

    I am having all kinds of frustration regarding If statment syntax on a Before Update event for this validation. I cannot get the code to recognize the fact I do not want any decimal values entered. In this form, they could only be a mistake. This is my current unsuccessful code. Do your worst!

    Code:
     
    Private Sub txtHCtrl1Rng_BeforeUpdate(Cancel As Integer)
        If Me.txtHCtrl1Rng <> Null Then
            If Int(Val(Me.txtHCtrl1Rng)) <> Val(Me.txtHCtrl1Rng) Then
                MsgBox ("Please enter an integer value")
            ElseIf Val(Me.txtHCtrl1Rng) < 1 Or Val(Me.txtHCtrl1Rng) > 50 Then
                MsgBox ("Please enter an integer value between 1 and 50 (inclusive).")
        End If
        End If
     
    End Sub


  2. #2
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The problem is the first IF() condition. You cannot test for NULL like you are trying to do. Look at the following code:
    Code:
    Sub TestNull()
       If 1 = 1 Then
          MsgBox "1 equals 1"
       End If
    
       If Null = Null Then
          MsgBox "True"
       Else
          MsgBox "Null = Null is false"
       End If
       
       If Null <> Null Then
          MsgBox "True"
       Else
          MsgBox "Null <> Null is false"
       End If
       
    End Sub
    You will never see a message box with TRUE. The only way to test for NULLs is to use the IsNull() function. Try this:

    Code:
    Private Sub txtHCtrl1Rng_BeforeUpdate(Cancel As Integer)
       Dim vTemp
    
       vTemp = Me.txtHCtrl1Rng
    
       If Not IsNull(vTemp) Then
          If Int(Val(vTemp)) <> Val(vTemp) Then
             MsgBox ("Please enter an integer value")
          ElseIf Val(vTemp) < 1 Or Val(vTemp) > 50 Then
             MsgBox ("Please enter an integer value between 1 and 50 (inclusive).")
          End If
       End If
    
    End Sub

  3. #3
    oleBucky's Avatar
    oleBucky is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Feb 2011
    Location
    Inside the Loop
    Posts
    100
    A solution and an explanation. Don't see that so often! Thanks so much. That gets the Null check in line as well as the values below 1 or over 50.

    However, the integer check is not working. 12.4 goes in as 12 and 12.6 goes in as 13. I have the record source field property as well as the form control properties set to 0 for the Decimal Places property value. Might this have bearing? In the mean time I will fiddle with the Decimal Places property and report back.

  4. #4
    oleBucky's Avatar
    oleBucky is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Feb 2011
    Location
    Inside the Loop
    Posts
    100
    I changed all the Decimal Value properties to Auto to no avail. I went as far as deleting the control and redoing it with Bill's defaults. No help there either. To repeat, values enter as decimals are rounded, not truncated, to integers apparently before the Before Update code has a chance to do it's evaluation. Might I need to change the data type from Long Integer? This is problematic given my existing relationships (data type compatibility). I take that back, the relationships would not be an issue.
    Last edited by oleBucky; 04-10-2011 at 05:50 PM. Reason: Flawed observation corrected.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If a control is bound to a field that is a integer type, and a single/double type number (12.6) is entered into the control, the number is automatically converted to an integer.

    Is this bad? You are going to make them re-enter the number if it is not an integer.

    BTW, since the control is bound to an integer type field, you don't need to use the VAL() function in the code. If the field is a text type, you would need the VAL() function.

  6. #6
    oleBucky's Avatar
    oleBucky is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Feb 2011
    Location
    Inside the Loop
    Posts
    100
    Quote Originally Posted by ssanfu View Post
    Is this bad? You are going to make them re-enter the number if it is not an integer.
    My thought was as the hardcopy source for the data entry will only include integers from 1 to 50 (or nothing), if the data is entered as a decimal, then there is certainly some sort of type-o; Such as "36" entered as ".6" or the data entry person is transcribing the incorrect source data (i.e. looking at the incorrect field on the hardcopy form). The validation would flag such errors.

    Is there an event that looks at the data entered into a control before the automatic rounding?

  7. #7
    oleBucky's Avatar
    oleBucky is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Feb 2011
    Location
    Inside the Loop
    Posts
    100
    After changing the table field size property to "Double", the message box works as expected. However, unless you remove the control Decimal Places setting of 0, the value is still rounded to the nearest integer after terminating the message box. You then have to tab back to the control and make corrections.

    Upon terminating the message box, is there a way to clear the incorrect decimal value that generated the message box then return focus to the control in question rather than moving to the next tab stop?

    I will continue my experimentation, remembering to experiment only a working copy of the DB ;-).

  8. #8
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Not sure, but try:

    Code:
    Me.txtHCtrl1Rng = NULL
    Me.txtHCtrl1Rng.SetFocus
    I will continue my experimentation, remembering to experiment only a working copy of the DB ;-).

  9. #9
    oleBucky's Avatar
    oleBucky is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Feb 2011
    Location
    Inside the Loop
    Posts
    100
    Code:
    Me.txtHCtrl1Rng = NULL
    Me.txtHCtrl1Rng.SetFocus
    OK, I'm really going to show my VB ignorance. Does this go with the event on the control or with the message box? My guess is it should be with some sort of On Click event with the OK button on the message box.

  10. #10
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Came up with a different plan.
    From Help: BeforeUpdate, AfterUpdate Events — Event Procedures Example
    The following example shows how you can use a BeforeUpdate event procedure to check whether a product name has already been entered in the database. After the user types a product name in the ProductName box, the value is compared to the ProductName field in the Products table. If there is a matching value in the Products table, a message is displayed that informs the user that the product has already been entered.

    Private Sub ProductName_BeforeUpdate(Cancel As Integer)
    If (Not IsNull(DLookup("[ProductName]", "Products", "[ProductName] ='" & Me!ProductName & "'"))) Then
    MsgBox "Product has already been entered in the database."
    Cancel = True
    Me!ProductName.Undo
    End If
    End Sub
    So this is how I modified your code:
    Code:
    Private Sub txtHCtrl1Rng1_BeforeUpdate(Cancel As Integer)
       Dim vTemp
    
       vTemp = Me.txtHCtrl1Rng
    
       If Not IsNull(vTemp) Then
          If Int(Val(vTemp)) <> Val(vTemp) Then
             MsgBox ("Please enter an integer value")
             Cancel = True
             Me!txtHCtrl1Rng.Undo
          ElseIf Val(vTemp) < 1 Or Val(vTemp) > 50 Then
             MsgBox ("Please enter an integer value between 1 and 50 (inclusive).")
             Cancel = True
             Me!txtHCtrl1Rng.Undo
          End If
    
       End Sub

  11. #11
    oleBucky's Avatar
    oleBucky is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Feb 2011
    Location
    Inside the Loop
    Posts
    100
    Excellent! That worked perfectly. Thanks to ssnafu, this is no longer fubar. I'll mark this solved.

    One quick question. Why did you use an exclamation point rather than a period in your Undo lines?
    Last edited by oleBucky; 04-12-2011 at 05:01 PM. Reason: Added inquiry

  12. #12
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    One quick question. Why did you use an exclamation point rather than a period in your Undo lines?
    That was the way the example has it.

    I didn't have time to test it, but I figured if it was in the example, it couldn't be too wrong....

    Try changing the bang to a dot and see if it still works.....

    Glad it is working for you....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 10-21-2010, 08:24 AM
  2. Validations
    By Icewolf0927 in forum Forms
    Replies: 1
    Last Post: 09-24-2010, 09:54 AM
  3. Simple Export Not So Simple
    By jgelpi16 in forum Programming
    Replies: 11
    Last Post: 09-01-2010, 07:23 AM
  4. Simple query problem
    By rajnag in forum Access
    Replies: 4
    Last Post: 08-19-2010, 05:09 AM
  5. simple form problem
    By kcsun in forum Forms
    Replies: 0
    Last Post: 08-12-2010, 12:28 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums