VBA Newbie question

[cosmoh2o]

Please HELP,
I am new to VBA using MS Access and am trying to get database object variable to connect to my current database and am getting the following error "Compile-error: user-defined type not defined". The code I am writting in the module follows:

Public Function Book_Cnt() As Integer
Dim rs As Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordSet("BOOKS&quot
Book_Cnt = 1
End Function

I know this is probably a very simple problem but, like I said I am new to Access and need help. Thanks VERY much for ANY help that can be provided by anyone.

 

[Junior1544]

well, one thing i don't understand is this...

when you call this funtion, it will alwas return one... The last line of code is Book_Cnt = 1...

ok, now for your question...

you are probably using access 2k or 2k2... where as "currentdb()" is not valid... that is DAO programming... to make that work the way you want, go into References under tools. and go down until you find Microsoft DAO 3.6 and check it... then change the code to this:

(btw, on a side note, when you open a recordset you should alwas close it, it's good practice...)


Public Function Book_Cnt() As Integer
Dim rs As dao.Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordSet("BOOKS&quot
Book_Cnt = 1
rs.close
set rs = nothing
End Function


Hope this helps...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[hermanlaksko]

Junior is absolutely correct, however he is missing one little thing: Dim db As DAO.Database (both recordset and database should be dim as dao)

But the way I see it, you code does not do anything, well yes, it graps the recordset books but it does not work with it, it just closes the recordset again.
So what are you trying to do with this recordset, I wonder?
Let us know ;-)

Herman

 

[pgk]

Hi,
As stated above set a reference to DAO 3.6 Object Library.

If the purpose of your function is to return the total number of books in the Books table, try the following:

Public Function BookCount() as Integer

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db=CurrentDB
Set rs=db.OpenRecordset("Select * from Books",dbOpenDynaset)

If rs.RecordCount > 0 then
rs.MoveLast
rs.MoveFirst
End If

BookCount=rs.RecordCount
End Function Hope it helps. Let me know what happens.
With regards,
PGK

 

[pgk]

Hi,
In the above function, add the following lines of code between BookCount=rs.RecordCount and End Function.

rs.Close
db.Close
Set rs=Nothing
Set db=Nothing

Forgot to put it down in the previous post. Hope it helps. Let me know what happens.
With regards,
PGK

 

[Junior1544]

hermanlaksko,

I didn't think you needed to reference a database object as dao since it doesn't exict in ado... Thanks for correcting me...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[cosmoh2o]

Thanks for all the helpful tips. After selecting Microsoft DAO 3.6 under "tools -> references" the code worked. It was just a simple function snippet to test Access but THANKS to everybody that sent in tips - they were very enlightening.