Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Problem with FindFirst Method

Discussion in 'Information Technology' started by j, Jul 28, 2006.

  1. j

    j
    Expand Collapse
    Guest

    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!
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikhs Face Problems While Carrying Kirpans In Khyber-Pakhtunkhwa - SikhSiyasat.Net Breaking News Jul 29, 2016
    "Dasam" Granth - A Look At The Core Problems Dasam Granth Oct 21, 2015
    Problems In Life Sikh Sikhi Sikhism Apr 13, 2015
    How can I make decent and useful friends and from where ?? most people have a problem with me Sikh Youth Oct 23, 2013
    The Problem with Taking Too Many Vitamins Health & Nutrition Oct 18, 2013

  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    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
    http://I.Am/DougSteele
    (no private e-mails, please)


    "j" <j@discussions.microsoft.com> wrote in message
    news:2A9DC5A0-C35D-4D34-9575-4603D10AE298@microsoft.com...
    >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!
     
  4. j

    j
    Expand Collapse
    Guest

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

    "Douglas J. Steele" wrote:

    > 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
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "j" <j@discussions.microsoft.com> wrote in message
    > news:2A9DC5A0-C35D-4D34-9575-4603D10AE298@microsoft.com...
    > >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!

    >
    >
    >
     
  5. j

    j
    Expand Collapse
    Guest

    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

    "Douglas J. Steele" wrote:

    > 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
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "j" <j@discussions.microsoft.com> wrote in message
    > news:2A9DC5A0-C35D-4D34-9575-4603D10AE298@microsoft.com...
    > >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!

    >
    >
    >
     
  6. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    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
    http://I.Am/DougSteele
    (no private e-mails, please)


    "j" <j@discussions.microsoft.com> wrote in message
    news:06DD6FBF-B47F-4554-A65D-46C74B8CB2F7@microsoft.com...
    > 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!!
    >
    > "Douglas J. Steele" wrote:
    >
    >> 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
    >> http://I.Am/DougSteele
    >> (no private e-mails, please)
    >>
    >>
    >> "j" <j@discussions.microsoft.com> wrote in message
    >> news:2A9DC5A0-C35D-4D34-9575-4603D10AE298@microsoft.com...
    >> >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!

    >>
    >>
    >>
     

Share This Page