Validation Form

  • Thread starter Thread starter Zack
  • Start date Start date
Z

Zack

How do i create a form to cross check a value against a
table with 200 values to see whether its valid or not.
 
You haven't given much info about what you are trying to do. Will using a
combo box to look up the values be any use?

If you just wanted to know if the entered value existed in your table, and
each value you were to compare against is unique, you could try:

Private Sub cmdCheckValue_Click()
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim strSQL as String

'Assuming the value to check is a string value, use a query to loacte
the control value in the specified field in the table
strSQL = "SELECT * FROM tblYourTable WHERE fldFieldToCheck = " & """" &
Me.txtYourTextBox & """"
Set db = CurrentDB
Set rs = db.OpenRecordset(strSQL)

'Handle the outcome of the search
If rs.EOF Then
'Value checked does not exist...Take required action
Else
'Perform some other task as the value exists
End If

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Sub

If an exact match is not what you need you could consider using the LIKE
operator in your SQL statement:
e.g.
strSQL = "SELECT * FROM tblYourTable WHERE fldFieldToCheck LIKE " & """" &
Me.txtYourTextBox & """"

If you want more stringent checks, modify the SQL based on your needs.

This code would go in the Click event of a button on your search form and
would require that the value to be checked is entered in the control first
(before the user clicks the button). Either that or you'll have to add some
error handling.


Jamie
 
Back
Top