Validating form field to table data

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

I want users to enter their Cost Center number and
password into two form fields, respectively. I have a
table, which contains valid Cost Center numbers and
passwords. How can I validate data in the form against
related records in the table?

Thank you.
 
I want users to enter their Cost Center number and
password into two form fields, respectively. I have a
table, which contains valid Cost Center numbers and
passwords. How can I validate data in the form against
related records in the table?

Thank you.
Click to expand...

I'd suggest having them enter the cost center from a combo box
presenting a list of only the valid cost centers; set the combo's
Limit to List property to True and they won't have the opportunity to
make an error.

In the BeforeUpdate event of the Password textbox you could have code
like

Private Sub txtPassword_BeforeUpdate(Cancel as Integer)
If Me!txtPassword <> DLookUp("[Password]", "[tablename]",
"[CostCenter] = '" & Me!cboCostCenter & "'") Then
Msgbox "Neener neener neener, you can't come in!", vbOKOnly
Cancel = True
Me.Undo
End If
End Sub

A more polite message is optional... <g> as is more elaborate code to
decide what to do (other than erasing the form, as I suggest) in case
of an incorrect password.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
John,

I tried your suggestion and it worked famously. I am
relatively new to VBA and appreciate the time you spent
providing me with the suggestion.

Best regards
Kevin
-----Original Message-----
I want users to enter their Cost Center number and
password into two form fields, respectively. I have a
table, which contains valid Cost Center numbers and
passwords. How can I validate data in the form against
related records in the table?

Thank you.
Click to expand...

I'd suggest having them enter the cost center from a combo box
presenting a list of only the valid cost centers; set the combo's
Limit to List property to True and they won't have the opportunity to
make an error.

In the BeforeUpdate event of the Password textbox you could have code
like

Private Sub txtPassword_BeforeUpdate(Cancel as Integer)
If Me!txtPassword <> DLookUp("[Password]", "[tablename]",
"[CostCenter] = '" & Me!cboCostCenter & "'") Then
Msgbox "Neener neener neener, you can't come in!", vbOKOnly
Cancel = True
Me.Undo
End If
End Sub

A more polite message is optional... <g> as is more elaborate code to
decide what to do (other than erasing the form, as I suggest) in case
of an incorrect password.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
.
Click to expand...
 
Back
Top