i have opened a recordset with "select subkey, psupp, A1 from prices"



now i need to find if a record exists that matches the subkey and psupp 

variables.



it should sound something like this,



data2.recordset.findfirst subkey where psupp is same as sPart



i used the code below, which was ok if there was only one record with 

that subkey



Data2.Recordset.FindFirst "subkey = '" & sPart & "'"



my aim is to update a price for a subkey where the supplier matches



any help would be appreciated.

Try this:

Data2.recordset.MoveFirst

Do Until Data2.recordset.EOF

   Data2.Recordset.FindNext "subkey = '" & sPart & "'"

   'Write your code here to do with the record whatever you want

Loop



The loop will end when you reach the End Of File



Of course if you are trying to change one column based on another you

are probably better off with an Update query:

"Update Price = Price & 1.05 WHERE subkey = " & sPart

This would cause a 5% increase where the match exists.





>i have opened a recordset with "select subkey, psupp, A1 from prices"

now i need to find if a record exists that matches the subkey and psupp 

variables.

it should sound something like this,

data2.recordset.findfirst subkey where psupp is same as sPart

i used the code below, which was ok if there was only one record with 

that subkey

Data2.Recordset.FindFirst "subkey = '" & sPart & "'"

my aim is to update a price for a subkey where the supplier matches

any help would be appreciated.<



Good luck,

Richard Lobel

Accessible Data

richard@a... <mailto:richard@a...> 

Cell:  (xxx) xxx-xxxx

Fax:  (xxx) xxx-xxxx

I'm guessing that since you're using FindFirst, this is a DAO recordset.

That being the case, you can search on multiple columns with FindFirst

(compare ADO recordset's Find method, which is limited to working with one

column--I've got a callous on my forehead from discovering that out the hard

way).



So... something like:



With Data2.Recordset

   .FindFirst "subkey = '" & sPart & "' AND psupp = '" & sPart & "'"

   If .NoMatch Then

      ' do nothing?

   Else

      ' do your update

   End If

End With





Should work.



That said, Richard's advice for using SQL is probably the best

approach--it'll run faster & is easier to read & maintain.



HTH,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

(xxx) xxx-xxxx



-----Original Message-----

From: chris angus [mailto:chris@a...]

Sent: Monday, October 22, 2001 5:30 PM

To: Access

Subject: [access] can anyone help translate this line into code





i have opened a recordset with "select subkey, psupp, A1 from prices"



now i need to find if a record exists that matches the subkey and psupp 

variables.



it should sound something like this,



data2.recordset.findfirst subkey where psupp is same as sPart



i used the code below, which was ok if there was only one record with 

that subkey



Data2.Recordset.FindFirst "subkey = '" & sPart & "'"



my aim is to update a price for a subkey where the supplier matches



any help would be appreciated.