Text box validation based on combobox selection

[kdirvin]

Hello all,

I am using Access 2007 and have a form to enter debit and credits for a bank account. I have an unbound combo box named "Transaction" and it contains 2 options, Debit and Credit. There is also an "Amount" text box in which to enter the debit or credit amount. When the user selects Debit from the combobox, I need a validation rule to prevent the user from entering a positive number into the "Amount" field.

I inserted the following code into the form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Transaction= "Debit" And Len(Me.Amount) > 0 Then
Cancel = True
MsgBox "Debits must be a negative amount."
End If
End Sub

My problem is that when I select Debit from the combobox, the message appears regardless of whether I enter a positive or negative number into the "Amount" field.

I do not know what I am missing in my code to ensure that the message appears only when a positive number is entered.

Thank you for any help that you can provide!

 

[DCrake]

To turn a debit into a credit is Amount * -1

Do a check

If Amount > 0 And Combo = "Credit" Then
  Amount = AMount * -1
Endif

 

[JANR]

FYI

If Me.Transaction= "Debit" And Len(Me.Amount) > 0 Then

Len is not what you want, Len is for mesuring the lenght of a string.

so

If Me.Transaction= "Debit" And (Me.Amount) > 0 Then

would be more of what you want to test for

JR

 

[kdirvin]

JR - I didn't know this about Len. Thank you!

DCrake - is this code for the BeforeUpdate event of the form? Or should i put it somewhere else.

Also, I am a bit confused as to why I multiply Amount by (-1) if Combo = "Credit". I need the credits to be entered as positive numbers.

Thank you both!

 

[vbaInet]

Did your code not work?

If Me.Transaction= "Debit" And Me.Amount > 0 Then
    Cancel = True
    MsgBox "Debits must be a negative amount."
Else If Me.Transaction= "Credit" And Me.Amount < 0 Then
    Cancel = True
    MsgBox "Credits must be a positive amount."
End If

 

[DCrake]

Credit/Debit sorry depends on what side of the fence you are sitting on.

 

[vbaInet]

Credit/Debit sorry depends on what side of the fence you are sitting on.

I like the credit side

 

[kdirvin]

I, too, like the credit side and need more of it!

vbaInet, I used your code for the BeforeUpdate event of my form, but I am still able to put negative amounts for Credit and positive for Debit. No error message pops up at all. The combobox from which I select "Debit" and Credit" is unbound. I do not know if that is a factor.

Am I right in making this a form-level validation rule and not a field-level one?

Thank you...

 

[vbaInet]

What is the data type of your Amount field? Also, are you sure the first column of the Credit/Debit combo box holds the values Credit, Debit?

 

[kdirvin]

I apologize for my delayed reply. There is only one column in the combobox and it lists the values "Debit" and "Credit." The Amount field is of Currency data type.

 

[vbaInet]

Did you say it is unbound? I missed that. It will only fire if you have made changes to bound controls on the form. It also will not fire when you move through records.

Move the code to the Before Update event of the Amount control.

 

[kdirvin]

I moved it to the Amount control and it works like a charm! Thank you so much!

 

[vbaInet]

You're welcome!

You will need some validation in the After Update event of the combo box as well. For example, the combo box's value is Credit and the amount is positive, if you change the combo to Debit the amount remains positive.