We help IT Professionals succeed at work.

find next record

Smave
Smave asked
on
Function ScanRange()
dim rsSearch as Object
'clone the recordset of an open form
Set rsSearch = [Form_Customer Browser].RecordsetClone

find first record  <<< how do I do this? >>>
Do Until rsSearch.EOF
    goto next record  <<< how do I do this? >>>
    Debug.Print "Contents of field01" & rsSearch.field01
    <<< what is the syntax for address in a field in a recordset object  >>>
Loop
End Function

Above is a function I am trying to write.

I clone the RecordSet of an open form and then Loop through the records.

The correct use of the FindFirst and FindNext methods eluded me.

Also, how do I address the fields inside the RecordSet?

Thank You.
Dave
Comment
Watch Question

Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer
CERTIFIED EXPERT

Commented:
Hi Smave,

rsSearch.MoveNext
Debug.Print rsSearch.Fields![my field name]
Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer
CERTIFIED EXPERT

Commented:
and we can define the rsSearch as a Recordset Object:

Dim rsSearch As New Recordset
   
    Set rsSearch = [Form_Customer Browser].RecordsetClone
 
    rsSearch.Find "[my Field name] = '" & myValue & "'", , adSearchForward, 1

and also you can try to explore the Filter Property in RecordSet Object:

rs.Filter = "[my Field Name] = '" & myValue & "'"

If rs.RecordCount > 0 Then
'Records Matched
End If

rs.Filter = "" 'Set Filter to Nothing

Hope this help
Brendt HessSenior DBA
CERTIFIED EXPERT

Commented:
If you mean "Move to the first record in the recordset" (without looking for a specific value in the record) then use the .MoveFirst method, e.g.:

rsSearch.MoveFirst
Do Until rsSearch.EOF
   Debug.Print "Contents of field01" & rsSearch!field01
   rsSearch.MoveNext
Loop
Brendt HessSenior DBA
CERTIFIED EXPERT

Commented:
Also, instead of FindFirst / FindNext, if the data you are looking for has an index, use the .Index property and the .Seek method:

(Assuming a lookup on a field called CustomerID which is the primary key, and an index called PrimaryKey):

rsSearch.Index = "PrimaryKey"
rsSearch.Seek "=", CustID

Author

Commented:
I tweaked the function as follows.

Dim rsSearch As New Recordset
Set rsSearch = [Form_Customer Browser].RecordsetClone

The Set statement causes a Run-Time Error '13':
Type Mismatch

Why?

Thanx
Dave
Frédéric MétrauxFreelance Project Development Manager

Commented:
I am always confused:
.MoveFirst and .MoveNext are for ADO recordsets
.FindFirst and .FindNext are for DAO recordsets

My guess is that the form recordsetclone is a ADO recordset. Can it be a DAO recordset as well?

Just listening...
Frédéric MétrauxFreelance Project Development Manager

Commented:
Yeah! Smave, that's what I thought: The form recordset clone is a DAO recordset, not an ADO. So your declaration must be:


Dim rsSearch As Recordset

Frédéric MétrauxFreelance Project Development Manager

Commented:
And, as for the DAO recordset, use .FindFirst and .FindNext

also, I hava a small doubt about the Set = line. Try:

Dim rsSearch As Recordset
Set rsSearch = Forms![Form_Customer Browser].RecordsetClone

Brendt HessSenior DBA
CERTIFIED EXPERT

Commented:
Change your Dim statement to

Dim rsSearch as DAO.Recordset

or

Dim rsSearch As ADODB.Recordset

depending on if you are using DAO or ADO.  Since both are available in Access, if you reference both, it's sort of a toss-up as to which one will be used if you just say "as Recordset"
Frédéric MétrauxFreelance Project Development Manager

Commented:
Or:
Forms("Form_Customer Browser").RecordsetClone
is cool too.
Frédéric MétrauxFreelance Project Development Manager

Commented:
No,
Currentdb().Forms("Formname"),RecordsetClone

Sorry, need to go to sleep, its late...
Frédéric MétrauxFreelance Project Development Manager

Commented:
NoNoNoNo:

Forms("Form_Customer Browser").RecordsetClone

is the correct syntax. Really sorry, I really need some sleep...  ?./o

Commented:
Smave,

I assume that you are using Access 2000 with ADO...

If you are then the form name you are using is invalid "Form_Customer Browser", I think that you should just use "Customer Browser" in your code. And Ornicar is correct, calling Recordsetclone does return a DAO recordset not a ADO recordset.

With this in mind your function would look like this

Function ScanRange()
dim rsSearch as Object
'clone the recordset of an open form
Set rsSearch = Forms![Customer Browser].Form.RecordsetClone

'find first record  <<< how do I do this? >>>
'do you mean goto the first record?
'rsSearch.movefirst
'or find the first record that you are searching for?
'rsSearch.Findfirst "[FieldName]=value"

'assuming that you mean searching the recordset
rsSearch.Findfirst "[field01]=1"
Do while not rsSearch.NoMatch'loop until search fails
'<<< what is the syntax for address in a field in a recordset object  >>>
    Debug.Print "Contents of field01" & rsSearch!field01
'OR    Debug.Print "Contents of field01" & rsSearch("field01")
'OR    Debug.Print "Contents of field01" & rsSearch.Fields(0)
    rsSearch.FindNext "[field01]=1"'find the next matching record
Wend
End Function
Frédéric MétrauxFreelance Project Development Manager

Commented:
Thanks Pete,
I wonder if: rsSearch("field01") works. Never used this one, always: rsSearch.Fields("field01")

Commented:
yes it does
rsSearch("field01") and rsSearch.Fields("field01") are the same as are rsSearch(0) and rsSearch.Fields(0) because the fields object of a recordset is the default object

Author

Commented:
OK.  I think I got it figured out.

This function WORKS!!

Function ScanRange()
Dim strCriteria As String, dbScan As Database, rsScan As Recordset
strCriteria = "[CustNumber] >= 1" 'Define search criteria."
Set dbScan = DBEngine.Workspaces(0).Databases(0)
Set rsScan = dbScan.OpenRecordset("Whole Customer Table", DB_OPEN_DYNASET)   ' Create Recordset.
rsScan.FindFirst strCriteria
Do Until rsScan.NoMatch
    Debug.Print rsScan.Fields![CustName], rsScan.Fields![Address01]
    rsScan.FindNext strCriteria
Loop
End Function

Question: Is this over kill?  It seems like a lot of overhead to do something simple.

I got a clue to my solution here:
http://support.microsoft.com/default.aspx?scid=%2fsearch%2fviewDoc.aspx%3fdocID%3dKC.Q199064%26dialogID%3d6420516%26iterationID%3d1%26sessionID%3danonymous%7c5680493

It looks like I played around with my references too much and got them all boggered.

Question: Does anyone know how to set the references back th their original default with out uninstalling and reinststalling Office?

Thanx again.
Dave
Freelance Project Development Manager
Commented:
Don't bother about the references, they depends from the database. So you boggered only those of this database. Compare them with a new database and you will be able to correct them.

Author

Commented:
ornicar

Thanx for the insight.  I will defiantly have to remember that!

Dave

Explore More ContentExplore courses, solutions, and other research materials related to this topic.