[AccessD] FindFirst

Hi Chester

What is the name of the fields in these two recordsets?  I feel it would be 
simpler if you could refer to them directly.  For example, if the name of 
the field is Meter in both recordsets, then your code will be:
RS8.FindFirst "Meter = " & RS9!Meter

By the way, in addition to Lambert's comments, note that this construct is 
going to position you at the RS8 record where the value of the nominated 
field matches that of the *first* record in RS9.  Is that what you are 
seeking?

Regards
Steve

-----Original Message----- 
From: Kaup, Chester
Sent: Friday, December 30, 2011 8:18 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] FindFirst

Thanks for taking the time to give me some really good info. I am still 
having trouble though. I get an Unknown Function Error on the line

RS8.FindFirst "RS8.Fields(1)=" & RS9.Fields(1)

Both RS8.Fields(1) and RS9.Fields(1) are number fields

I then tried the following. Meter1 and Meter2 are both dimmed as single

Meter1 = RS8.Fields(1)
Meter2 = RS9.Fields(1)
RS8.FindFirst "Meter1=" & Meter2

This gives the error
The Microsoft Access database engine does not recognize "Meter1" as a valid 
field name or expression.

Thanks for the help.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Thursday, December 29, 2011 11:56 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] FindFirst

Chester,

Your criteria string used by FindFirst is the problem.

But first some comments about referencing fields in a recordset.

There are several ways (i.e. several valid syntaxes) to reference a specific 
field in a recordset (which can point to a table or a query)

1/ By the ordinal value of the field, i.e. its position in the list of 
fields in the recordset. Your use of the expression  "RS8.Fields(1)" is an 
example. It resolves to the value of the first field in the recordset. The 
big disadvantage of this syntax is that it is not "reader friendly". When 
reading the code you (or anyone else) would probably have to go back to the 
table/query to find out what Fields(1) actually is.

2/ By using the field name as an index to the fields collection of the 
recordset.
e.g. RS8("SomeFieldName")
   This is more readable as the field name in the quotes tells you what data 
is being referenced.

3/ BY directly using the Recordset and field names.
e.g. [RS8]![SomeFieldName]
   The square brackets are optional if the object names do not have spaces, 
but I always use them.

I personally prefer to use method 3 as it is then possible in code blocks to 
use the With / End With construct, which allows you to skip the recordset 
name in multiple statements...

With RS8
![SomeFieldName] = AValue
![AnotherField] = AnotherValue
.FindFirst "SomeOtherField=" & Avalue
If Not .NoMatch Then ' We found it
' Do something
End If
... Etc.
End With

So, returning to your question, how to use FindFirst.

"RS8.Fields(1)=RS9.Fields(1)" does not work as a criteria string because it 
is literally saying: "find the first record where the field(1) value is 
equal to the literal string "RS9.Fields(1)", but you actually want to find 
the record where the *value* of RS8.Fields(1) is equal to the *value* of 
RS8.Fields(1).

If you change the criteria string to this...

RS8.FindFirst "RS8.Fields(1)=" & RS9.Fields(1)

Then it will work, providing Fields(1) is not a text or a date value.

The above syntax works for other data types, but for TEXT you have to 
enclose the value you are searching for in quotes. Either a combination of 
single and double quotes like this...

"RS8.Fields(1)='" & RS9.Fields(1) & "'"

Or all double quotes like this....

"RS8.Fields(1)="""" & RS9.Fields(1) & """"

Those are a little difficult to read, especially seeing "'" for what it is 
with a proportional font. So I use a little function to wrap the text in 
quotes...

Function Quote(aString) As String
    Quote = """" & aString & """"
End Function

And using that I can then write...

"RS8.Fields(1)=" & Quote(RS9.Fields(1))

Or my preferred syntax

"[RS8]![SomeFieldName]=" & Quote([RS9]![SomeOtherField]

Searching for date values is similar, but a different delimiter is used, the 
# sign. So if RS9.Field(1) was a date value, the search criteria would have 
to be

"RS8.Fields(1)=#" & RS9.Fields(1) & "#"

HTH

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Thursday, December 29, 2011 12:27 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] FindFirst

This is my first try using this command and I need some help. Here is what I 
have. Does it work only on tables? Thanks.

Set RS8 = MyDb.OpenRecordset("qry Produced Gas CO2 Analysis")

Set RS9 = MyDb.OpenRecordset(strSQL)

RS8.FindFirst "RS8.Fields(1)=RS9.Fields(1)"



Chester Kaup

Engineering Technician

Kinder Morgan CO2 Company, LLP

Office (432) 688-3797

FAX (432) 688-3799





No trees were killed in the sending of this message. However a large number 
of electrons were terribly inconvenienced.

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com