Using BOF, EOF

[fun...@bellsouth.net]

Greetings all:

My goal is to determine whether the table (recordset?) being used by my
form (bound to the controls?) is at eof. Apparently, BOF and EOF need
the current record set's name. I found the "currentdb" function but
can't find a "currentrecordset" or similar function. Any ideas anyone?

God Bless,
Pierre

[Steven Blackwell]

Generally you set up a recordset in a similar manner as this.

Dim rst as recordset
Dim db as database
set db = currentdb
set rst = db.openrecordset("TableName")

if rst.EOF then
'do something
end if

As far as I know BOF and EOF properties can only be used on the Recordset
object. The recordset object is set up and instantiated as shown above.

This is for opening a query or table or a dynamic query within some VBA code
for processing.
Are you trying too see of a User is at the end of a set of records bound to
a form using the record source property from the data tab of the form
properties? If so we will have to look for some other method of checking for
this state.

<fun...@bellsouth.net> wrote in message
news:3B66EE22...@bellsouth.net...

[fun...@bellsouth.net]

Greetings, Steve:

That is exactly the situation I'm in. The properties dialog was used to bind
the objects to the recordset. By your response, I'm assuming that a completely
coded method exists.

That's actually what I want. I wish to use Access only for its GUI tools and
control all execution, including GUI property settings using VB code. In
researching how to create a master Access startup procedure, I've only
discovered setting one via a startup Access database's startup form and
specifying that through my Access Windows icon command line. Any alternative
method tips that would allow me to programatically initiate control database,
table and form setup would be greatly appreciated.

God Bless,
Pierre

[Steven Blackwell]

I'm not quite sure what your goal is. I know it is hard to ask for specific
help and not disclose sensitive company information or processes.

I know that most property settings within access including form and object
properties can be retrieved, or set with VBA. I found that the help index
and related help files were a great information source on how to work with
them.

If you can, I would like to hear more about the project so that I can try to
point you in the right direction. If I have the knowlege to do so.

<fun...@bellsouth.net> wrote in message
news:3B6705CB...@bellsouth.net...

[Sandra Daigle]

Here is one way - the form has a property named Recordsetclone,
through which you can directly access the underlying recordset.

With Me.RecordsetClone
.Bookmark = Me.Bookmark
.MoveNext
If .EOF Then
MsgBox "EOF is true"
End If
.Bookmark = Me.Bookmark
.MovePrevious
If .BOF Then
MsgBox "BOF is true"
End If
End With

--

Sandra Daigle, Microsoft Access MVP

<fun...@bellsouth.net> wrote in message
news:3B66EE22...@bellsouth.net...

[Eric Hubert]

What advantage is there to using the RecordsetClone
property of a form instead of just the Recordset
property? If I delete a record using the RecordsetClone
propetry and then do a Me.Requery, would the deleted
record appear on the form?

Thanks,
Eric

>.
>

[fun...@bellsouth.net]

Greeetings Steve:

Thanks for responding again.

To summarize, all of the utilization examples of recordset properties such as
BOF, EOF and recordset name seem to assume that you have opened the recordset
via the "OpenRecordset" function (method?) and have stored the return value in a
variable which you can then use to refer to the recordset when ascertaining its
property settings, ie. "MyRecordSetNameVariable.BOF" or
"MyRecordSetNameVariable.RecordCount"

Understanding that that may certainly be the simplest way and I certainly wish
to reconfigure the application to follow that scheme, I currently have a form
whose controls have been bound to a table via their properties dialog.
Currently, my goal is to enter code intended to ascertain and react to the
current recordset's current state. My question is, "How does VB want me to ask
it about the current recordset properties when I didn't open the recordset via
"OpenRecordset" but, instead, bound the tables via the form and control
properties?".

Is there is a tool similar to "currentdb" for databases or "Me" for forms?

God bless greatly for your help,
Pierre

[Sandra Daigle]

I use Recordsetclone when I want to do something "behind the scenes"
so to speak. In the example I gave earlier, if I had used the
Recordset property the current record on the form would have flashed
back and forth and form events would have occured that would take
extra time and might cause undesired errors.

I also often use the recordsetclone for searching a recordset for a
particular value - again, without affecting what is displayed on the
form.

You can delete a record using the recordsetclone - and no it will not
appear on the form.

Sandra Daigle, Microsoft Access MVP

"Eric Hubert" <erich...@black-hole.com> wrote in message
news:bba901c11a08$3c86c3d0$19ef2ecf@tkmsftngxa01...

[fun...@bellsouth.net]

Greetings Sandra:

Thanks for responding.

It appears that "recordsetclone" allows the recordset and its clone to
have different current record number values and requires additional
processing to ensure that this doesn't occur. Are you aware of a
recordset version of the "currentdb" for databases or "Me" for forms?

God Bless,
Pierre

[Sandra Daigle]

Use Recordset or Recordsetclone - that's it! If your form is bound
then both of these properties allow you to reference the underlying
recordset which is what you are asking to do. Yes, recordsetclone is a
clone and to make sure that the form and the recordsetclone are
looking at the same record you set its bookmark:

me.recordsetclone.bookmark = me.bookmark

This allows you to examine and manipulate the recordset independantly
of the form - which is often quite advantageous! Yes, you do have to
be careful that you set the bookmark when appropriate.

--

Sandra Daigle, Microsoft Access MVP

<fun...@bellsouth.net> wrote in message
news:3B672D46...@bellsouth.net...

[Graham Mandeno]

Hi Eric

The major difference between the Recordset and RecordsetClone objects is
that the Recordset reflects the currency of the form - in other words,
navigation to a new record on the form will also change the current record
in the Recordset, but not the RecordsetClone. To synchronise between the
RecordsetClone and the current form record you must use bookmarks

Here are some examples:

1. Me.Recordset.MoveNext is (almost) equivalent to DoCmd.GotoRecord
Record:=acNext

2. Me.Recordset.FindFirst "RecordID=" & cboPickRecord
is (almost) equivalent to
With Me.RecordsetClone
.FindFirst "RecordID=" & cboPickRecord
Me.Bookmark = .Bookmark
End With

In the second example, because there is no such thing as
Me.Recordset.NoMatch, if the corresponding record does not exist then there
is no error raised, but the form is reset to the first record.

Records edited or deleted in the RecordsetClone are reflected in the
Recordset without requerying.

Obviously the RecordsetClone is better suited for such tasks as looping
through the records in a form or returning .RecordCount without altering the
currently displayed record.

One word of warning - the type of object returned by Me.Recordset may vary -
for example, with an .MDB you get a DAO.Recordset, but with an .ADP you get
an ADODB.Recordset.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: MVP^mandeno.com (replace "^" with "@")
Please post new questions to newsgroups.

"Eric Hubert" <erich...@black-hole.com> wrote in message
news:bba901c11a08$3c86c3d0$19ef2ecf@tkmsftngxa01...

[fun...@bellsouth.net]

Greetings Graham:

Thanks for responding.

Could you confirm my understanding that use of .Recordcount or .EOF/BOF creates
an environment-altering event?

In addition, I couldn't get "Recordset.EOF" to work where "RecordsetClone.EOF"
did. Is RecordSet.EOF valid?

God Bless,
Pierre

[Eric Hubert]

Thank you Graham and Sandra for your replies. It cleared
things up.

-Eric