[AccessD] FindFirst

Steve Schapel steve at datamanagementsolutions.biz
Thu Dec 29 14:19:09 CST 2011


Hi Chester

Ok.  Drop the "[RS8]", and the brackets around RS9...

RS8.FindFirst "[Meter_Id]=" & RS9![Meter_Id]

Regards
Steve

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

Using actual field names here is what it looks like

RS8.FindFirst "[RS8]![Meter_Id]=" & [RS9]![Meter_Id]

The goal is to find the first record in RS8 that matches the current record 
in RS9.

Thanks for the help.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Steve Schapel
Sent: Thursday, December 29, 2011 1:37 PM
To: Access Developers discussion and problem solving
Subject: Re: [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

-- 
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 



More information about the AccessD mailing list