Problems w/ FindFirst

[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

 

[strive4peace]

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

*

 

[Rob Parker]

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

 

[strive4peace]

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]

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

Rob

 

[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

 

[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

 

[strive4peace]

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

*

 

[strive4peace]

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

*

 

[Rob Parker]

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

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>