Recordset with Find Method

[Robert]

I have a table (Table1) with two fields:
RequisitionNumber (primary key, text)
Activated (YesNo option)

I have a form (Form1)
txtNewRecord (unbound text box)

I have created a recordset on the above table. I need to
find the next available RequisitionNumber where the
Activated value = 0. I have written the code below, tried
to debug it and can't figure out where I went wrong. Any
help in proceeding is appreciated and I thank you in
advance.

Option Compare Database
Option Explicit


Public Sub StartReq()
Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim num As Integer
Dim varFound As Variant, strCriteria As String,
strActivated As String
Dim strNewReq As String
'Establish connectin and recordset for the current
project
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
Set cnn = CurrentProject.Connection
'Opens the table Table1 and a recordset based on that
table
rst.Open "Table1", cnn, adOpenStatic,
adLockOptimistic, adCmdTable
'Determine the number of records in the recordset
rst.Requery
num = rst.RecordCount
'Test recordset for presence of records.
If rst.RecordCount = 0 Then
MsgBox "There are no requisitions"
rst.Close
Exit Sub
End If
rst.MoveFirst
'Test recordset for available criteria based on
strCriteria
strActivated = "0"
strCriteria = "Activated = '" & strActivated & "'"
rst.Find strCriteria
If Not rst.EOF Then
varFound = rst.Bookmark
rst.Find strCriteria, 1
If rst.EOF Then
rst.Bookmark = varFound
MsgBox "There are no requisitions available"
End If
rst.Close
End If
Forms!form1!txtNewRecord = varFound
End Sub

 

[SA]

Robert:

I didn't have a look at your code because there is a MUCH faster way to go
about doing this. Rather than look through a recordset with a find, create
a query or view which basically does something like:

SELECT Min(MyTable.RequisitionID) AS NextID FROM MyTable WHERE
(((MyTable.Activated)=0))

Then simply open that recordset in code and look for the value of the field
Requisition ID, no searching or looping.

Secondly, you should probably change your "Activated" field to a yes/no
field rather than a text field as you appear to have it in your table now.
That will a.) consume much less table space and b.) be much faster for
searches. (And if it is a yes no field currently then that's one problem
with your find statement in that you are using a string value.

 

[Tim Ferguson]

I need to
find the next available RequisitionNumber where the
Activated value = 0.

You can do this in two lines:


' the NZ traps the Null and returns a "0"
strMaxReq = Nz(DMax("RequisitionNumber", "MyTable", _
"Activated = False"), "0")

' convert the string to a number and add one and
' then convert it back to a string again (groan)
' Why is RequisitionNumber a text field anyway...
strNextReq = CStr(CLng(strMaxReq) + 1)





Hope that helps


Tim F