Problem with FindFirst Method

[j]

I am trying to run a find first method as follows:
p=1
Do
first.FindFirst "UniqueIdentifier = " & CStr(p)
custID = first!CustomerID
second.FindFirst "[CustomerIdentify] = '" & custID & "'"
customername = second!customername
loop while p<10

what this does, is it sorts through 10 records in one table (first) and for
each one tries to find a matching customer ID number in another table
(second) from which it draws the customer name. There are some instances
where a record in First will not be in Second. However, it seems to
arbitrarily assign it to the first record rather than give me an error of any
sort saying that findfirst fails. How do I identify cases where the findfirst
method does not find anything?
Thanks!

 

[Douglas J. Steele]

Do
first.FindFirst "UniqueIdentifier = " & CStr(p)
custID = first!CustomerID
second.FindFirst "[CustomerIdentify] = '" & custID & "'"
If Not second.NoMatch Then
customername = second!customername
End If
loop while p<10

Recognize, though, that it would be infinitely better to use an Update query
to do this, rather than playing with two recordsets like you are.

 

[j]

Can you explain a bit on how to do this using an update query? I am
unfamiliar with it but would really appreciate the knowlege! thanks!!

Do
first.FindFirst "UniqueIdentifier = " & CStr(p)
custID = first!CustomerID
second.FindFirst "[CustomerIdentify] = '" & custID & "'"
If Not second.NoMatch Then
customername = second!customername
End If
loop while p<10

Recognize, though, that it would be infinitely better to use an Update query
to do this, rather than playing with two recordsets like you are.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am trying to run a find first method as follows:
p=1
Do
first.FindFirst "UniqueIdentifier = " & CStr(p)
custID = first!CustomerID
second.FindFirst "[CustomerIdentify] = '" & custID & "'"
customername = second!customername
loop while p<10

what this does, is it sorts through 10 records in one table (first) and
for
each one tries to find a matching customer ID number in another table
(second) from which it draws the customer name. There are some instances
where a record in First will not be in Second. However, it seems to
arbitrarily assign it to the first record rather than give me an error of
any
sort saying that findfirst fails. How do I identify cases where the
findfirst
method does not find anything?
Thanks!

 

[j]

I should add that we are trying to look in one table, then find the record
with a matching id number in another table. Then we are taking the difference
between a "price" column, to see the price that they were quoted for a
particular service. If there is a difference in the price, it moves the data
(the two records and the difference) to a third table

Do
first.FindFirst "UniqueIdentifier = " & CStr(p)
custID = first!CustomerID
second.FindFirst "[CustomerIdentify] = '" & custID & "'"
If Not second.NoMatch Then
customername = second!customername
End If
loop while p<10

Recognize, though, that it would be infinitely better to use an Update query
to do this, rather than playing with two recordsets like you are.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am trying to run a find first method as follows:
p=1
Do
first.FindFirst "UniqueIdentifier = " & CStr(p)
custID = first!CustomerID
second.FindFirst "[CustomerIdentify] = '" & custID & "'"
customername = second!customername
loop while p<10

what this does, is it sorts through 10 records in one table (first) and
for
each one tries to find a matching customer ID number in another table
(second) from which it draws the customer name. There are some instances
where a record in First will not be in Second. However, it seems to
arbitrarily assign it to the first record rather than give me an error of
any
sort saying that findfirst fails. How do I identify cases where the
findfirst
method does not find anything?
Thanks!

 

[Douglas J. Steele]

Not really, without knowing more about your tables.

Here's an example from the Help file. Based on tables in the Northwind
Trading database that comes with Access, It reduces the UnitPrice for all
non-discontinued products supplied by Tokyo Traders by 5 percent. The
Products and Suppliers tables have a many-to-one relationship.

UPDATE Suppliers INNER JOIN Products
ON Suppliers.SupplierID = Products.SupplierID SET UnitPrice = UnitPrice *
..95
WHERE CompanyName = 'Tokyo Traders' AND Discontinued = No;

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Can you explain a bit on how to do this using an update query? I am
unfamiliar with it but would really appreciate the knowlege! thanks!!

Do
first.FindFirst "UniqueIdentifier = " & CStr(p)
custID = first!CustomerID
second.FindFirst "[CustomerIdentify] = '" & custID & "'"
If Not second.NoMatch Then
customername = second!customername
End If
loop while p<10

Recognize, though, that it would be infinitely better to use an Update
query
to do this, rather than playing with two recordsets like you are.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am trying to run a find first method as follows:
p=1
Do
first.FindFirst "UniqueIdentifier = " & CStr(p)
custID = first!CustomerID
second.FindFirst "[CustomerIdentify] = '" & custID & "'"
customername = second!customername
loop while p<10

what this does, is it sorts through 10 records in one table (first) and
for
each one tries to find a matching customer ID number in another table
(second) from which it draws the customer name. There are some
instances
where a record in First will not be in Second. However, it seems to
arbitrarily assign it to the first record rather than give me an error
of
any
sort saying that findfirst fails. How do I identify cases where the
findfirst
method does not find anything?
Thanks!