[AccessD] Request for help with Function

Hi Bob,

To late at night for me to test this but perhaps it could help you.
Have the function return a value (That would also make it a function)
If it's false then cancel and undo

HTH

Bill

Code Sample 2
===============================
Private Function isValidStatus() as boolean
    isValidStatus = True ' Assume OK
Dim Cancel As Integer
If Me.ActiveControl <> "A" And Me.ActiveControl <> "R" And Me.ActiveControl
<> "C" And Me.ActiveControl <> "P" And Me.ActiveControl <> "S" And
Me.ActiveControl <> "N" Then
    MsgBox "The valid choices are 'A' (available), 'R' (reserved), 'C'
(chorus/cast), 'P' (pre-sale), 'S' (sold), and 'N' (not available)."
    isValidStatus = false
End If
End Function
===============================

Code Sample 3
===============================
Private Sub EventSeatA001_BeforeUpdate(Cancel As Integer)
if  isValidStatus (Me.ActiveControl.Value) = false then
    Cancel = True
    Me.ActiveControl.Undo
end if

End Sub


----- Original Message ----- 
From: "Bob Gajewski" <rbgajewski at roadrunner.com>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Sent: Tuesday, January 13, 2009 10:46 PM
Subject: [AccessD] Request for help with Function


Hello Friends

I have a form that contains 184 one-character text fields (EventSeatA001,
EventSeatA002, etc) - seats in an auditorium. All fields are defaulted to
"A".

I need to check the value that the user inputs, and if an invalid character
was entered, I want to display an error message and return the field to the
value from before it was changed.

I have the following code in the _BeforeUpdate CBF for each of the 184
fields, and it works just fine.

Code Sample 1
===============================
If Me.ActiveControl <> "A" And Me.ActiveControl <> "R" And Me.ActiveControl
<> "C" And Me.ActiveControl <> "P" And Me.ActiveControl <> "S" And
Me.ActiveControl <> "N" Then
    MsgBox "The valid choices are 'A' (available), 'R' (reserved), 'C'
(chorus/cast), 'P' (pre-sale), 'S' (sold), and 'N' (not available)."
    Cancel = True
    Me.ActiveControl.Undo
End If

===============================

However, I would prefer to put this validation into a Function and just call
it in each _BeforeUpdate. This would make maintenance SO much easier. This
is where I am struggling. I wrote the Function code and made the call in the
_BeforeUpdate ...

Code Sample 2
===============================
Private Function isValidStatus()
Dim Cancel As Integer
If Me.ActiveControl <> "A" And Me.ActiveControl <> "R" And Me.ActiveControl
<> "C" And Me.ActiveControl <> "P" And Me.ActiveControl <> "S" And
Me.ActiveControl <> "N" Then
    MsgBox "The valid choices are 'A' (available), 'R' (reserved), 'C'
(chorus/cast), 'P' (pre-sale), 'S' (sold), and 'N' (not available)."
    Cancel = True
    Me.ActiveControl.Undo
End If
End Function
===============================

Code Sample 3
===============================
Private Sub EventSeatA001_BeforeUpdate(Cancel As Integer)
isValidStatus (Me.ActiveControl.Value)
End Sub
===============================

... and the validation works fine, but as soon as I click on [OK] on the
message box, I get a system error message:

Code Sample 4
===============================
Run-time error 13:

Type mismatch
===============================

If anyone can point me in the right direction, I would very appreciative.

Thanks,
Bob Gajewski


P Please consider the environment before printing this e-mail

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com