Bookmarks

[Guest]

Help please. Maybe I just dont undererstand Bookkmarks but I want to find a
record in my table and have the Form's bound textboxes display the
appropriate information. Easy, you might say just use the following

rs1.move 246
me.bookmark = rs1.bookmark

and the appropriate listboxes in the form will show the data that is held in
record no 256 in the Table. I have done this and it works fine; up to a point.
Everything is fine with every record in the Table up to record number 452,
record number 453 and over over produces an ERROR "Runtime Erroe 3159 Not a
valid Bookmark" I can not beleive that Acces is limited to storing Tables
with a maximum of 452 records.
Where have I gone wrong?
Thanks RayC

 

[Arvin Meyer [MVP]]

I use a bookmark to move to records in an 80,000+ record table. I don't use
the offset method though. I find a record using an index, then move there.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Rick Brandt]

Help please. Maybe I just dont undererstand Bookkmarks but I want to
find a record in my table and have the Form's bound textboxes display
the appropriate information. Easy, you might say just use the
following

rs1.move 246
me.bookmark = rs1.bookmark

and the appropriate listboxes in the form will show the data that is
held in record no 256 in the Table. I have done this and it works
fine; up to a point. Everything is fine with every record in the
Table up to record number 452, record number 453 and over over
produces an ERROR "Runtime Erroe 3159 Not a valid Bookmark" I can not
beleive that Acces is limited to storing Tables with a maximum of 452
records.
Where have I gone wrong?

How many records in your table. As I understand it rs1.move 246 does not move
you to record number 246. It moves you 246 records forward from where you are
currently positioned. If you are already near the end then a relatively small
number would produce that error.

 

[Guest]

In this particular Table I have 850 records so I understand what you are
saying. I have changed the statement to rs1.movefirst (to get to the first
record) followed by rs1.move 453 (to move to the 453rd record in the table )
Same error.
Everything is fine at 452 but error at 453

RayC

 

[Guest]

My table has about 850 records at the moment and the number that I am wanting
to move to is the record index number. Are you saying that I should use the
statement FindFirst StrCriteria and will that have any effect on the Bookmark
problem?

Thnks RayC

 

[Rick Brandt]

My table has about 850 records at the moment and the number that I am
wanting to move to is the record index number. Are you saying that I
should use the statement FindFirst StrCriteria and will that have any
effect on the Bookmark problem?

What Arvin is saying (I think) is that moving to a record based on the record
position number is very seldom of any practical use. That number is not tied to
a particular record in any way and will refer to just about any record in your
database depending on current filtering and sorting applied to the form. What
is at record position 454 cannot be relied upon if you actually expect to go to
a particular record.

 

[Guest]

Hi Rick, what I am looking for is the Record Identifier which (in this
particular instance) just happens to be in a table where the ID'S run
consecutively from 1 to 850, though I understand what you are saying.
I have changed the code to be FindFirst(StrCriteria) where strcriteria is
some information that is held in the record I am looking for. However. I
still get exactly the same error from the me.Bookmark = rs1.Bookmark
statement when I look for a record that is greater than the 452nd record in
the Table.
RayC

 

[Rick Brandt]

Hi Rick, what I am looking for is the Record Identifier which (in this
particular instance) just happens to be in a table where the ID'S run
consecutively from 1 to 850, though I understand what you are saying.
I have changed the code to be FindFirst(StrCriteria) where
strcriteria is some information that is held in the record I am
looking for. However. I still get exactly the same error from the
me.Bookmark = rs1.Bookmark statement when I look for a record that is
greater than the 452nd record in the Table.
RayC

Post your exact code. I can think if no reason for that to be the case if you
are using FindFirst.

 

[Guest]

Oh Dear, Showing my Crappy code to the experts fills me with trepidation.
Anyway, you will need some explanation. What I am wanting to achieve is to
have the various fields populate with the relevant information that is held
in a record. There are a number of Combo, "Find" Boxes that are unbound an
the other textBoxes are bound to the Table "tblCustomers". The ShowInfo Sub
gets its information passed to it from the calling routine e.g strField could
be "CustomerName" and varInfo could be "Fred" (though in my case, I am only
looking for a unoque identifier eg strField could = "AccountNo" and varinfo
could = "ABC1234". Once the appropriate rec9ord is found I use the
Me.Bookmark = rs1.Bookmark to shift to that record and populate all of the
Bound Text Boxes with data. I then go through all of the UnBound Combo Boxes
and fill them individualy with information.

By the way, you will see my attempt to build a critera by using a String
Variable (varInfo) but this needs to be eveloped in Quatation marks (") my
attempt does not work, is there any wat to do that?

Private Sub ShowInfo(strField As String, VarInfo As Variant)
Dim dbs As Database, rs1 As Recordset
Set dbs = CurrentDb
Set rs1 = dbs.OpenRecordset("tbl_Customer_Wool", dbOpenDynaset)

Debug.Print IsNumeric(VarInfo)
'strCriteria = strField & "=" & """ & VarInfo & """
If IsNumeric(FieldInfo) Then
strCriteria = BuildCriteria(strField, dbInteger, VarInfo)
Else
strCriteria = BuildCriteria(strField, dbText, VarInfo)
End If

rs1.FindFirst (strCriteria)
'rs1.MoveFirst: rs1.Move a
'cmbCustomerID = a
'CustomerID = a
'Me!CustomerID = rs1.cmbCustomerID ' CustomerID
Me!cmbFindAccNo = rs1("AccountNo") ' cmbFindAccountNo
Me!cmbFindCustomerName = rs1("CustomerName") ' cmbFindCustomerName
Me!cmbFindStoreNo = rs1("StoreNo") ' cmbFindStoreNo
Me!cmbFindStoreName = rs1("StoreName") ' cmbFindStoreName
Me!cmbContractNo = rs1("ContractNo")
' Debug.Print rs1.Bookmark, Val(rs1.Bookmark)
Me.Bookmark = rs1.Bookmark
rs1.Close: dbs.Close

End Sub

Hope that this helps
RayC

 

[Rick Brandt]

Oh Dear, Showing my Crappy code to the experts fills me with
trepidation. Anyway, you will need some explanation. What I am
wanting to achieve is to have the various fields populate with the
relevant information that is held in a record. There are a number of
Combo, "Find" Boxes that are unbound an the other textBoxes are bound
to the Table "tblCustomers". The ShowInfo Sub gets its information
passed to it from the calling routine e.g strField could be
"CustomerName" and varInfo could be "Fred" (though in my case, I am
only looking for a unoque identifier eg strField could = "AccountNo"
and varinfo could = "ABC1234". Once the appropriate rec9ord is found
I use the Me.Bookmark = rs1.Bookmark to shift to that record and
populate all of the Bound Text Boxes with data. I then go through all
of the UnBound Combo Boxes and fill them individualy with
information.

By the way, you will see my attempt to build a critera by using a
String Variable (varInfo) but this needs to be eveloped in Quatation
marks (") my attempt does not work, is there any wat to do that?

Private Sub ShowInfo(strField As String, VarInfo As Variant)
Dim dbs As Database, rs1 As Recordset
Set dbs = CurrentDb
Set rs1 = dbs.OpenRecordset("tbl_Customer_Wool", dbOpenDynaset)

Debug.Print IsNumeric(VarInfo)
'strCriteria = strField & "=" & """ & VarInfo & """
If IsNumeric(FieldInfo) Then
strCriteria = BuildCriteria(strField, dbInteger, VarInfo)
Else
strCriteria = BuildCriteria(strField, dbText, VarInfo)
End If

rs1.FindFirst (strCriteria)
'rs1.MoveFirst: rs1.Move a
'cmbCustomerID = a
'CustomerID = a
'Me!CustomerID = rs1.cmbCustomerID ' CustomerID
Me!cmbFindAccNo = rs1("AccountNo") ' cmbFindAccountNo
Me!cmbFindCustomerName = rs1("CustomerName") '
cmbFindCustomerName Me!cmbFindStoreNo = rs1("StoreNo")
' cmbFindStoreNo Me!cmbFindStoreName = rs1("StoreName") '
cmbFindStoreName Me!cmbContractNo = rs1("ContractNo")
' Debug.Print rs1.Bookmark, Val(rs1.Bookmark)
Me.Bookmark = rs1.Bookmark
rs1.Close: dbs.Close

End Sub

Hope that this helps

Okay, you are "over-engineering" this a bit. The form already has a Recordset
which can be cloned using RecordsetClone so there is no need to create a
database object or Recordset object. Simplified you can just use...

With Me.RecordsetClone
.FindFirst strCriteria
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With

 

[Guest]

Hi Rick
This works fine and is such a nice, compact piece of code. I don't know why
the books always eem to spend pages telling you to do it in the way I did. Or
maybe I am just not looking hard enough.
Many thanks for your invaluable help. RayC

 

[Rick Brandt]

Hi Rick
This works fine and is such a nice, compact piece of code. I don't know why
the books always eem to spend pages telling you to do it in the way I did. Or
maybe I am just not looking hard enough.
Many thanks for your invaluable help. RayC

My example is pretty much the "standard" way to use bookmarks for navigating.
If you're book is suggesting otherwise then you might want to shop around for an
alternative.

For example, if you use the ComboBox wizard to create a ComboBox, one of the
wizard choices will build a navigation ComboBox that uses the RecordsetClone in
much the same manner as the code I gave you.

 

[Guest]

Thanks Rick
I think that the problem comes from me strugling to understand the
fundamental rules of what I am reading. Hopefully that will come.
I like the code, and because I will want to use that in a number of
different Forms, it seems to make sense to re-write it into a Utility that
can be called from any Form (don't hold thr samr code in each individual
form) In order to achieve that, I will need to re write it in a format that
does not rely on the Me. function. Could you give me any pointers in that
direction

Thanks RayC

 

[Rick Brandt]

Thanks Rick
I think that the problem comes from me strugling to understand the
fundamental rules of what I am reading. Hopefully that will come.
I like the code, and because I will want to use that in a number of
different Forms, it seems to make sense to re-write it into a Utility that
can be called from any Form (don't hold thr samr code in each individual
form) In order to achieve that, I will need to re write it in a format that
does not rely on the Me. function. Could you give me any pointers in that
direction

Generally you would have to pass the form (either the actual object or its name
as a string) to such a function and the strCrit string you want to use.
Something like...

Function FormNavigate(frm AS Form, crit As String)

With frm.RecordsetClone
.FindFirst crit
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With

End Function

 

[Guest]

That is Just Great. Beats the spots off my great lumbering Code.
Where would we be without you guys?

Thanks a million RayC