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