Recognizing EOF in Recordset

[ppkgoldengun]

I am trying to complete a Do Loop sequence that includes a recordset findnext command. Unfortunately, the Findnext always stays at the last found record (it successfully finds the previous records) instead of return a EOF is true which would end the loop. Any hints would be greatly appreciated. My code:
Private Sub MergeButton_Click()
Dim Letterloc As String
Dim strInsured As String
strInsured = Me.txtInsuredName
Dim srInsuredName As String
Dim strInsuredAddress As String
Dim strInsuredCity As String
Dim strInsuredCode As String
Dim strInsuredZip As String
Dim strInsuredContact As String
Dim strPurchaseOrder As String
Dim strCertDesc As String
Dim strInsType As String
Dim strExpDate As String


Dim db As DAO.Database
Dim rstInsured As DAO.Recordset
Set db = CurrentDb
Set rstInsured = db.OpenRecordset("qryCertDetailRedAll", dbOpenSnapshot)

rstInsured.FindFirst "[InsuredName]='" & strInsured & "'"
'Set rstInsured = db.OpenRecordset("tblEmployees", dbOpenSnapshot)
'Set rstInsured = db.OpenRecordset("qryContract", dbOpenSnapshot)
Debug.Print strInsured
Debug.Print rstInsured![InsuredName]
Debug.Print rstInsured![ExpDate]
'Debug.Print rstInsured![Certificate Description]
'Debug.Print rstInsured![InsType]


Letterloc = """" & WordLoc & """"
Set objWord = CreateObject("Word.Application")
With objWord
' Make the application visible.
.Visible = True
' Open the document.
'.Documents.Open ("c:\Access\SJSU\CertNear.dot")
.Documents.Open (Letterloc)
' Move to each bookmark and insert text from the form.
'Make sure that list follows bookmark order in Document.
'Set all the values to Zeros or empty strings if a null value is found so Word doesnt choke

strInsuredName = Nz(rstInsured![InsuredName])
strInsuredAddress = Nz(rstInsured![InsuredAddress])
strInsuredCity = Nz(rstInsured![InsuredCity])
strInsuredCode = Nz(rstInsured![InsuredCode])
strInsuredZip = Nz(rstInsured![InsuredZip])
strInsuredContact = Nz(rstInsured![InsuredContact])


.ActiveDocument.Bookmarks("InsuredName").Select
.Selection.Text = (CStr(strInsuredName))
.ActiveDocument.Bookmarks("InsuredAddress").Select
.Selection.Text = (CStr(strInsuredAddress))
.ActiveDocument.Bookmarks("InsuredCity").Select
.Selection.Text = (CStr(strInsuredCity))
.ActiveDocument.Bookmarks("InsuredCode").Select
.Selection.Text = (CStr(strInsuredCode))
.ActiveDocument.Bookmarks("InsuredZip").Select
.Selection.Text = (CStr(strInsuredZip))
.ActiveDocument.Bookmarks("InsuredContact").Select
.Selection.Text = (CStr(strInsuredContact))

' Go To First Table In Document

objWord.Selection.WholeStory
objWord.Selection.HomeKey Unit:=6
With objWord.Selection

.GoTo What:=wdGoToTable, Which:=wdGoToFirst, Count:=1, Name:=""
.MoveDown Unit:=wdLine, Count:=2
End With

Do While Not rstInsured.EOF

strPurchaseOrder = Nz(rstInsured![PurchaseOrder])
strCertDesc = Nz(rstInsured![Certificate Description])
strInsType = Nz(rstInsured![InsType])
strExpDate = Nz(rstInsured![ExpDate])


With objWord.Selection

.TypeText Text:=(CStr(strPurchaseOrder))
.MoveRight Unit:=wdCell
.TypeText Text:=(CStr(strCertDesc))
.MoveRight Unit:=wdCell
.TypeText Text:=(CStr(strInsType))
.MoveRight Unit:=wdCell
.TypeText Text:=(CStr(strExpDate))
.MoveRight Unit:=wdCell
End With

rstInsured.FindNext "[InsuredName]='" & strInsured & "'"

Loop

rstInsured.Close
Selection.SelectRow
Selection.Rows.Delete


End With
' Print the document in the foreground so Microsoft Word 97
' will not close until the document finishes printing.
objWord.ActiveDocument.PrintOut Background:=False
' Close the document without saving changes.
objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
' Quit Microsoft Word 97 and release the object variable.
objWord.Quit
Set objWord = Nothing
Exit Sub
MergeButton_Err:
' If a field on the form is empty
' remove the bookmark text and continue.
If Err.Number = 94 Then
objWord.Selection.Text = ""
Resume Next
' If the Photo field is empty.
ElseIf Err.Number = 2046 Then
MsgBox "Please add a photo to this record and try again."
Else
MsgBox Err.Number & vbCr & Err.Description
End If
Exit Sub
' End Sub


End Sub

 

[RoyVidar]

I don't understand.

The .FindNext starts the search with the current record, and finds the next occurance matching the search criterion in the direction of the sort order of the recordset (i e start to end), making this (the found record, if found) the current record. When no match is found, either .Eof or .Nomatch is True. As I understand, this is both the description of your routine and how the .FindNext should behave.

What is the problem? Should you use .MoveNext in stead? Does the query include as many records as you think it should?

Roy-Vidar

 

[ppkgoldengun]

The code is doing exactly what you said. It just doesn't find EOF true, ever contrary to the Access Documentation in Help.

It finds the first then finds the next matching then the next and so on. When it gets to the last record that it finds it continues to return that by going through the loop again. I have a query that I run that matches to the required return values so I know that it finds all and gets hung up returning the last record instead of trying to find the next, running into the EOF and exiting the loop.

A movenext would require moving through each record, doing a compare, and would be much slower. If Access supported parameterized recordsets that would be no problem as the recordset would only contain the records matching the criteria but it doesn't. So.... why does it sit at the last record in the loop and doesnt execute the end of file?

 

[RoyVidar]

Wasn't very precise there, a little slip, try useing the .NoMatch property in stead of the .Eof property (Eof continues to be false after the last occurrence is found).

[tt]do while not rstInsured.NoMatch[/tt]

- or do the .NoMatch test after the .FindNext

BTW - Access does support parameterized recordsets

Roy-Vidar

 

[mhcruella]

About those parameterized recordsets, does the recordset have to be re-opened if the value of the parameter changes? How does that work?

Mich

 

[ppkgoldengun]

It is my understanding that you can create parameterized recordsets for use say when called from a form reference in the criteria of the query. However, such record sets are not available for use through VBA code manipulation afterwards. At least I could not get it to work and I've seen other posts that it is not possible.

 

[RoyVidar]

Well, my comment was based on "If Access supported parameterized recordsets that would be no problem as the recordset would only contain the records matching the criteria but it doesn't."

There are several methods of returning recordsets from queries having parameters. Not all of them return updateable recordsets, if that's what's referred to as "not available for use trough VBA code manipulation", but then those queries are often performed to get some information. When I need to update/append... I try very hard to avoid recordset operations, but rather execute gueries.

Just going thru a couple of ways of using some kind of criteria from forms in ADO recordsets (don't know much about DAO, but I suppose the same is available there too). I'll use currentproject.connection, but a valid connection object connecting to Access should be OK.

Easiest, perhaps, is to concatinate the sql string:

[tt]strSQL="select * from sometable where " & _
"id = " & Me("txtID").value & _
" and fName = '" & Me("txtfName").Value & "'"
set rs=currentproject.connection.execute(strSQL)[/tt]

- the .execute method of the connection object will return a readonly forwardonly, recordset. For an updateable recordset, set apropriate cursor and locktype on the recordset, and use the open method of the recordset.

Now, the above query, as a stored query can also be opened as a recordset, the stored query would then perhaps look something like this (named MyQuery):

[tt]select * from sometable where id = forms!formname!txtID and fName = forms!formname!txtfName[/tt]

I was for a while convinced one would need to use ADOX to open it, as shown here thread709-819033, but you can also access it like this:

[tt]dim cmd as adodb.command
dim prm as adodb.parameter
dim rs as adodb.recordset
set cmd=new adodb.command
with cmd
.activeconnection=currentproject.connection
.properties("Jet OLEDB:Stored Query") = true
.commandtext="MyQuery"
for each prm in .parameters
eval(prm.name)
next prm
set rs = .execute
end with[/tt]

Next sample, alter the stored query to something like this (changing the name to "MyQuery2")

[tt]select * from sometable where id = [MyId] and fName = [MyfName][/tt]

With such stored query, the approach could be something like this:

[tt]dim cmd as adodb.command
dim prm as adodb.parameter
dim rs as adodb.recordset
set cmd=new adodb.command
with cmd
.activeconnection=currentproject.connection
.properties("Jet OLEDB:Stored Query") = true
.commandtext="MyQuery2"
.parameters("[MyId]") = Me("txtID").value
.parameters("[MyfName]") = Me("txtfName").Value
set rs = .execute
end with[/tt]

For more info, chip's faq faq709-1526 (in the Visual Basic(Microsoft) Databases forum) is worth a read.

On the question of how a recordset behaves when parameters changes, I don't know, but have assumed you'd need to reopen it with the new parameters. When I need to access different "sets of records", I either reopen the recordset, or use the .Filter method (again ADO).

Don't know if this answers the questions, though...

Roy-Vidar

 

[ppkgoldengun]

Really appreciate the clarification and information. Thanks.