Database Access Problem

[AcornRanch]

I designed an inventory program to keep track of parts out of a database. I had it working great and used this code to call up the part when the item number box lost focus. Now every time I put in a number the program stops. I changed some of the references that are in the VB5 compiler and I think that may have something to do with it, the code is below. Any help would be greatly appreciated.


Private Sub txtNum_LostFocus()

Dim dbs As Database
Dim inventory As Recordset
Dim wrkDefault As Workspace
Dim temp As Integer
Dim counter As Integer


Set wrkDefault = DBEngine.Workspaces(0)
Set dbs = OpenDatabase("parts.mdb")
Set inventory = _
dbs.OpenRecordset("Inventory", dbOpenDynaset)

OrderByX

Do
If txtNum.Text = inventory!Itemnumber Then

txtNum.Text = inventory!Itemnumber
txtDesc.Text = inventory!Description
txtRetail.Text = inventory!Retail
txtWhole.Text = inventory!Wholesale
txtProfit.Text = inventory!Profit
cmbCompany.Text = inventory!Company
txtQty.Text = inventory!Qty
txtMemo.Text = inventory!MemoBox
counter = counter + 1
Else

inventory.MoveNext

End If

Loop While inventory!Itemnumber <> 1

End Sub

[MartinLiss]

What do you mean by "the program stops"? Does it end, or does it go into a loop?

What does OrderByX do?
What is Counter for?

This is not part of your problem but you should put the opening of the database in some other routine and do it just once, rather than every time the textbox loses focus. You should also make sure to set inventory = Nothing in the LoatFocus event and to close the database someplace. Also, the txtNum.Text = inventory!Itemnumber line is not necessary.

------------------
Marty
HASTE CUISINE
Fast French food.

[AcornRanch]

The orderbyx sub takes the database and orders the database according to item number. This is because the I have a flag set in the database. The counter was from another attempt at the database. Also, the programs gets stuck in a loop. But it shouldn't because the code looks right and it worked a day ago and I didn't change the code. Thanks.

[JHausmann]

I think you need to check for EOF in your do loop, otherwise it's gonna go infinite _if_ the last record's Itemnumber retrieved is some number other than 1.

[This message has been edited by JHausmann (edited 02-03-2000).]

[AcornRanch]

I just wanted to say thank you. The EOF thing worked. I just had it exit the do loop then. The more I think of it I DID have code like that in but I have no idea what happened. I knew it had worked before.

Thanks again!

Jeremy