Referencing values from a record set 
Author Message
 Referencing values from a record set

You are close. In DAO 3.5, you need to refer to recordset fields as
members of the Fields collection of the recordset object, which is the
default collection. You cannot refer to them as properties of the
recordset. So any of the following will do:
MyValue = rst.Fields("FieldName")
MyValue = rst("FieldName")
MyValue = rst!FieldName
MyValue = rst(strFieldNameVariable)

On Access 97 forms, you can refer to fields in the recordsource as
Me.FieldName, because those fields (and also the controls on the form)
are implemented as properties of the form's class. But DAO doesn't
make the fields properties -- they are just members of the Fields
collection, so you need to use the one of the syntaxes above, which
are used to refer to members of a collection, not rst.FieldName.

 -- Andy

Quote:

>Hi there

>first time working with record set

>    strSql = "SELECT * FROM Summary WHERE [WeekEnding] ="
>    strSql = strSql & "#"
>    strSql = strSql & Format(Forms![P_Summ]![WeekEnding], "mm-dd-yyyy")
>    strSql = strSql & "#"

>        Debug.Print "strSql = "; Tab(10); strSql

>    Set rst = dbs.OpenRecordset(strSql)

>    rst.MoveFirst
>        Debug.Print "Date = "; Tab(20);
>    rst.Close
>    Set rst = Nothing
>......
> the select statement give me the corret record that I want to look at.
>In order to reference the values held in this recordset ( eg week date ,
>production, sales etc.) how is that done.

>I would want to pass the value of he recordset sales to a long Eg accsales =

>Rst.sales?? or in a form me!production = rst.production ??

>Any Idear that pionts my nose to a solution is very much appreciated.

>Thanks
>Rene'



Thu, 26 Apr 2001 03:00:00 GMT  
 Referencing values from a record set
Hi there

first time working with record set

    strSql = "SELECT * FROM Summary WHERE [WeekEnding] ="
    strSql = strSql & "#"
    strSql = strSql & Format(Forms![P_Summ]![WeekEnding], "mm-dd-yyyy")
    strSql = strSql & "#"

        Debug.Print "strSql = "; Tab(10); strSql

    Set rst = dbs.OpenRecordset(strSql)

    rst.MoveFirst
        Debug.Print "Date = "; Tab(20);
    rst.Close
    Set rst = Nothing
......
 the select statement give me the corret record that I want to look at.
In order to reference the values held in this recordset ( eg week date ,
production, sales etc.) how is that done.

I would want to pass the value of he recordset sales to a long Eg accsales =

Rst.sales?? or in a form me!production = rst.production ??

Any Idear that pionts my nose to a solution is very much appreciated.

Thanks
Rene'



Fri, 27 Apr 2001 03:00:00 GMT  
 Referencing values from a record set
Its possible and works likewise, but not recomended, to reference using the
sintax rst.FiledName. Its not recomended because properties and methods are
"separeted" from the object using dots.
It's a good practice to reference using rst!FieldName because the FieldName
is something you named, so you will differentiate "your" objects from
properties and methods.

Quote:

>You are close. In DAO 3.5, you need to refer to recordset fields as
>members of the Fields collection of the recordset object, which is the
>default collection. You cannot refer to them as properties of the
>recordset. So any of the following will do:
>MyValue = rst.Fields("FieldName")
>MyValue = rst("FieldName")
>MyValue = rst!FieldName
>MyValue = rst(strFieldNameVariable)

>On Access 97 forms, you can refer to fields in the recordsource as
>Me.FieldName, because those fields (and also the controls on the form)
>are implemented as properties of the form's class. But DAO doesn't
>make the fields properties -- they are just members of the Fields
>collection, so you need to use the one of the syntaxes above, which
>are used to refer to members of a collection, not rst.FieldName.

> -- Andy


>>Hi there

>>first time working with record set

>>    strSql = "SELECT * FROM Summary WHERE [WeekEnding] ="
>>    strSql = strSql & "#"
>>    strSql = strSql & Format(Forms![P_Summ]![WeekEnding], "mm-dd-yyyy")
>>    strSql = strSql & "#"

>>        Debug.Print "strSql = "; Tab(10); strSql

>>    Set rst = dbs.OpenRecordset(strSql)

>>    rst.MoveFirst
>>        Debug.Print "Date = "; Tab(20);
>>    rst.Close
>>    Set rst = Nothing
>>......
>> the select statement give me the corret record that I want to look at.
>>In order to reference the values held in this recordset ( eg week date ,
>>production, sales etc.) how is that done.

>>I would want to pass the value of he recordset sales to a long Eg accsales
=

>>Rst.sales?? or in a form me!production = rst.production ??

>>Any Idear that pionts my nose to a solution is very much appreciated.

>>Thanks
>>Rene'



Fri, 27 Apr 2001 03:00:00 GMT  
 Referencing values from a record set
Using DAO 3.5 recordsets, there is no choice -- you must use bangs.
But because fields and controls of Forms are properties of the form
class, dots are fine for them.

Dots vs. Bangs really has nothing to do with what you name vs. what
Access names. It has to do with properties/methods vs. members of
collections. It is just a coincidence that things you name in Access
usually end up in collections.

 -- Andy


Quote:
>Its possible and works likewise, but not recomended, to reference using the
>sintax rst.FiledName. Its not recomended because properties and methods are
>"separeted" from the object using dots.
>It's a good practice to reference using rst!FieldName because the FieldName
>is something you named, so you will differentiate "your" objects from
>properties and methods.


>>You are close. In DAO 3.5, you need to refer to recordset fields as
>>members of the Fields collection of the recordset object, which is the
>>default collection. You cannot refer to them as properties of the
>>recordset. So any of the following will do:
>>MyValue = rst.Fields("FieldName")
>>MyValue = rst("FieldName")
>>MyValue = rst!FieldName
>>MyValue = rst(strFieldNameVariable)

>>On Access 97 forms, you can refer to fields in the recordsource as
>>Me.FieldName, because those fields (and also the controls on the form)
>>are implemented as properties of the form's class. But DAO doesn't
>>make the fields properties -- they are just members of the Fields
>>collection, so you need to use the one of the syntaxes above, which
>>are used to refer to members of a collection, not rst.FieldName.

>> -- Andy


>>>Hi there

>>>first time working with record set

>>>    strSql = "SELECT * FROM Summary WHERE [WeekEnding] ="
>>>    strSql = strSql & "#"
>>>    strSql = strSql & Format(Forms![P_Summ]![WeekEnding], "mm-dd-yyyy")
>>>    strSql = strSql & "#"

>>>        Debug.Print "strSql = "; Tab(10); strSql

>>>    Set rst = dbs.OpenRecordset(strSql)

>>>    rst.MoveFirst
>>>        Debug.Print "Date = "; Tab(20);
>>>    rst.Close
>>>    Set rst = Nothing
>>>......
>>> the select statement give me the corret record that I want to look at.
>>>In order to reference the values held in this recordset ( eg week date ,
>>>production, sales etc.) how is that done.

>>>I would want to pass the value of he recordset sales to a long Eg accsales
>=

>>>Rst.sales?? or in a form me!production = rst.production ??

>>>Any Idear that pionts my nose to a solution is very much appreciated.

>>>Thanks
>>>Rene'



Fri, 27 Apr 2001 03:00:00 GMT  
 Referencing values from a record set
Excel has a copyfromrecordset method that allows you to specify and
upper-left corner cell and automatically fills in the rest for
multiple rows and columns.

 -- Andy

Quote:

>Thanks
>2 minutes after sending this mail I found the answer

>Jet I like to take the question a step further.

>I managed to get all the references etc.
>let's say you want to pass the sales information for the month by weeknumber
>where
>the week number is between 1 and five and the dates are between 10/01/98 and
>10/30/98.
>That query gets you say 4 to 5 rows.
>how could I pass this 4 rows with all its 6 or 7 colums to and excel sheet?
>I know how to do it with one row. would I have to use recorset clone or copy
>to pass the information to an excel range?


>>Using DAO 3.5 recordsets, there is no choice -- you must use bangs.
>>But because fields and controls of Forms are properties of the form
>>class, dots are fine for them.

>>Dots vs. Bangs really has nothing to do with what you name vs. what
>>Access names. It has to do with properties/methods vs. members of
>>collections. It is just a coincidence that things you name in Access
>>usually end up in collections.



Sat, 28 Apr 2001 03:00:00 GMT  
 Referencing values from a record set
Thanks
2 minutes after sending this mail I found the answer

Jet I like to take the question a step further.

I managed to get all the references etc.
let's say you want to pass the sales information for the month by weeknumber
where
the week number is between 1 and five and the dates are between 10/01/98 and
10/30/98.
That query gets you say 4 to 5 rows.
how could I pass this 4 rows with all its 6 or 7 colums to and excel sheet?
I know how to do it with one row. would I have to use recorset clone or copy
to pass the information to an excel range?

Quote:

>Using DAO 3.5 recordsets, there is no choice -- you must use bangs.
>But because fields and controls of Forms are properties of the form
>class, dots are fine for them.

>Dots vs. Bangs really has nothing to do with what you name vs. what
>Access names. It has to do with properties/methods vs. members of
>collections. It is just a coincidence that things you name in Access
>usually end up in collections.



Sun, 29 Apr 2001 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. How to set default value of combo box to value of record in database

2. referencing value of specific record

3. Reference previous record field value

4. Record Set Guru's - Can't Create Record Set error 429

5. Record Set Guru's - Can't Create Record Set error 429

6. Newbe SQL value must be record set?

7. setting values in a record dynamically

8. Setting a variable to the largest record value.

9. Record position after setting a value and refreshing...

10. Setting Value to one subform record only

11. Ado: Howto set values in new record

12. How to set Constant Values to Control Code Values

 

 
Powered by phpBB® Forum Software