Hi J
Something like the following should work
John
Public Function CheckForValue(strValue As String, strTbl As String, _
strFld As String) As Boolean
'checks for strings only
'Arguments:
' strValue value to be checked
' strTbl name of table
' strFld name of field
'NO ERROR CHECKING IMPLEMENTED
Dim db As DAO.Database 'current db
Dim rs As DAO.Recordset 'target recordset
Set db = CurrentDb()
'target recordset is all occurences of strValue in field strFle in table
strTbl
Set rs = db.OpenRecordset("Select t.[" & strFld & "] from " & strTbl & "
t where t.[" & _
strFld & "] = '" & strValue & "';",
dbOpenDynaset)
'check to see if any records returned. If none then the strValue hasn't
been used yet.
With rs
CheckForValue = Not (.EOF And .BOF)
End With
'clean up
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Function
Private Sub Text2_BeforeUpdate(Cancel As Integer)
'calls CheckForValue function to see if value in Text2 has been used in
Field X in table N
If CheckForValue(Me!Text2, "n", "x") Then
'yep tell the user and cancel
MsgBox Me!Text2 & " has already been used."
Cancel = True
End If
End Sub
JMorrell said:
I have a form in which a user will enter a SSN, among other things. I
don't want the user to go through the entire processss of entering this data
only to find out that the SSN is already in use. I thought an event
procedure before update on that control would be a place to use a SELECT
statement to see if that field is used. If it is, stop the user with a
msgBox telling them that. Having problems with the SELECT statement right
now. Any help is greatly appreciated.
