Record Find, First & Last

G

Guest

Help please
I am looking to find all the occurences in a table where one of the Fields
holds a "Serial Number" and another field holds the "Contract Number". The
Serial Numbers are unique and the Contract Numbers are the same. So, I want
to search through my Table, searching every record for a field that matches
my Contract Number Criterion and pick out the corresponding unique serial
number.
Easy, I thought. Just FindFirst Criteria, followed by a loop to FindNext
Criterion. the Loop to be terminated by an EOF.
When I did this the loop continued to run well after the last racord in the
Table had been found. (I thought thay the loop would exit after it had found
the last matching Critera)
So I came up with a solution whereby I did a Find First, store that record
in the variable "First", followed by a FindLast and store that record in the
variable "Last". I then would compare each record in the Find Next Loop with
the Variable "Last" and use that to exit wen FindNext = "Last". However, My
initial attempts gives me the results where "First" contains the first Serial
Number that matches the Contract Number, Great, Unfortunately the vatriable
Last = the same Serial Number. The loop works great and finds all of the
other serial numbers that have the same Contract Number but I have no method
of jumping out of the loop when I get to the last Matching Record.

I must be missing something,, can anyone help? I enclose my "Play" code
below but it contains bits of other attempts to get the routine working. Any
better suggestions as to how I should be approaching this would be most
welcome.

Please don'y foget that the line "if first = last" sould be in the loop to
provide an exit from the loop.
rs1.MoveFirst
rs1.FindFirst strCriteria
first = rs1("SerialNo")
rs1.MoveLast
rs1.FindLast strCriteria
last = rs1("SerialNo")
If first = last Then Debug.Print , "Last"
Debug.Print rs1("ContractNo")
[ContractNo] = rs1("ContractNo")
strCriteria = BuildCriteria("ContractNo", dbInteger, [ContractNo])
rs1.FindFirst strCriteria
Debug.Print rs1("SerialNo")
' If Not (rs1.NoMatch) Then
Do Until rs1.EOF
rs1.FindNext strCriteria
Debug.Print rs1("SerialNo")
' Else:
' Debug.Print "no Records"
' End If
Loop
 
D

Douglas J. Steele

If I'm following correctly, the problem you're running into is because in
essence you're using:

Do Until rs1.EOF
rs1.FindNext strCriteria
Debug.Print rs1("SerialNo")
Loop

You're in a particular place in the recordset, so rs1.EOF isn't true and you
enter the loop. You do a FindNext, and that does take you to EOF. However,
you try to refer to rs1("SerialNo"), which causes an error, since you're at
EOF.

Try:

rs1.FindFirst strCriteria
Do Until rs1.EOF
Debug.Print rs1("SerialNo")
rs1.FindNext strCriteria
Loop

or, if you prefer,

rs1.FindFirst strCriteria
If rs1.EOF Then
Debug.Print "No Records"
Else
Do Until rs1.EOF
Debug.Print rs1("SerialNo")
rs1.FindNext strCriteria
Loop
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ray C said:
Help please
I am looking to find all the occurences in a table where one of the Fields
holds a "Serial Number" and another field holds the "Contract Number". The
Serial Numbers are unique and the Contract Numbers are the same. So, I want
to search through my Table, searching every record for a field that matches
my Contract Number Criterion and pick out the corresponding unique serial
number.
Easy, I thought. Just FindFirst Criteria, followed by a loop to FindNext
Criterion. the Loop to be terminated by an EOF.
When I did this the loop continued to run well after the last racord in the
Table had been found. (I thought thay the loop would exit after it had found
the last matching Critera)
So I came up with a solution whereby I did a Find First, store that record
in the variable "First", followed by a FindLast and store that record in the
variable "Last". I then would compare each record in the Find Next Loop with
the Variable "Last" and use that to exit wen FindNext = "Last". However, My
initial attempts gives me the results where "First" contains the first Serial
Number that matches the Contract Number, Great, Unfortunately the vatriable
Last = the same Serial Number. The loop works great and finds all of the
other serial numbers that have the same Contract Number but I have no method
of jumping out of the loop when I get to the last Matching Record.

I must be missing something,, can anyone help? I enclose my "Play" code
below but it contains bits of other attempts to get the routine working. Any
better suggestions as to how I should be approaching this would be most
welcome.

Please don'y foget that the line "if first = last" sould be in the loop to
provide an exit from the loop.
rs1.MoveFirst
rs1.FindFirst strCriteria
first = rs1("SerialNo")
rs1.MoveLast
rs1.FindLast strCriteria
last = rs1("SerialNo")
If first = last Then Debug.Print , "Last"
Debug.Print rs1("ContractNo")
[ContractNo] = rs1("ContractNo")
strCriteria = BuildCriteria("ContractNo", dbInteger, [ContractNo])
rs1.FindFirst strCriteria
Debug.Print rs1("SerialNo")
' If Not (rs1.NoMatch) Then
Do Until rs1.EOF
rs1.FindNext strCriteria
Debug.Print rs1("SerialNo")
' Else:
' Debug.Print "no Records"
' End If
Loop
Click to expand...
 
G

Guest

Why not just use a query restricted on the ContractNo column, e.g.

Sub GetSerialNumbers(lngContractNo As Long)

On Error GoTo Err_Handler

Dim rst As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM Contracts" & _
" WHERE ContractNo = " & lngContractNo
" ORDER BY SerialNo"

Set rst = New ADODB.Recordset

With rst
.ActiveConnection = CurrentProject.Connection
.Open _
Source:=strSQL, _
CursorType:=adOpenKeyset, _
Options:=adCmdText
If Not .EOF Then
Do While Not .EOF
Debug.Print .Fields("ContractNo") & "; " & .Fields("SerialNo")
.MoveNext
Loop
Else
MsgBox "No matching records.", vbInformation, "Sorry"
End If
End With

Exit_here:
rst.Close
Set rst = Nothing
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_here

End Sub

Ken Sheridan
Stafford, England

Ray C said:
Help please
I am looking to find all the occurences in a table where one of the Fields
holds a "Serial Number" and another field holds the "Contract Number". The
Serial Numbers are unique and the Contract Numbers are the same. So, I want
to search through my Table, searching every record for a field that matches
my Contract Number Criterion and pick out the corresponding unique serial
number.
Easy, I thought. Just FindFirst Criteria, followed by a loop to FindNext
Criterion. the Loop to be terminated by an EOF.
When I did this the loop continued to run well after the last racord in the
Table had been found. (I thought thay the loop would exit after it had found
the last matching Critera)
So I came up with a solution whereby I did a Find First, store that record
in the variable "First", followed by a FindLast and store that record in the
variable "Last". I then would compare each record in the Find Next Loop with
the Variable "Last" and use that to exit wen FindNext = "Last". However, My
initial attempts gives me the results where "First" contains the first Serial
Number that matches the Contract Number, Great, Unfortunately the vatriable
Last = the same Serial Number. The loop works great and finds all of the
other serial numbers that have the same Contract Number but I have no method
of jumping out of the loop when I get to the last Matching Record.

I must be missing something,, can anyone help? I enclose my "Play" code
below but it contains bits of other attempts to get the routine working. Any
better suggestions as to how I should be approaching this would be most
welcome.

Please don'y foget that the line "if first = last" sould be in the loop to
provide an exit from the loop.
rs1.MoveFirst
rs1.FindFirst strCriteria
first = rs1("SerialNo")
rs1.MoveLast
rs1.FindLast strCriteria
last = rs1("SerialNo")
If first = last Then Debug.Print , "Last"
Debug.Print rs1("ContractNo")
[ContractNo] = rs1("ContractNo")
strCriteria = BuildCriteria("ContractNo", dbInteger, [ContractNo])
rs1.FindFirst strCriteria
Debug.Print rs1("SerialNo")
' If Not (rs1.NoMatch) Then
Do Until rs1.EOF
rs1.FindNext strCriteria
Debug.Print rs1("SerialNo")
' Else:
' Debug.Print "no Records"
' End If
Loop
Click to expand...
 
G

Guest

Thanks Douglas, that is a great help.

Ray c


Douglas J. Steele said:
If I'm following correctly, the problem you're running into is because in
essence you're using:

Do Until rs1.EOF
rs1.FindNext strCriteria
Debug.Print rs1("SerialNo")
Loop

You're in a particular place in the recordset, so rs1.EOF isn't true and you
enter the loop. You do a FindNext, and that does take you to EOF. However,
you try to refer to rs1("SerialNo"), which causes an error, since you're at
EOF.

Try:

rs1.FindFirst strCriteria
Do Until rs1.EOF
Debug.Print rs1("SerialNo")
rs1.FindNext strCriteria
Loop

or, if you prefer,

rs1.FindFirst strCriteria
If rs1.EOF Then
Debug.Print "No Records"
Else
Do Until rs1.EOF
Debug.Print rs1("SerialNo")
rs1.FindNext strCriteria
Loop
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ray C said:
Help please
I am looking to find all the occurences in a table where one of the Fields
holds a "Serial Number" and another field holds the "Contract Number". The
Serial Numbers are unique and the Contract Numbers are the same. So, I want
to search through my Table, searching every record for a field that matches
my Contract Number Criterion and pick out the corresponding unique serial
number.
Easy, I thought. Just FindFirst Criteria, followed by a loop to FindNext
Criterion. the Loop to be terminated by an EOF.
When I did this the loop continued to run well after the last racord in the
Table had been found. (I thought thay the loop would exit after it had found
the last matching Critera)
So I came up with a solution whereby I did a Find First, store that record
in the variable "First", followed by a FindLast and store that record in the
variable "Last". I then would compare each record in the Find Next Loop with
the Variable "Last" and use that to exit wen FindNext = "Last". However, My
initial attempts gives me the results where "First" contains the first Serial
Number that matches the Contract Number, Great, Unfortunately the vatriable
Last = the same Serial Number. The loop works great and finds all of the
other serial numbers that have the same Contract Number but I have no method
of jumping out of the loop when I get to the last Matching Record.

I must be missing something,, can anyone help? I enclose my "Play" code
below but it contains bits of other attempts to get the routine working. Any
better suggestions as to how I should be approaching this would be most
welcome.

Please don'y foget that the line "if first = last" sould be in the loop to
provide an exit from the loop.
rs1.MoveFirst
rs1.FindFirst strCriteria
first = rs1("SerialNo")
rs1.MoveLast
rs1.FindLast strCriteria
last = rs1("SerialNo")
If first = last Then Debug.Print , "Last"
Debug.Print rs1("ContractNo")
[ContractNo] = rs1("ContractNo")
strCriteria = BuildCriteria("ContractNo", dbInteger, [ContractNo])
rs1.FindFirst strCriteria
Debug.Print rs1("SerialNo")
' If Not (rs1.NoMatch) Then
Do Until rs1.EOF
rs1.FindNext strCriteria
Debug.Print rs1("SerialNo")
' Else:
' Debug.Print "no Records"
' End If
Loop
Click to expand...
Click to expand...
 
G

Guest

Hi Ken
I managed to gry the issue resolved (with the help of another MVP) using EOF
but I am intrigued by your solution and will try it out.
Thank you for you for your help it is much appreciated.

Ray C

Ken Sheridan said:
Why not just use a query restricted on the ContractNo column, e.g.

Sub GetSerialNumbers(lngContractNo As Long)

On Error GoTo Err_Handler

Dim rst As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM Contracts" & _
" WHERE ContractNo = " & lngContractNo
" ORDER BY SerialNo"

Set rst = New ADODB.Recordset

With rst
.ActiveConnection = CurrentProject.Connection
.Open _
Source:=strSQL, _
CursorType:=adOpenKeyset, _
Options:=adCmdText
If Not .EOF Then
Do While Not .EOF
Debug.Print .Fields("ContractNo") & "; " & .Fields("SerialNo")
.MoveNext
Loop
Else
MsgBox "No matching records.", vbInformation, "Sorry"
End If
End With

Exit_here:
rst.Close
Set rst = Nothing
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_here

End Sub

Ken Sheridan
Stafford, England

Ray C said:
Help please
I am looking to find all the occurences in a table where one of the Fields
holds a "Serial Number" and another field holds the "Contract Number". The
Serial Numbers are unique and the Contract Numbers are the same. So, I want
to search through my Table, searching every record for a field that matches
my Contract Number Criterion and pick out the corresponding unique serial
number.
Easy, I thought. Just FindFirst Criteria, followed by a loop to FindNext
Criterion. the Loop to be terminated by an EOF.
When I did this the loop continued to run well after the last racord in the
Table had been found. (I thought thay the loop would exit after it had found
the last matching Critera)
So I came up with a solution whereby I did a Find First, store that record
in the variable "First", followed by a FindLast and store that record in the
variable "Last". I then would compare each record in the Find Next Loop with
the Variable "Last" and use that to exit wen FindNext = "Last". However, My
initial attempts gives me the results where "First" contains the first Serial
Number that matches the Contract Number, Great, Unfortunately the vatriable
Last = the same Serial Number. The loop works great and finds all of the
other serial numbers that have the same Contract Number but I have no method
of jumping out of the loop when I get to the last Matching Record.

I must be missing something,, can anyone help? I enclose my "Play" code
below but it contains bits of other attempts to get the routine working. Any
better suggestions as to how I should be approaching this would be most
welcome.

Please don'y foget that the line "if first = last" sould be in the loop to
provide an exit from the loop.
rs1.MoveFirst
rs1.FindFirst strCriteria
first = rs1("SerialNo")
rs1.MoveLast
rs1.FindLast strCriteria
last = rs1("SerialNo")
If first = last Then Debug.Print , "Last"
Debug.Print rs1("ContractNo")
[ContractNo] = rs1("ContractNo")
strCriteria = BuildCriteria("ContractNo", dbInteger, [ContractNo])
rs1.FindFirst strCriteria
Debug.Print rs1("SerialNo")
' If Not (rs1.NoMatch) Then
Do Until rs1.EOF
rs1.FindNext strCriteria
Debug.Print rs1("SerialNo")
' Else:
' Debug.Print "no Records"
' End If
Loop
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Selecting record from unbound box 2
Access Access Database vba problem 0
How to do this? 2
Use FindFirst to find record in Access 3
Timer event does not fire 1
Run-time error 430: Class does not support Automation..... 4
-1 returned as last record of recordset object 1
My use of "FindFirst" is not working 13

Top