Louis:
You could try calling the MoveLast method of the recordset object first to
ensure its filled. However, relying on the RecordCount assumes that the
numbers in the rows are an unbroken series, which seems rather risky as it
does not allow for deletions, unless the series is recomputed after the
deletion of a row, and if the number is an autonumber, it also assumes that
the values have been generated sequentially, which cannot be guaranteed as an
autonumber is designed to ensure unique values, not necessarily sequential
ones.
If the form's underlying recordset is based on a table or saved query then
you could call the DLookup function to check for the existence of the value,
e.g.
If IsNull(DLookup("SomeField", "SomeTableOrQuery", "SomeField = " &
[txtSearch])) Then
MsgBox "Number not found.", vbExclamation, "Invalid Search Criterion!"
Me.txtSearch.SetFocus
End If
If the form's underlying recordset is based on a static SQL statement you
could establish a recordset based on the SQL statement but further restricted
to return only the row with the value entered in the relevant column, and
then test for an empty recordset, e.g.
Dim rst As ADODB.Recordset
Dim strSQL As String
strSQL = _
"SELECT * " & _
"FROM Products INNER JOIN Categories " & _
"ON Products.CategoryID = Categories.CategoryID " & _
"WHERE Category = ""Widget"" " & _
"AND ProductNumber = " & Me.txtSearch
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Open _
Source:=strSQL, _
CursorType:=adOpenForwardOnly
If .BOF And . EOF Then
MsgBox "Number not found.", vbExclamation, "Invalid Search
Criterion!"
Me.txtSearch.SetFocus
End If
End With
If the form's underlying recordset is not static, e.g. it might have been
filtered by the user and you want to search only within the filtered
recordset then you could call the FindFirst method of the form's Recordset
object:
Dim rst As Object
Set rst = Me.Recordset
rst.FindFirst "SomeField = " & Me.txtSearch
If rst.NoMatch Then
MsgBox "Number not found.", vbExclamation, "Invalid Search Criterion!"
Me.txtSearch.SetFocus
End If
Ken Sheridan
Stafford, England
ll said:
Hi,
I am trying to check the input of my Access form, and I am wanting
visitors to input only numbers within the recordset. Below is the
code. The problem here is that no matter what number I input into the
text box (txtSearch), I get the MsgBox error identified below in the
code. Does the value in txtSearch perhaps need to be set equal to a
variable first, etc? Thanks, Louis
-----
If (Me![txtSearch]) > rst.RecordCount Then
MsgBox "Please enter a value greater than 0 (between 1 and " &
rst.RecordCount & ").", vbOKOnly, "Invalid Search Criterion!"
Me![txtSearch].SetFocus
Exit Sub
End If
