Do Loop Using .Bookmark as Test

[xhat]

I'm calling a sub that takes 3 parms - a DAO Recordset object, and two variants that have been set equal to .Bookmark property in a previous sub. The two variants are called StartPoint and StopPoint respectively. I clone the recordset object (rsc), and set rsc.Bookmark = StartPoint. This moves my pointer to the correct starting point in the recordset. I then want to loop through the records until I hit my StopPoint. To do this, I wrote this bit of code...

Do Until vntStopPoint = rsc.Bookmark
     some lines of code that manipulate a string
     rsc.MoveNext
Loop

Compiler does not like the "Do..." line of code. Claims I have a type mismatch and highlights the "=" sign in the Do.. line. I've tried double and triple "=" signs for comparing, but always the same result: Compile Error - Type Mismatch. So, my question is two parts: What is causing the type mismatch, and is it possible to use .Bookmark property as a control in a loop?

Thanks.

 

[MajP]

the dao recordset bookmark property returns a byte, adodb returns a variant. See what happens if you dimension your variable as byte.

 

[MajP]

you may want to look at the absolute position property, or simply use the PK of the record as your start and stop location

rs.findfirst "someField = " & someValue
while not rs.eof or rs!someField = somePK

rs.moveNext
loop

 

[xhat]

MajP - Thank you, that was the problem. A bit tricky since in a previous sub I have declared vars as variants and had no problems using .Bookmark and the vars. However, it is only in this particular sub that I'm actually comparing the var to .Bookmark, so that makes sense. Thanks again!

 

[TheAceMan1]

How are ya xhat . . .

How are you deriving the your initial bookmarks Start/Stop Piint? . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[xhat]

MajP - for what I'm doing with this db, it is easier to set bkmrks and move around in the recordset that way. (At least, that's what I'm telling myself right now.) The table upon which I'm basing my recordset is a single column table that was used to import a text file. Each row in the table is some variable length string, and I'd be gobbling up a lot of processor time doing LIKE comparisons and parsing out substrings, etc, etc. I'm taking the tack that I'll do string parsing just once, set my bookmarks to the appropriate rows, and then just jump around with them.

 

[HarleyQuinn]

The way I'd compare Bookmarks to their variable representations is to use strComp:

Do Until StrComp(rsc.Bookmark, vntStopPoint, vbBinaryCompare) = 0
'...
rs.MoveNext
Loop

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[MajP]

How about absolute position?
If you can find the BK you can find the abs position

Public Sub test()
Dim abs1 As Long
Dim abs2 As Long
'set abs 1 and abs 2 as the position in the recordset
rs.AbsolutePosition = abs1
Do Until rs.EOF Or rs.AbsolutePosition = abs2

rs.MoveNext
Loop
End Sub

 

[xhat]

MajP - I'm not familiar with .AbsolutePosition, and perhaps based on this excerpt from O'Reilly, I don't want to be!

AbsolutePosition

Data Type

Long

Description

This misleadingly named property sets or returns the relative record number of a recordset's current record. It is a zero-based number corresponding to the ordinal position of the current record in the recordset, ranging from zero to one less than the record count. If there is no current record (for example, for an empty recordset), AbsolutePosition returns -1.

TIP:

Despite the "Absolute" in the property name, this property is not stable and certainly is not a record number. It changes every time records are added to or deleted from a recordset. Use the Bookmark property to set a variable you can use to return to a particular record after moving the record pointer by a search or requerying.

However, I'm not poo pooing the idea, I'm just not really sure what it does nor how to use it, whereas with .Bookmark, I know exactly what I'm getting. (Well, almost exactly or I guess I wouldn't have made the original post!) In any event, after a little more careful reading of O'Reilly, I think using .AbPos and .Bookmark are essentially doing the same thing; both are setting stop and start points based on a row in the table. After dimensioning my Sub args as Bytes, the compiler complained no more, so I think I'm good comparing the .Bookmarks. Is there an issue you're aware of with .Bookmarks that I should be considering in the design?

I may be able to answer my own question. In the parent Sub, I dimension a few Variant vars to hold the .Bookmark vals. This is, in fact, required that the vars be Variant type in order to work with .Bookmark and variables to store BKs. But in my child Sub, the one to which I pass the Variant vars, I have to receive them as Bytes in order to compare the vars in the child Sub to the cloned recordset's BK property. But here's the rub: when I call the child Sub and try to pass the two relevant Variant vars from the parent Sub, I get a Type Mismatch error! So basically, I have to have variants in the parent Sub, bytes in the child Sub, but I can't pass a Variant var to a sub that is expecting a Byte var! What to do...?

 

[xhat]

MajP - By the way, here are the offending bits of code, and thanks for your responses!

'[COLOR=green]Call to the Sub. Pass it a recordset object, and two Variants that are storing BK vals[/color]
ParseSeatNumsOnly rs, varCurrRec, varHoleCardsBkmrk
......
Public Sub ParseSeatNumsOnly(ByVal RecSet As Recordset, ByVal bytStartPoint As Byte, ByVal bytStopPoint As Byte)

I've tried writing the Sub call both ways - with enclosing () and without.

 

[HarleyQuinn]

This is, in fact, required that the vars be Variant type in order to work with .Bookmark and variables to store BKs

It's not, you can assign them to strings (to work with the way I proposed, or to Bytes as MajP is describing)

To do this the way you seem to be going, replace the variants you're assigning in the parent sub with bytes (so they match the sub's declaration. Though this method does not work for me comparing bytes the way described).

I'm still going to push strings and strComp...

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[xhat]

Harley - thanks for weighing in. I'm working in Access 2000, and when I try to make the following assignment in my parent Sub...

[COLOR=green]
'Move record pointer to bookmark[/color]
        rs.Bookmark = bytCurrRec

... when my var is DIM'd as a Byte, I get the following error: "Compile Error. Type Mismatch:array or user-defined type expected." This is the genesis of my assertion that a variant type is, in fact, required, along with some of my own reading on the web about working with DAO bookmarks. Also, my parent Sub is heavily coded with this design in mind (Variant vars storing BKs), so wholesale changing the parent Sub is going to be a big re-write. One I hope I can avoid!

Having said that, I took a second look at your suggestion about using StrComp, but your declaration of the func...

Do Until StrComp(rsc.Bookmark, vntStopPoint, vbBinaryCompare)

... doesn't look essentially different from simply accepting the vntStopPoint arg as a Byte in the child Sub and then using the comparison operator "=" to evaluate the statement. I think we're talking about the same thing when you set the vbBinaryCompare option. However, all of this is kind of moot until I can figure out why my damn child Sub call keeps generating a Type Mismatch error! So I re-wrote my child Sub to look like this...

Public Sub ParseSeatNumsOnly(ByVal RecSet As Recordset, ByVal varStartPoint As Variant, ByVal varStopPoint As Variant)
[COLOR=green]'Receive a recordset object and a bookmark. Run through records to the point of the point of the bookmark and stop. Parse out the players and their seat numbers.[/color]
On Error GoTo HandleErrors

Dim strUpdSQL, strGameNum As String     [COLOR=green]'Construct a SQL append statement[/color]
Dim rsc As DAO.Recordset                [COLOR=green]'Make a clone of the recordset[/color]

[COLOR=green]'Clone the recordset[/color]
Set rsc = RecSet.Clone

[COLOR=green]'set the cloned recordset to the start point[/color]
rsc.Bookmark = varStartPoint

[COLOR=green]'Retrieve the game number[/color]
strGameNum = CopyInString("#", rsc.Fields("Field1"), ":")

strUpdSQL = "UPDATE Games SET "

[COLOR=green]'loop thru record set until the Stop Point is reached. Build the update string with the player's name and match to the game number[/color]
Do Until StrComp(rsc.Bookmark, varStopPoint, vbBinaryCompare) = 0
    strUpdSQL = strUpdSQL & AppendSeatNums(rsc.Fields("Field1"))
    strUpdSQL = strUpdSQL & " WHERE GameNo = '" & CopyInString("#", strGameNum, ":") & "'"
    DoCmd.RunSQL strUpdSQL
    rsc.MoveNext
Loop

... which matches exactly the call in the parent Sub and I still get a Type Mismatch error. Grrr....

 

[xhat]

Also note that the Type Mismatch error occurs at runtime, not compile time! I had to put a msgbox in my error handler to get the err.number and description.

 

[xhat]

Well, all, thanks for your help. Nothing like a little reboot of the O/S to set everything back to working order... Oh, and blowing away a reference library. Who the heck knows... But Harley, looks as though your StrComp idea is working, so what the hell...

 

[MajP]

The problem is probably something entirely different

NEVER
NEVER
NEVER

write this in access
dim rs as recordset

Always write it as
dim rs as dao.recordset
or
dim rs as adodb.recordset

My guess is if you look in your references the first listed reference is ADO followed by DAO.
That means when you do this
dim rs as recordset
you get a ADO recordset.

And thus when you pass it to your function you get a type mismatch error only at runtime.

I always thought it is stupid in vb that we have many object models with the same name. They could have simply made a "adoRecordset" object.

 

[MajP]

The absolute position is the position a record is in the recordset. Pretty basic. The first record is 0 the second is 1 etc. All Oriely is saying is something we all know. There is no such thing as a record number in databases. The position is based on a sort order. There is an important differenc between the absolute position and a book mark. You can get the absolute position and if you requery the database (assuming same sort order) the absolute position remains the same. You can not do this with a bookmark. Once you requery you get a new unique set of bookmarks. Also two recordsets based on the same query or table have different bookmarks unless the recordset is a clone of the other recordset.

 

[xhat]

MajP & Harely - Thanks for your help guys. Everything is working smoothly now, and I've learned a good crash course in DAO recordsets and bookmarks.