Unsatisfying syntax

[Feico]

With a little help from my friends (that's you) I succeeded to write a
little program.

To access a field in the current record, I simply write its name, for
example:
Value = calculation
Which is, of course, shorthand for
CurrentTable.CurrentRecord.Value = calculation

To access another field, I write
rs.Fields("Value")

Although this works, I really think that such a complicated syntax,
where a string is used instead of an identifier, is pretty improper. I
think that something like
rs.Value
is a lot better

What should it be?
--
Feico

[Rick Brandt]

Feico wrote:

With a little help from my friends (that's you) I succeeded to write a
little program.

To access a field in the current record, I simply write its name, for
example:
Value = calculation
Which is, of course, shorthand for
CurrentTable.CurrentRecord.Value = calculation

To access another field, I write
rs.Fields("Value")

Although this works, I really think that such a complicated syntax,
where a string is used instead of an identifier, is pretty improper. I
think that something like
rs.Value
is a lot better

What should it be?

If you have an open RecordSet object then you can refer to a field with...

RS!FieldName

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[lylefair]

rs.Collect(Index)
that is:
rs.Collect("NameofField")
or
rs.Collect(3) where 3 is the ordinal position of the field with the
positions beginning at zero.

[Feico]

On Tue, 18 Oct 2005 12:39:58 GMT, "Rick Brandt"
<ri*********@hotmail.com> wrote in comp.databases.ms-access:

If you have an open RecordSet object then you can refer to a field with...

RS!FieldName

I find this much neater than RS.Fields("FieldName")
Unfortunately it didn't work.
Well, you say that a RecordSet must be open.
I executed this:

set db = currentdb
set rs = db.openrecordset("Table")

Doesn't that mean that a RecordSet is open?
--
Feico

[Larry Linson]

"Feico" <fe***@gmsrem0vethis.net> wrote

set db = currentdb
set rs = db.openrecordset("Table")

Doesn't that mean that a RecordSet is open?

It should. Did you move to a Record? When you are doing ADO or DAO, and open
a Recordset, you still have to navigate to the Record you want or iterate
through the Records.

After set rs = db.openrecordset("Table"), you need something like

If Not rs.BOF And rs.EOF Then 'not an empty recordset
rs.MoveFirst 'move to first record
Do Until rs.EOF
<your processing for each record>
rs.MoveNext
Loop

If you are iterating through the records.

And, I am assuming that you are simply using "Table" and "Value" to
represent the actual names. Those are not good names to use; they are Access
reserved words, and can cause confusion.

Larry Linson
Microsoft Access MVP

[Feico]

On Wed, 19 Oct 2005 01:11:29 GMT, "Larry Linson"
<bo*****@localhost.not> wrote in comp.databases.ms-access:

"Feico" <fe***@gmsrem0vethis.net> wrote

set db = currentdb
set rs = db.openrecordset("Table")

Doesn't that mean that a RecordSet is open?
It should. Did you move to a Record? When you are doing ADO or DAO, and open
a Recordset, you still have to navigate to the Record you want or iterate
through the Records.

After set rs = db.openrecordset("Table"), you need something like

If Not rs.BOF And rs.EOF Then 'not an empty recordset
rs.MoveFirst 'move to first record
Do Until rs.EOF
<your processing for each record>
rs.MoveNext
Loop

If you are iterating through the records.

And, I am assuming that you are simply using "Table" and "Value" to
represent the actual names. Those are not good names to use; they are Access
reserved words, and can cause confusion.

Actually, the program is this (irrelevant bits removed):

Option Compare Database

Private Sub BTW1_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim inkoopin as Currency

inkoopin = 0

Set db = CurrentDb
Set rs = db.openrecordset("Inkoop")

Do While Not rs.EOF
inkoopin = inkoopin + rs.Fields("Bedrag") '(1)
inkoopin = inkoopin + rs!Bedrag '(2)
rs.MoveNext
Loop

(1) This works, but I dislike the syntax
(2) I like this syntax, but it doesn't work

Error message (translated into English, so the text may be a bit
different): 3265 during execution. Element not found in set.

These instructions: If Not rs.BOF And rs.EOF Then 'not an empty recordset
rs.MoveFirst 'move to first record

are missing in my program, but the table is not empty, and I find that
MoveFirst is automatic.
--
Feico

[Rick Brandt]

Feico wrote:

Actually, the program is this (irrelevant bits removed):

Option Compare Database

Private Sub BTW1_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim inkoopin as Currency

inkoopin = 0

Set db = CurrentDb
Set rs = db.openrecordset("Inkoop")

Do While Not rs.EOF
inkoopin = inkoopin + rs.Fields("Bedrag") '(1)
inkoopin = inkoopin + rs!Bedrag '(2)
rs.MoveNext
Loop

(1) This works, but I dislike the syntax
(2) I like this syntax, but it doesn't work

Error message (translated into English, so the text may be a bit
different): 3265 during execution. Element not found in set.

I have always used (2) and never had a problem with it. If I got that error I
would assume it was because I spelled the name of the field incorrectly.

Does rs!Fields!Bedrag work for you?

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Feico]

On Wed, 19 Oct 2005 11:27:15 GMT, "Rick Brandt"
<ri*********@hotmail.com> wrote in comp.databases.ms-access:

inkoopin = inkoopin + rs.Fields("Bedrag") '(1)
inkoopin = inkoopin + rs!Bedrag '(2) (1) This works, but I dislike the syntax
(2) I like this syntax, but it doesn't work

Error message (translated into English, so the text may be a bit
different): 3265 during execution. Element not found in set.

I have always used (2) and never had a problem with it. If I got that error I
would assume it was because I spelled the name of the field incorrectly.

Does rs!Fields!Bedrag work for you?

Nope, same error message. And of course I checked the spelling of the
name. And it is case insensitive, isn't it?
--
Feico

[John]

both of these should work:

inkoopin = inkoopin + rs.Fields("Bedrag") '(1)
inkoopin = inkoopin + rs!Bedrag '(2)

neither version is case sensitive.
i'm guessing that you might get different error messages for each
version, if the field you are after is not in the rs.

try dumping out all the fields in the rs just prior to referencing the
field.

For iCol = 0 To rs.Fields.Count - 1
msgbox(rs.Fields(iCol).Name
Next

[Feico]

On 19 Oct 2005 05:21:46 -0700, Jo**@ViridianTech.com wrote in
comp.databases.ms-access:

try dumping out all the fields in the rs just prior to referencing the
field.

For iCol = 0 To rs.Fields.Count - 1
msgbox(rs.Fields(iCol).Name
Next

Well, it displays the names of all columns in the table.

However, I just made a new discovery. The trouble only occurs when
there are spaces in the name of the column. I replaced these with
underscores and was pretty sure that this ws correct.

inkoopin = inkoopin + rs.Fields("Bed rag") '(space in name)
inkoopin = inkoopin + rs!Bed_rag '(underscore in name)

The latter is not accepted. There is no problem if there is no
space/underscore in the name.
--
Feico

[John]

Oh, so the example you first gave was not exactly working (i.e
crashing) code. You're making it harder on us to help you debug.

yes, blanks in the name are bad, don't use them. it messes up all
sorts of stuff. and it won't upsize if you ever intend to convert to
Oracle or Sql Server. you'll have problems the rest of your life with
stored procs and such.

If you're fussy about syntax, you're going to hate this..
I think you'll have to use this...rs![bed rag]

[Feico]

On 19 Oct 2005 08:15:12 -0700, Jo**@ViridianTech.com wrote in
comp.databases.ms-access:

Oh, so the example you first gave was not exactly working (i.e
crashing) code. You're making it harder on us to help you debug.
I'm sorry. In other parts of the syntax spaces should be replaced by
underscores, and I could not imagine that it wouldn't work in other
positions.
If you're fussy about syntax, you're going to hate this..
I think you'll have to use this...rs![bed rag]

Well, after all it solves my problems.

BTW, the program is not about blankets. "Bedrag" is Dutch and means
"amount of money" .
--
Feico

[David W. Fenton]

Jo**@ViridianTech.com wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:

Oh, so the example you first gave was not exactly working (i.e
crashing) code. You're making it harder on us to help you debug.

yes, blanks in the name are bad, don't use them. it messes up all
sorts of stuff. and it won't upsize if you ever intend to convert
to Oracle or Sql Server. you'll have problems the rest of your
life with stored procs and such.

If you're fussy about syntax, you're going to hate this..
I think you'll have to use this...rs![bed rag]

Or rs("bed rag") will work, too.

I agree -- no spaces or special characters in field names. It makes
programming much, much easier with no compromise of readability (if
you use upper case to demarcate word divisions).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc