Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Validation Rules (A2K SP-3)

    I have a frmCheckInEdit that has a field DepositRefundAmt that has a validation rule: 999 or Is Null. 999 is used to signify that the deposit amount field, SecurityDeposit, is being transferred to another receipt number. Other dollar amounts can be put into DepositRefundAmt but not from this form. On the form, I want to add another field, TrfTo, that is the receipt number field to which the deposit is being transferred. What I want to accomplish is:
    1. To make the field, TrfTo, mandatory if 999 is entered into the DepositRefundAmt field
    2. That 999 can not be entered into the DepositRefundAmt field unless DepositRefundAmt is not null
    3. That the first digit of the number entered into TrfTo= the field tblRental.PropertyID (Byte field) which is in the query that drives the form.
    4. I am planning to make TrfTo a Long Integer field.

    Any suggestion would be welcomed.

    Thanks

    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation Rules (A2K SP-3)

    You can handle 1. in the Before Update event of the form. If TrfTo is blank and DepositRefundAmt is 999:
    - Display a MsgBox.
    - Set focus to TrfTo.
    - Set Cancel to True.

    You can handle 2. in the Before Update event of DepositRefundAmt. If the OldValue property is null and Value is 999:
    - Display a MsgBox.
    - Set Cancel to True.

    If the first digit ofTrfTo is fixed, why let it be entered by the user? You can prefix it to the number entered by the user.

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation Rules (A2K SP-3)

    What is the correct syntax for checking if the field is blank?

    What I tried was
    If Me.TrfTo=blank and Me.DepositRefundAmt=999 then
    MsgBox "You...Transferred"
    Me.TrfTo.SetFocue
    Cancel=True
    End If

    I also tried Me.TrfTo =null

    Your question re why enter the first digit of the receipt nimber.
    The receipt number is built by combining the PropertyID with the OrderID. The operators are used to working with the receipt numbers and I am afraid that having them enter only a portion of the number, it will be confusing to them. I was trying to prevent a typo by checking the first digit.

    Tom

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation Rules (A2K SP-3)

    To check if a value is null in VBA code, you use the IsNull function:

    If IsNull(Me.TrfTo) And Me.DepositRefundAmt = 999 Then

    To check the TrfTo, you can use its Before Update event.

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation Rules (A2K SP-3)

    My code is now

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.LastModified = Date
    If IsNull(Me.TrfTo) And Me.DepositRefundAmt = 999 Then
    MsgBox "You must enter the receipt number where the Deposit is being transferred a"
    Me.TrfTo.SetFocus
    Cancel = True
    End If
    End Sub

    If the form is closed afer entering 999 but with out entering a receipt number the message box message properly displays but when you click OK then a error message appears saying that the record can not be saved and do you want to close the database. If you say no and then enter the receipt number it closes find. Can you suppress the 2nd error message"

    Tom

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation Rules (A2K SP-3)

    In such a situation I prevent the user from closing the form by the x in the upper right corner. I provide a Close command button in whose On Click code I check whether all conditions are satisfied.

    <!profile=Ready4Data>Ready4Data<!/profile> posted a nice example of how to prevent the user from closing a form in <post#=301,350>post 301,350</post: >. You'll find another eaxmple in <post#=166,577>post 166,577</post: >.

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation Rules (A2K SP-3)

    This is the code that I have on the close command button

    Private Sub cmdClose_Click()

    If (Me.DepositRefundAmt = 999 And Me.TrfTo > 0) Or IsNull(Me.DepositRefundAmt) Then
    blnCanClose = True
    DoCmd.Close acForm, "frmCheckInEdit"
    Else
    MsgBox "If 999 has been entered, you must enter the receipt number where the Deposit is being transferred"
    End If

    End Sub


    The form will close using the Command Close Button if DepositRefundAmt is null or when DepositRefundAmount is 999 and TrfTo has a value > O. (I could not get the IsNotNull to work in place of TrfTo > 0.

    The X Close button is disabled if both fields are empty. However, if 999 is entered into the DepositRefundAmt, then X close button is no longer disabled.

    Any Suggestions?

    Tom

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation Rules (A2K SP-3)

    Just set the Close Button property of the form to No, or set the Control Box property to No. The x close button will not be displayed then.

  9. #9
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation Rules (A2K SP-3)

    Hans,
    Many thanks. I set the Control Box to No.

    I need another test.

    DepositRefundAmt on frmCheckInEdit has a validation rule of 999 or Is Null. I need to have a test that 999 can only be entered when Me.SecurityDeposit is greater than 0 (normally null) or Me.DepositTransferred is greater than 0 (normally null) . (It is OK for Me.SecurityDeposit to = $0.00 (Default Value) if Me.DepositRefundAmt is null and Me.DepositTransferred = null.

    Tom

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation Rules (A2K SP-3)

    You can add an extra test to the Before Update event of DepositRefundAmt:

    If Me.DepositRefundAmt = 999 And Not(Me.SecurityDeposit > 0 Or Me.DepositTransferred >0) Then
    MsgBox "Can't set DepositRefundAmt to 999.", vbExclamation
    Cancel = True
    End If

  11. #11
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation Rules (A2K SP-3)

    Thanks for your suggested code that I added to the Before Update event of DepositRefundAmt but something I can't find is keeping it from firing.

    I am enclosing a stripped down version of the database.

    Any Suggestions?

    Tom

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation Rules (A2K SP-3)

    The problem is with Null values. Try this:

    If Me.DepositRefundAmt = 999 And Not (Nz(Me.SecurityDeposit) > 0 Or Nz(Me.DepositTransferred) > 0) Then

  13. #13
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation Rules (A2K SP-3)

    Adding the Nz function corrected the problem.

    I want to add one more test. When Me.DepositTransferred is greater than 0, then I want to require that the receipt number be entered into MeTrfFrom.

    I added code to the Before Update event of the form. When 100 is entered into DepositTransferred and the Close Form button is pressed, the warning message appears properly. When OK is pressed on the warning message, the form closes so there is no opportunity to enter the receipt number.

    My code is

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.LastModified = Date
    If IsNull(Me.TrfTo) And Me.DepositRefundAmt = 999 Then
    MsgBox "You must enter the receipt number where the Deposit is being transferred."
    Me.TrfTo.SetFocus
    Cancel = True
    End If
    If IsNull(Me.TrfFrom) And Me.DepositTransferred > 0 Then
    MsgBox "You must enter the receipt mumber from the record where the deposit is coming from.", vbExclamation
    Me.TrfFrom.SetFocus
    Cancel = True
    End If
    End Sub


    Any suggestions?

    Tom

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation Rules (A2K SP-3)

    Build the test code into the On Click event of the Close button.

  15. #15
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation Rules (A2K SP-3)

    I am still having trouble getting the test to work.. I tried adding this code to the On Click event of the close button

    Private Sub cmdClose_Click()

    If (Me.DepositRefundAmt = 999 And Me.TrfTo > 0) Or IsNull(Me.DepositRefundAmt) Then
    blnCanClose = True
    DoCmd.Close acForm, "frmCheckInEdit"
    Else
    MsgBox "If 999 has been entered, you must enter the receipt number where the Deposit is being transferred"
    End If
    If IsNull(Me.TrfFrom) And Me.DepositTransferred > 0 Then
    MsgBox "You must enter the receipt mumber from the record where the deposit is coming from.", vbExclamation
    Else
    blnCanClose = True
    DoCmd.Close acForm, "frmCheckInEdit"
    End If
    End Sub

    The offending code is the first line of the second if statement

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •