list of fields and their names in a form, under Access 2000 (* I am lost! *)

[Gilles Maisonneuve via AccessMonster.com]

Hello,

I a a newbie under Access. I've read the topics already dealing with field
list in a form and how to handle but I have a problem: all the code samples
given in this site and also with the help file provided with my (French)
version of Access 2000 do not work.

More precisely:
1?) DAO.Recordset does not exist so no code can be coded with this type of
data
2?) using Recordset type to declare variable always bring me to "type
incompatible" (I translate from French "Incompatibilit? de type") when I
try the following code:

Dim rst As Recordset
Set rst = Me.RecordsetClone <<< here is the erroneous statement seen
under the debugger

I can do it with the following syntax:

Dim rs As Object
Set rst = Me.RecordsetClone

but then, when I use the code:

Dim fld As Field
For Each fld In rs
Debug.Print fld.Name
Next fld

I get the error:
"Unauthorised operation for this type of object" (error 3251). Once again I
translate from French ("Op?ration non autoris?e pour ce type d'object") to
English so forgive me if its the wrong message text.


I'va been looking fo the whole day on internet and in the Access help files
and I get a clue while all those samples are not functionning (I've also
trie to use the syntax I found in the help and on the web: "For Each fld In
Me.Recordset" and of course I get the same error message).

*** Please can somebody help me, I am completely lost! ***

Gilles (my address: gilles AT maisonneuve.dabsol.co.uk)

 

[Troy]

In order to use the DAO recordset Type, you need to set a reference to DAO
(Microsoft Data Access Objects 3.6).

Code Window/IDE -->Tools -->References -->Scroll down until you find
"Microsoft Data Access Objects 3.6" -->Check the check box for this
reference

It *must* exist (yes, even in French <g>) for Access to even run at all!

--
Troy

Troy Munford
Development Operations Manager
FMS, Inc.
www.fmsinc.com


in message Hello,

I a a newbie under Access. I've read the topics already dealing with field
list in a form and how to handle but I have a problem: all the code samples
given in this site and also with the help file provided with my (French)
version of Access 2000 do not work.

More precisely:
1?) DAO.Recordset does not exist so no code can be coded with this type of
data
2?) using Recordset type to declare variable always bring me to "type
incompatible" (I translate from French "Incompatibilit? de type") when I
try the following code:

Dim rst As Recordset
Set rst = Me.RecordsetClone <<< here is the erroneous statement seen
under the debugger

I can do it with the following syntax:

Dim rs As Object
Set rst = Me.RecordsetClone

but then, when I use the code:

Dim fld As Field
For Each fld In rs
Debug.Print fld.Name
Next fld

I get the error:
"Unauthorised operation for this type of object" (error 3251). Once again I
translate from French ("Op?ration non autoris?e pour ce type d'object") to
English so forgive me if its the wrong message text.


I'va been looking fo the whole day on internet and in the Access help files
and I get a clue while all those samples are not functionning (I've also
trie to use the syntax I found in the help and on the web: "For Each fld In
Me.Recordset" and of course I get the same error message).

*** Please can somebody help me, I am completely lost! ***

Gilles (my address: gilles AT maisonneuve.dabsol.co.uk)

 

[Douglas J. Steele]

Actually, Access can run fine without the DAO reference, Troy. After all,
neither Access 2000 nor Access 2002 have that reference by default, and they
both run.

 

[Troy]

HA! Sorry, I was really unclear in my last post. I did not mean to say that
the *reference* must exist, but rather that DAO must exist on the
machine.....After re-reading it, that certainly isn't what I wrote down,
though....Silly scattered thoughts move so quickly...

--
Troy

Troy Munford
Development Operations Manager
FMS, Inc.
www.fmsinc.com


Actually, Access can run fine without the DAO reference, Troy. After all,
neither Access 2000 nor Access 2002 have that reference by default, and they
both run.

 

[Gilles Maisonneuve via AccessMonster.com]

Hemm...

I don't get a clue of what you are talking about because there is no such
thibng as a menu: "Code Window/IDE" in the menu list of my Access 2000
window. I have the usual list
"File Edit View(or Display?) Insert Format Records Tools Windows ?"

In the "Tools" menu there is no such thing as a "Reference" sub-menu. I
only have:
Orthograph
Automatic corrections
Link to Office
On-line collaboration
Relations
Analyze
DB tools
Securtity
Replication
Start-up
Macros
Active-X controls
Complementary macros
Personalization
Options (Preferences?)

There is no such thing in the Access 2000 help to set up a "reference to
ADO" or to enable ADO.
As the forms are running smoothly and my VB code is running normaly either
the access to the ME.* and ME!* stuff and the ADODB declaration I thought
this would work.

It seems that the DAO recordset declaration in my version of VBA has been
replaced by a simple declaration to Recordset on the contrary of the help
samples I found. But still I can't access the Recordset fields that way.

My probleme is still open. What do you think?

Gilles

Here is another piece of code I tried:

Dim fld As Field
For Each fld In Me.Recordset
Debug.Print fld.Name
Next fld


if I try the following:

Dim rs As Object
Dim fld As Field
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark
rs.MoveFirst
For Each fld In rs
Debug.Print fld.Name
Next fld

I still get the same error message talking about an illegal/invalid
operation on such kind of type of object.

If I try to replace the declaration line:
Dim rs As Object
with
Dim rs As Recordset

I get the same error but at the rs assignment statement.

Does anyone has a clue ?

 

[Douglas J. Steele]

You must be in the VB Editor to get the Reference submenu on the Tools menu.

 

[Gilles Maisonneuve via AccessMonster.com]

Hi Douglas,

Thank you for the explaination, sorry if I did not understand you well
about the options/references. I had no idea about all those reaferences to
set.

Finaly my code works now. In any case someone is interested in, the valid
code works that way:

Dim rs As DAO.Recordset
Dim fld As DAO.Field
Set rs = Me.Recordset
For Each fld In rs.Fields
Debug.Print fld.Name
Next fld

Each time the DAO. prefix must be used because it seems that the default
type of Recordset and Field is ADODB... EVEN IF declaring an ADODB
recordset or field also requires the prefix ADODB.

A final question about this: are these settings (references) save into the
database or into Access configuration. I mean if I reinstall Access on
another machine (the target machine) will I have to recopy all those
settings on the target machine or just by copying the .MDB I created with
the good VBA code in it, it will keep the appropriate VBA settings?

Thank you for your help Douglas, you were very kind.

Now that you have opened my mind on all the "references" I will try to take
care about it.

Gilles