Use a string as a field name

[Tom]

I am looking through a recordset at individual records, when i call

rs1![Field 1] its fine

however, i want to be able to use a variable name instead, which contains
the name of the field as a string e.g.

rs1!Variable1

Note: The String may contain spaces.
How do i get this to work?
Thanks

Tom

 

[Dirk Goldgar]

I am looking through a recordset at individual records, when i call

rs1![Field 1] its fine

however, i want to be able to use a variable name instead, which contains
the name of the field as a string e.g.

rs1!Variable1

Note: The String may contain spaces.
How do i get this to work?
Thanks

Use:

rs1(Variable1)

or, more explicit:

rs1.Fields(Variable1)

 

[Tom]

Thanks, That helped.

A further question:

If i want to check whether this 'cell' is empty, how would i do this, because

if rs1.Fields(Variable1) = "" then...

does not do it.

And how do i edit this field.+

Thanks again

Tom

I am looking through a recordset at individual records, when i call

rs1![Field 1] its fine

however, i want to be able to use a variable name instead, which contains
the name of the field as a string e.g.

rs1!Variable1

Note: The String may contain spaces.
How do i get this to work?
Thanks

Use:

rs1(Variable1)

or, more explicit:

rs1.Fields(Variable1)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Douglas J. Steele]

There's a difference between a zero-length string ("") and Null. If you're
not sure which your field might be, use

If Len(rs1.Fields(Variable1) & vbNullString) = 0 Then
' There's nothing in the field
Else
' There's something in the field
End If

If your intent is to put a value into the field if there's nothing there
currently, you'd use either

If Len(rs1.Fields(Variable1) & vbNullString) = 0 Then
rs1.Edit
rs1.Fields(Variable1) = somevalue
rs1.Update
End If

or

If Len(rs1.Fields(Variable1) & vbNullString) = 0 Then
rs1.Fields(Variable1) = somevalue
rs1.Update
End If

depending on whether it's a DAO recordset (the first option) or an ADO
recordset (the second option)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, That helped.

A further question:

If i want to check whether this 'cell' is empty, how would i do this,
because

if rs1.Fields(Variable1) = "" then...

does not do it.

And how do i edit this field.+

Thanks again

Tom

I am looking through a recordset at individual records, when i call

rs1![Field 1] its fine

however, i want to be able to use a variable name instead, which
contains
the name of the field as a string e.g.

rs1!Variable1

Note: The String may contain spaces.
How do i get this to work?
Thanks

Use:

rs1(Variable1)

or, more explicit:

rs1.Fields(Variable1)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks, That helped.

A further question:

If i want to check whether this 'cell' is empty, how would i do this,
because

if rs1.Fields(Variable1) = "" then...

does not do it.

The zero-length string, "", is itself a value, and not the same as empty or
Null. Usually you want to check a field to see if it is Null, which you can
do in VBA like this:

If IsNull(rs1.Fields(Variable1)) Then.

Text and memo fields can hold zero-length strings, and sometimes you may
want to check if such a field is either Null or a ZLS. In that case, you
can use a short-cut to check for both possibilities at once, like this:

If Len(rs1.Fields(Variable1) & "") = 0 Then

The concatenation forces the value, if Null, to a zero-length string. Note
that you have no reason to do this with other types of fields, as they can't
hold string values.

And how do i edit this field.+

That depends on what kind of recordset you have. If it's a DAO recordset,
then you have to put the recordset first into Edit mode, then modify the
values, and then Update the record:

With rs1
.Edit
.Fields(Variable1) = "foo" ' a text field
.Fields(Variable2) = 123 ' a number field
.Update
End With

If it's an ADO recordset, you don't have to call any .Edit method, but you
still have to call the .Update method to save the changed record.

 

[Tom]

Thanks again,

And i think finally,

how do i make a new record in a table with no records (and for that matter,
for a table with pre-existing records)

Thanks again

Tom

 

[Dirk Goldgar]

Thanks again,

And i think finally,

how do i make a new record in a table with no records (and for that
matter,
for a table with pre-existing records)

Y'know, the help file does have information on this sort of thing. You just
have to poke around a little.

To add a new record using a recordset, you call the AddNew method, then
update the field values, and then call the .Update method:

With rs1
.AddNew
.Fields("YourTextField") = "foo"
.Fields("YourNumberField") = 123
,Update
End With

Note that you can also use the bang (!) notation to refer to fields by name.
The following is exactly equivalent to the above code:

With rs1
.AddNew
!YourTextField = "foo"
!YourNumberField = 123
,Update
End With

But you can't use the bang with variable field names.

If you're adding a record with an autonumber field, you do not (normally)
assign a value to that field. Access will generate it.