[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