Welcome to SPN

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

Sign Up Now!

Problems w/ FindFirst

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

  1. Sharkbyte

    Sharkbyte
    Expand Collapse
    Guest

    The code below resides in a loop. It works fine, for the first record, but
    none of the others. The 'Findfirst' line searches for the proper ItemID,
    however, it continues to attempt to update the first record of the table.

    Any suggestions on what I'm doing wrong?

    Thanks, in advance.

    Sharkbyte


    Dim MyDB As Database
    Dim MyDetail As Recordset

    Set MyDB = CurrentDb()
    Set MyDetail = MyDB.OpenRecordset("tblpurchaseorderdetails")

    ..MoveFirst
    ..FindFirst ("itemid = '" & gblItemID & "' ")

    With MyDetail
    .Edit
    !ItemStatus = "Received"
    !QuantityReceived = !QuantityOrdered
    .Update
    End With
     
  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
    Muslim Girl-Sikh Boy Marriage Problems- Please Help! Love & Marriage Oct 12, 2012
    Problems with Sikh Historiography-prof Jagdish Singh Sikh Video Aug 31, 2012

  3. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Hi Sharkbyte,

    Why not use an UPDATE query...

    '~~~~~~~~~~~~~~~~~~~~
    dim strSQL as string
    strSQL = "UPDATE tblpurchaseorderdetails " _
    & " SET ItemStatus = 'Received', " _
    & " QuantityReceived = nz(QuantityOrdered) " _
    & " WHERE itemid '" & gblItemID & "';"
    debug.print strSql
    currentdb.execute strSQL
    '~~~~~~~~~~~~~~~~~~~~

    ** debug.print ***

    debug.print strSQL

    --> this prints a copy of the SQL statement to the debug
    window (CTRL-G)

    After you execute your code, open the Debug window
    CTRL-G to Goto the debuG window -- look at the SQL statement

    If the SQL statement has an error

    1. Make a new query (design view)

    2. choose View, SQL from the menu
    (or SQL from the toolbar, first icon)

    3. cut the SQL statement from the debug window
    (select, CTRL-X)

    4. paste into the SQL window of the Query
    (CTRL-V)

    5. run ! from the SQL window
    -- Access will tell you where the problem is in the SQL


    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    Sharkbyte wrote:
    > The code below resides in a loop. It works fine, for the first record, but
    > none of the others. The 'Findfirst' line searches for the proper ItemID,
    > however, it continues to attempt to update the first record of the table.
    >
    > Any suggestions on what I'm doing wrong?
    >
    > Thanks, in advance.
    >
    > Sharkbyte
    >
    >
    > Dim MyDB As Database
    > Dim MyDetail As Recordset
    >
    > Set MyDB = CurrentDb()
    > Set MyDetail = MyDB.OpenRecordset("tblpurchaseorderdetails")
    >
    > .MoveFirst
    > .FindFirst ("itemid = '" & gblItemID & "' ")
    >
    > With MyDetail
    > .Edit
    > !ItemStatus = "Received"
    > !QuantityReceived = !QuantityOrdered
    > .Update
    > End With
     
  4. Rob Parker

    Rob Parker
    Expand Collapse
    Guest

    Well, even if that code is within a loop that you haven't shown, it will
    not work because you are always telling it to .FindFirst ;-)

    Your Dim and Set statements belong outside your loop, and you need to use a
    ..FindNext within the loop. Something like:

    Dim MyDB As Database
    Dim MyDetail As DAO.Recordset 'disambiguate the recordset type

    Set MyDB = CurrentDb()
    Set MyDetail = MyDB.OpenRecordset("tblpurchaseorderdetails")

    ..MoveFirst

    Do While Not MyDetail.EOF
    .FindNext ("itemid = '" & gblItemID & "' ")
    With MyDetail
    .Edit
    !ItemStatus = "Received"
    !QuantityReceived = !QuantityOrdered
    .Update
    End With
    Loop

    Having said that, I'd actually build and execute an SQL string to do your
    update, as Crystal suggests. My reply is merely to assist you in
    understanding how to set up a loop that works ;-)

    HTH,

    Rob


    "Sharkbyte" <sharkbyte1@hotmail.com-> wrote in message
    news:EFA8EF96-1E17-46AF-A750-8D7ACA0B6199@microsoft.com...
    > The code below resides in a loop. It works fine, for the first record,
    > but
    > none of the others. The 'Findfirst' line searches for the proper ItemID,
    > however, it continues to attempt to update the first record of the table.
    >
    > Any suggestions on what I'm doing wrong?
    >
    > Thanks, in advance.
    >
    > Sharkbyte
    >
    >
    > Dim MyDB As Database
    > Dim MyDetail As Recordset
    >
    > Set MyDB = CurrentDb()
    > Set MyDetail = MyDB.OpenRecordset("tblpurchaseorderdetails")
    >
    > .MoveFirst
    > .FindFirst ("itemid = '" & gblItemID & "' ")
    >
    > With MyDetail
    > .Edit
    > !ItemStatus = "Received"
    > !QuantityReceived = !QuantityOrdered
    > .Update
    > End With
     
  5. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Re: Problems w/ FindFirst -- FindFirst before loop, FindNext inloop

    Thanks, Rob :)

    It is a good idea to help Sharkbyte understand the logic
    errors instead of just suggesting a different method, thanks
    for that

    just have a couple comments on the code that Rob posted ...

    in case the FIRST record contains what is being sought... do
    ..FindFirst before the loop (do not believe that .FindNext
    will stay on the same record...)

    Object variables need to be released when you are done with them

    '~~~~~~~~~~~~~~~~~~~~~~~~~~
    'NEEDS REFERENCE TO
    'Microsoft DAO Object Library

    Dim MyDB As DAO.Database
    Dim MyDetail As DAO.Recordset

    Set MyDB = CurrentDb()
    Set MyDetail = MyDB.OpenRecordset("tblpurchaseorderdetails")

    With MyDetail

    .FindFirst "itemid = '" & gblItemID & "'"

    if not MyDetail.NoMatch then
    Do
    .Edit
    !ItemStatus = "Received"
    !QuantityReceived = !QuantityOrdered
    .Update
    .FindNext "itemid = '" & gblItemID & "'"
    Loop WHILE not MyDetail.NoMatch
    end if

    end with

    'close/release object variables
    MyDetail.close
    set MyDetail = nothing
    set MyDB = nothing
    '~~~~~~~~~~~~~~~~~~~~~~~~~~

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    Rob Parker wrote:
    > Well, even if that code is within a loop that you haven't shown, it will
    > not work because you are always telling it to .FindFirst ;-)
    >
    > Your Dim and Set statements belong outside your loop, and you need to use a
    > .FindNext within the loop. Something like:
    >
    > Dim MyDB As Database
    > Dim MyDetail As DAO.Recordset 'disambiguate the recordset type
    >
    > Set MyDB = CurrentDb()
    > Set MyDetail = MyDB.OpenRecordset("tblpurchaseorderdetails")
    >
    > .MoveFirst
    >
    > Do While Not MyDetail.EOF
    > .FindNext ("itemid = '" & gblItemID & "' ")
    > With MyDetail
    > .Edit
    > !ItemStatus = "Received"
    > !QuantityReceived = !QuantityOrdered
    > .Update
    > End With
    > Loop
    >
    > Having said that, I'd actually build and execute an SQL string to do your
    > update, as Crystal suggests. My reply is merely to assist you in
    > understanding how to set up a loop that works ;-)
    >
    > HTH,
    >
    > Rob
    >
    >
    > "Sharkbyte" <sharkbyte1@hotmail.com-> wrote in message
    > news:EFA8EF96-1E17-46AF-A750-8D7ACA0B6199@microsoft.com...
    >
    >>The code below resides in a loop. It works fine, for the first record,
    >>but
    >>none of the others. The 'Findfirst' line searches for the proper ItemID,
    >>however, it continues to attempt to update the first record of the table.
    >>
    >>Any suggestions on what I'm doing wrong?
    >>
    >>Thanks, in advance.
    >>
    >>Sharkbyte
    >>
    >>
    >>Dim MyDB As Database
    >>Dim MyDetail As Recordset
    >>
    >>Set MyDB = CurrentDb()
    >>Set MyDetail = MyDB.OpenRecordset("tblpurchaseorderdetails")
    >>
    >>.MoveFirst
    >>.FindFirst ("itemid = '" & gblItemID & "' ")
    >>
    >>With MyDetail
    >> .Edit
    >> !ItemStatus = "Received"
    >> !QuantityReceived = !QuantityOrdered
    >> .Update
    >>End With

    >
    >
    >
     
  6. Rob Parker

    Rob Parker
    Expand Collapse
    Guest

    Re: Problems w/ FindFirst -- FindFirst before loop, FindNext in loop

    Thanks for the minor amendments to my air-code example, Crystal.

    Rob

    "strive4peace" <"strive4peace2006 at yahoo dot com"> wrote in message
    news:OkJHMjogGHA.3996@TK2MSFTNGP03.phx.gbl...
    > Thanks, Rob :)
    >
    > It is a good idea to help Sharkbyte understand the logic errors instead of
    > just suggesting a different method, thanks for that
    >
    > just have a couple comments on the code that Rob posted ...
    >
    > in case the FIRST record contains what is being sought... do .FindFirst
    > before the loop (do not believe that .FindNext will stay on the same
    > record...)
    >
    > Object variables need to be released when you are done with them
    >
    > '~~~~~~~~~~~~~~~~~~~~~~~~~~
    > 'NEEDS REFERENCE TO
    > 'Microsoft DAO Object Library
    >
    > Dim MyDB As DAO.Database
    > Dim MyDetail As DAO.Recordset
    >
    > Set MyDB = CurrentDb()
    > Set MyDetail = MyDB.OpenRecordset("tblpurchaseorderdetails")
    >
    > With MyDetail
    >
    > .FindFirst "itemid = '" & gblItemID & "'"
    >
    > if not MyDetail.NoMatch then
    > Do
    > .Edit
    > !ItemStatus = "Received"
    > !QuantityReceived = !QuantityOrdered
    > .Update
    > .FindNext "itemid = '" & gblItemID & "'"
    > Loop WHILE not MyDetail.NoMatch
    > end if
    >
    > end with
    >
    > 'close/release object variables
    > MyDetail.close
    > set MyDetail = nothing
    > set MyDB = nothing
    > '~~~~~~~~~~~~~~~~~~~~~~~~~~
    >
    > Warm Regards,
    > Crystal
    > Microsoft Access MVP 2006
    >
    > *
    > Have an awesome day ;)
    >
    > remote programming and training
    > strive4peace2006 at yahoo.com
    >
    > *
    >
    > Rob Parker wrote:
    >> Well, even if that code is within a loop that you haven't shown, it will
    >> not work because you are always telling it to .FindFirst ;-)
    >>
    >> Your Dim and Set statements belong outside your loop, and you need to use
    >> a .FindNext within the loop. Something like:
    >>
    >> Dim MyDB As Database
    >> Dim MyDetail As DAO.Recordset 'disambiguate the recordset type
    >>
    >> Set MyDB = CurrentDb()
    >> Set MyDetail = MyDB.OpenRecordset("tblpurchaseorderdetails")
    >>
    >> .MoveFirst
    >>
    >> Do While Not MyDetail.EOF
    >> .FindNext ("itemid = '" & gblItemID & "' ")
    >> With MyDetail
    >> .Edit
    >> !ItemStatus = "Received"
    >> !QuantityReceived = !QuantityOrdered
    >> .Update
    >> End With
    >> Loop
    >>
    >> Having said that, I'd actually build and execute an SQL string to do your
    >> update, as Crystal suggests. My reply is merely to assist you in
    >> understanding how to set up a loop that works ;-)
    >>
    >> HTH,
    >>
    >> Rob
    >>
    >>
    >> "Sharkbyte" <sharkbyte1@hotmail.com-> wrote in message
    >> news:EFA8EF96-1E17-46AF-A750-8D7ACA0B6199@microsoft.com...
    >>
    >>>The code below resides in a loop. It works fine, for the first record,
    >>>but
    >>>none of the others. The 'Findfirst' line searches for the proper ItemID,
    >>>however, it continues to attempt to update the first record of the table.
    >>>
    >>>Any suggestions on what I'm doing wrong?
    >>>
    >>>Thanks, in advance.
    >>>
    >>>Sharkbyte
    >>>
    >>>
    >>>Dim MyDB As Database
    >>>Dim MyDetail As Recordset
    >>>
    >>>Set MyDB = CurrentDb()
    >>>Set MyDetail = MyDB.OpenRecordset("tblpurchaseorderdetails")
    >>>
    >>>.MoveFirst
    >>>.FindFirst ("itemid = '" & gblItemID & "' ")
    >>>
    >>>With MyDetail
    >>> .Edit
    >>> !ItemStatus = "Received"
    >>> !QuantityReceived = !QuantityOrdered
    >>> .Update
    >>>End With

    >>
    >>
     
  7. Sharkbyte

    Sharkbyte
    Expand Collapse
    Guest

    I actually began with an Update query. I went this way because I appeared to
    be having problems with not being able to requery a subform because the
    transaction hadn't completed.

    But thanks for the help.

    Sharkbyte


    "strive4peace" <"strive4peace2006 at yaho" wrote:

    > Hi Sharkbyte,
    >
    > Why not use an UPDATE query...
    >
    > '~~~~~~~~~~~~~~~~~~~~
    > dim strSQL as string
    > strSQL = "UPDATE tblpurchaseorderdetails " _
    > & " SET ItemStatus = 'Received', " _
    > & " QuantityReceived = nz(QuantityOrdered) " _
    > & " WHERE itemid '" & gblItemID & "';"
    > debug.print strSql
    > currentdb.execute strSQL
    > '~~~~~~~~~~~~~~~~~~~~
    >
    > ** debug.print ***
    >
    > debug.print strSQL
    >
    > --> this prints a copy of the SQL statement to the debug
    > window (CTRL-G)
    >
    > After you execute your code, open the Debug window
    > CTRL-G to Goto the debuG window -- look at the SQL statement
    >
    > If the SQL statement has an error
    >
    > 1. Make a new query (design view)
    >
    > 2. choose View, SQL from the menu
    > (or SQL from the toolbar, first icon)
    >
    > 3. cut the SQL statement from the debug window
    > (select, CTRL-X)
    >
    > 4. paste into the SQL window of the Query
    > (CTRL-V)
    >
    > 5. run ! from the SQL window
    > -- Access will tell you where the problem is in the SQL
    >
    >
    > Warm Regards,
    > Crystal
    > Microsoft Access MVP 2006
    >
    > *
    > Have an awesome day ;)
    >
    > remote programming and training
    > strive4peace2006 at yahoo.com
    >
    > *
    >
    > Sharkbyte wrote:
    > > The code below resides in a loop. It works fine, for the first record, but
    > > none of the others. The 'Findfirst' line searches for the proper ItemID,
    > > however, it continues to attempt to update the first record of the table.
    > >
    > > Any suggestions on what I'm doing wrong?
    > >
    > > Thanks, in advance.
    > >
    > > Sharkbyte
    > >
    > >
    > > Dim MyDB As Database
    > > Dim MyDetail As Recordset
    > >
    > > Set MyDB = CurrentDb()
    > > Set MyDetail = MyDB.OpenRecordset("tblpurchaseorderdetails")
    > >
    > > .MoveFirst
    > > .FindFirst ("itemid = '" & gblItemID & "' ")
    > >
    > > With MyDetail
    > > .Edit
    > > !ItemStatus = "Received"
    > > !QuantityReceived = !QuantityOrdered
    > > .Update
    > > End With

    >
     
  8. Sharkbyte

    Sharkbyte
    Expand Collapse
    Guest

    Thanks for the lesson. It was my first time using this method. I have gone
    back to the Update query, but will keep your tips in mind.

    Sharkbyte



    "Rob Parker" wrote:

    > Well, even if that code is within a loop that you haven't shown, it will
    > not work because you are always telling it to .FindFirst ;-)
    >
    > Your Dim and Set statements belong outside your loop, and you need to use a
    > ..FindNext within the loop. Something like:
    >
    > Dim MyDB As Database
    > Dim MyDetail As DAO.Recordset 'disambiguate the recordset type
    >
    > Set MyDB = CurrentDb()
    > Set MyDetail = MyDB.OpenRecordset("tblpurchaseorderdetails")
    >
    > ..MoveFirst
    >
    > Do While Not MyDetail.EOF
    > .FindNext ("itemid = '" & gblItemID & "' ")
    > With MyDetail
    > .Edit
    > !ItemStatus = "Received"
    > !QuantityReceived = !QuantityOrdered
    > .Update
    > End With
    > Loop
    >
    > Having said that, I'd actually build and execute an SQL string to do your
    > update, as Crystal suggests. My reply is merely to assist you in
    > understanding how to set up a loop that works ;-)
    >
    > HTH,
    >
    > Rob
    >
    >
    > "Sharkbyte" <sharkbyte1@hotmail.com-> wrote in message
    > news:EFA8EF96-1E17-46AF-A750-8D7ACA0B6199@microsoft.com...
    > > The code below resides in a loop. It works fine, for the first record,
    > > but
    > > none of the others. The 'Findfirst' line searches for the proper ItemID,
    > > however, it continues to attempt to update the first record of the table.
    > >
    > > Any suggestions on what I'm doing wrong?
    > >
    > > Thanks, in advance.
    > >
    > > Sharkbyte
    > >
    > >
    > > Dim MyDB As Database
    > > Dim MyDetail As Recordset
    > >
    > > Set MyDB = CurrentDb()
    > > Set MyDetail = MyDB.OpenRecordset("tblpurchaseorderdetails")
    > >
    > > .MoveFirst
    > > .FindFirst ("itemid = '" & gblItemID & "' ")
    > >
    > > With MyDetail
    > > .Edit
    > > !ItemStatus = "Received"
    > > !QuantityReceived = !QuantityOrdered
    > > .Update
    > > End With

    >
    >
    >
     
  9. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Re: Problems w/ FindFirst -- FindFirst before loop, FindNext inloop

    Hi Rob,

    you're welcome ;) We are all in this together seeking a
    common goal :)

    glad you were not offended by my "air-comments"! I
    appreciated your add-on to what I wrote and to help
    Sharkbyte understand why his code didn't work. Have a safe
    and happy holiday, Rob.

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    Rob Parker wrote:
    > Thanks for the minor amendments to my air-code example, Crystal.
    >
    > Rob
    >
    > "strive4peace" <"strive4peace2006 at yahoo dot com"> wrote in message
    > news:OkJHMjogGHA.3996@TK2MSFTNGP03.phx.gbl...
    >
    >>Thanks, Rob :)
    >>
    >>It is a good idea to help Sharkbyte understand the logic errors instead of
    >>just suggesting a different method, thanks for that
    >>
    >>just have a couple comments on the code that Rob posted ...
    >>
    >>in case the FIRST record contains what is being sought... do .FindFirst
    >>before the loop (do not believe that .FindNext will stay on the same
    >>record...)
    >>
    >>Object variables need to be released when you are done with them
    >>
    >>'~~~~~~~~~~~~~~~~~~~~~~~~~~
    >>'NEEDS REFERENCE TO
    >>'Microsoft DAO Object Library
    >>
    >>Dim MyDB As DAO.Database
    >>Dim MyDetail As DAO.Recordset
    >>
    >>Set MyDB = CurrentDb()
    >>Set MyDetail = MyDB.OpenRecordset("tblpurchaseorderdetails")
    >>
    >>With MyDetail
    >>
    >> .FindFirst "itemid = '" & gblItemID & "'"
    >>
    >> if not MyDetail.NoMatch then
    >> Do
    >> .Edit
    >> !ItemStatus = "Received"
    >> !QuantityReceived = !QuantityOrdered
    >> .Update
    >> .FindNext "itemid = '" & gblItemID & "'"
    >> Loop WHILE not MyDetail.NoMatch
    >> end if
    >>
    >>end with
    >>
    >>'close/release object variables
    >>MyDetail.close
    >>set MyDetail = nothing
    >>set MyDB = nothing
    >>'~~~~~~~~~~~~~~~~~~~~~~~~~~
    >>
    >>Warm Regards,
    >>Crystal
    >>Microsoft Access MVP 2006
    >>
    >> *
    >> Have an awesome day ;)
    >>
    >> remote programming and training
    >> strive4peace2006 at yahoo.com
    >>
    >> *
    >>
    >>Rob Parker wrote:
    >>
    >>>Well, even if that code is within a loop that you haven't shown, it will
    >>>not work because you are always telling it to .FindFirst ;-)
    >>>
    >>>Your Dim and Set statements belong outside your loop, and you need to use
    >>>a .FindNext within the loop. Something like:
    >>>
    >>>Dim MyDB As Database
    >>>Dim MyDetail As DAO.Recordset 'disambiguate the recordset type
    >>>
    >>>Set MyDB = CurrentDb()
    >>>Set MyDetail = MyDB.OpenRecordset("tblpurchaseorderdetails")
    >>>
    >>>.MoveFirst
    >>>
    >>>Do While Not MyDetail.EOF
    >>> .FindNext ("itemid = '" & gblItemID & "' ")
    >>> With MyDetail
    >>> .Edit
    >>> !ItemStatus = "Received"
    >>> !QuantityReceived = !QuantityOrdered
    >>> .Update
    >>> End With
    >>>Loop
    >>>
    >>>Having said that, I'd actually build and execute an SQL string to do your
    >>>update, as Crystal suggests. My reply is merely to assist you in
    >>>understanding how to set up a loop that works ;-)
    >>>
    >>>HTH,
    >>>
    >>>Rob
    >>>
    >>>
    >>>"Sharkbyte" <sharkbyte1@hotmail.com-> wrote in message
    >>>news:EFA8EF96-1E17-46AF-A750-8D7ACA0B6199@microsoft.com...
    >>>
    >>>
    >>>>The code below resides in a loop. It works fine, for the first record,
    >>>>but
    >>>>none of the others. The 'Findfirst' line searches for the proper ItemID,
    >>>>however, it continues to attempt to update the first record of the table.
    >>>>
    >>>>Any suggestions on what I'm doing wrong?
    >>>>
    >>>>Thanks, in advance.
    >>>>
    >>>>Sharkbyte
    >>>>
    >>>>
    >>>>Dim MyDB As Database
    >>>>Dim MyDetail As Recordset
    >>>>
    >>>>Set MyDB = CurrentDb()
    >>>>Set MyDetail = MyDB.OpenRecordset("tblpurchaseorderdetails")
    >>>>
    >>>>.MoveFirst
    >>>>.FindFirst ("itemid = '" & gblItemID & "' ")
    >>>>
    >>>>With MyDetail
    >>>> .Edit
    >>>> !ItemStatus = "Received"
    >>>> !QuantityReceived = !QuantityOrdered
    >>>> .Update
    >>>>End With
    >>>
    >>>

    >
     
  10. strive4peace

    strive4peace
    Expand Collapse
    Guest

    you welcome, Sharkbyte ;) happy to help

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    Sharkbyte wrote:
    > Thanks for the lesson. It was my first time using this method. I have gone
    > back to the Update query, but will keep your tips in mind.
    >
    > Sharkbyte
    >
    >
    >
    > "Rob Parker" wrote:
    >
    >
    >>Well, even if that code is within a loop that you haven't shown, it will
    >>not work because you are always telling it to .FindFirst ;-)
    >>
    >>Your Dim and Set statements belong outside your loop, and you need to use a
    >>..FindNext within the loop. Something like:
    >>
    >>Dim MyDB As Database
    >>Dim MyDetail As DAO.Recordset 'disambiguate the recordset type
    >>
    >>Set MyDB = CurrentDb()
    >>Set MyDetail = MyDB.OpenRecordset("tblpurchaseorderdetails")
    >>
    >>..MoveFirst
    >>
    >>Do While Not MyDetail.EOF
    >> .FindNext ("itemid = '" & gblItemID & "' ")
    >> With MyDetail
    >> .Edit
    >> !ItemStatus = "Received"
    >> !QuantityReceived = !QuantityOrdered
    >> .Update
    >> End With
    >>Loop
    >>
    >>Having said that, I'd actually build and execute an SQL string to do your
    >>update, as Crystal suggests. My reply is merely to assist you in
    >>understanding how to set up a loop that works ;-)
    >>
    >>HTH,
    >>
    >>Rob
    >>
    >>
    >>"Sharkbyte" <sharkbyte1@hotmail.com-> wrote in message
    >>news:EFA8EF96-1E17-46AF-A750-8D7ACA0B6199@microsoft.com...
    >>
    >>>The code below resides in a loop. It works fine, for the first record,
    >>>but
    >>>none of the others. The 'Findfirst' line searches for the proper ItemID,
    >>>however, it continues to attempt to update the first record of the table.
    >>>
    >>>Any suggestions on what I'm doing wrong?
    >>>
    >>>Thanks, in advance.
    >>>
    >>>Sharkbyte
    >>>
    >>>
    >>>Dim MyDB As Database
    >>>Dim MyDetail As Recordset
    >>>
    >>>Set MyDB = CurrentDb()
    >>>Set MyDetail = MyDB.OpenRecordset("tblpurchaseorderdetails")
    >>>
    >>>.MoveFirst
    >>>.FindFirst ("itemid = '" & gblItemID & "' ")
    >>>
    >>>With MyDetail
    >>> .Edit
    >>> !ItemStatus = "Received"
    >>> !QuantityReceived = !QuantityOrdered
    >>> .Update
    >>>End With

    >>
    >>
    >>
     
  11. Rob Parker

    Rob Parker
    Expand Collapse
    Guest

    Re: Problems w/ FindFirst -- FindFirst before loop, FindNext in loop

    Hi Crystal,

    I've got no problem with anyone adding to /expanding/correcting (hopefully
    there's not too much of the last one - I try to only post when I know what
    I'm talking about) any of my answers. And your right about the common
    goal - there's very few posts in these groups that aren't like that; the
    exceptions are from the lost souls posting in error, and the advertiser and
    his detractors ;-)

    Sadly, there's no holiday on the books here in Melbourne, Australia, at
    present :-(

    Regards,

    Rob


    "strive4peace" <"strive4peace2006 at yahoo dot com"> wrote in message
    news:O0NyB1sgGHA.2208@TK2MSFTNGP05.phx.gbl...
    > Hi Rob,
    >
    > you're welcome ;) We are all in this together seeking a common goal :)
    >
    > glad you were not offended by my "air-comments"! I appreciated your
    > add-on to what I wrote and to help Sharkbyte understand why his code
    > didn't work. Have a safe and happy holiday, Rob.
    >
    > Warm Regards,
    > Crystal
    > Microsoft Access MVP 2006
    >

    <snip>
     

Share This Page