So close...Does not like the way I Dim Database

[jamiek555]

I am using Access 2000.

When I step through or run the code below and get to the "Dim db as Database" line, it gives me an error:

Microsoft Visual Basic: Compile Error - User-defined type not defined


Why? How can I fix this? Do you see any other glaring problems with my code?

Thanks in advance,

Jamie


' Option Compare Database
Private Sub AdjustInventory()


Dim db As Database
Dim rst As Recordset
Dim strSql As String
Dim Response As Integer


Set db = CurrentDb()
Set rst = db.OpenRecordest("QryQuantityProdOrdered")


With rst
If .RecordCount < 1 Then
.Close
Response = MsgBox("No Inventory Adjustments Requred", vbOKOnly, "No Inventory Adjustment")
End

Else

Do While Not .EOF
strSql = "Update TblProduct Set ProdNoInStock = ProdNoInStock - " & _
![SumOfQuantity] & " Where ProdID = " & ![ProdID]

db.Execute (strSql)

strSql = "Update TblOrderDetail Set AppliedToInventory = 'yes'" & _
" Where ProdID = " & ![ProdID] & " and AppliedToInventory = 'no'"

db.Execute (strSql)
.MoveNext
Loop
End If

End With

 

[casey]

You may need to add the Microsoft DAO 3.6 Object Library.
To do so...In Visual Basic window on the menu, select >>Tools>>References...

A form will be displayed showing all active libraries. Search for Microsoft DAO 3.6 Object Library in the list and place a check by it. This will add the Database, Recordset, etc. objects to Access which allow you to access your DB.

Hope that helps.

 

[jamiek555]

Thanks...all better now...

Thanks Casey,

Not only did I need to do this, but I needed to switch the DAO library above the ADO library.

Now I am stuck on getting my module to run when I open a query (see my post in the Queries section).

Jamiek

 

[Alexandre]

I don't know where you launch your query from (you say not from a form) but that is where you should also call your sub from...