Validation of Data from a list box

[Guest]

Hello out there, has any on had this problem brfore?

I have a form which is bound to a transaction table containg currency
numbers, reson codes (RC), dates, an ID and a record number (auto number). A
number may be + (Credit) or - (Debits). A Reason code table (RC_Table) keeps
tract of reasons for transactions and weather they are C or D type and a
description. The form requests the persons ID (a combo box allows selection
of the Id (which is the key by which all data is retrieved from the
transaction table). The next field is recaon code (RC is the control source)
a combo box based on the Reason Code table RC_table. Using the combo box the
user selects the reason (and at the same time RC_table.Type (C or D) is
dispplayed).

SELECT RC_table.RC, RC_table.Reason, RC_table.Type FROM RC_table ORDER BY
[RC];

The next field is amount. And that's where the problem starts. I can't
capture that selected "Type" to perfrom the proper validation on the amout
field which should be + or - depending on the "Type".

If Rc_Type = "C" and Amount < 0 then
MsgBox "this is a ......."
Else If RC_Type = "D" and Amount > 0 then
MsgBox "this is a ......."
End if
A 0 is ok since it may be an opening balance and has no effect of the
account in question.

Please help me find a way to retrieve the Field RC_Type for the list box and
pass it to the Amout field so i can perfrom the test "before update"

Thank you in advance.

Dick

 

[Steve Schapel]

Dick,

I can't see what the problem is. You have a control on the form that
displays the Type, as "D" or "C", right? I assume this is an unbound
textbox (or do you mean this is a listbox?), which gets its value based
on the selection in the RC combobox. Whatever, what is wrong when you
try to use the kind of If...Then code you used in your example? It
seems to me that it would work. Another way to retrieve the Type value
would be to refer back to the RC combobox, e.g....
If Me.RC.Column(2) = "C" and Amount < 0 Then
MsgBox "this is a ......."

 

[Guest]

Hi Steve,

By the way Merry Christmas!

Well I used you alternet method of obtaininig the C or D and it worked. The
data was in combo box, e.g. RC and Type are shown to the user based on the
reason they choose. Now the error check works, but how do i force a
correction.

Do I setFocus during the error check?

Best regards,

Dick

Hallp New Year as well!

Dick,

I can't see what the problem is. You have a control on the form that
displays the Type, as "D" or "C", right? I assume this is an unbound
textbox (or do you mean this is a listbox?), which gets its value based
on the selection in the RC combobox. Whatever, what is wrong when you
try to use the kind of If...Then code you used in your example? It
seems to me that it would work. Another way to retrieve the Type value
would be to refer back to the RC combobox, e.g....
If Me.RC.Column(2) = "C" and Amount < 0 Then
MsgBox "this is a ......."

--
Steve Schapel, Microsoft Access MVP

Hello out there, has any on had this problem brfore?

I have a form which is bound to a transaction table containg currency
numbers, reson codes (RC), dates, an ID and a record number (auto number). A
number may be + (Credit) or - (Debits). A Reason code table (RC_Table) keeps
tract of reasons for transactions and weather they are C or D type and a
description. The form requests the persons ID (a combo box allows selection
of the Id (which is the key by which all data is retrieved from the
transaction table). The next field is recaon code (RC is the control source)
a combo box based on the Reason Code table RC_table. Using the combo box the
user selects the reason (and at the same time RC_table.Type (C or D) is
dispplayed).

SELECT RC_table.RC, RC_table.Reason, RC_table.Type FROM RC_table ORDER BY
[RC];

The next field is amount. And that's where the problem starts. I can't
capture that selected "Type" to perfrom the proper validation on the amout
field which should be + or - depending on the "Type".

If Rc_Type = "C" and Amount < 0 then
MsgBox "this is a ......."
Else If RC_Type = "D" and Amount > 0 then
MsgBox "this is a ......."
End if
A 0 is ok since it may be an opening balance and has no effect of the
account in question.

Please help me find a way to retrieve the Field RC_Type for the list box and
pass it to the Amout field so i can perfrom the test "before update"

Thank you in advance.

Dick

 

[Steve Schapel]

Dick,

Probably like this, assuming you are using the Before Update event of
the Amount textbox...

If Me.RC.Column(2) = "C" and Me.Amount < 0 then
MsgBox "this is a ......."
Cancel = True
... etc

By the way, just a comment... I would not do it this way myself. I
would not require the user to enter a -ve amount. In my opinion, it
would be better if all amounts entered were positive... the entry of D
or C already tells you whether the value needs to be added or subtracted
in your calculations.

Happy New Year to you too!