I am fairly new to this Access and VBA coding.  I am trying to write a routine in VBA code to parse the records in a table.  I found an example that uses a Database Object and it looks like this might be a good way for me to implement this; however, I am getting a Compile Error - Invalid use of Property on the assignment of my Database Object (db) to CurrentDb().

Here is the code:

        Dim myDb As Database
        Dim myRecordSet As Recordset
        Dim strSQL As String
        Dim myCount As Integer
        Dim rsCount As Integer

        myDb = CurrentDb()
        strSQL = "SELECT InventoryControlNumber from InventoryItems"
        myRecordSet = myDb .OpenRecordset(strSQL)
        myRecordSet .MoveLast
        myCount = rs.RecordCount
        myRecordSet .MoveFirst
        For rsCount = 0 To myCount - 1
            Debug.Print (rsCount)
            Debug.Print (myRecordSet .Fields(0))
            Debug.Print (myRecordSet .Fields(1))
            Debug.Print (myRecordSet .Fields(2))
            Debug.Print (myRecordSet .Fields(3))
            Debug.Print (myRecordSet .Fields(4))
            Debug.Print (myRecordSet .Fields(5))
            myRecordSet .MoveNext
        Next rsCount

        ' Close recordset
        myRecordSet .Close
        Set myDb = Nothing

I appreciate any assistance that can be provided.

FYI:
Here are the current Visual Basic References that I have checked:

[X] Visual Basic For Applications
[X] Microsoft Access 9.0 Object Library
[X] OLE Automation
[X] Microsoft ActiveX Data Objects 2.1 Library
[X] Microsoft DAO 3.6 Object Library
[X] Microsoft Office 10.0 Object Library


I am using Microsoft Access 2000 (9.0.6926 SP3)
with Visual Basic 6.0


Also....
I tried changing CurrentDb() to CurrentDB
with no parenthesis - same error.
I tried specifying DAO in my variable declarations:
Dim myDb As DAO.Database - same error.


Thanks,
Bill






UPDATE

Making a little bit of progress..........I think........

I changed the code to
Set myDb = CurrentDb
and that seems to have fixed the Compile Error
BUT
now I am getting
Run-time Error 13 - Type MisMatch
on this line:
Set myRecordSet = myDb.OpenRecordset(strSQL)

I guess I picked some bad sample code to base my first Access/VBA programming experiment on....



ANOTHER UPDATE

Changed the following:
Dim myDb as DAO.Database
Dim myRecordSet As DAO.Recordset
These changes fixed the Type MisMatch runtime error.

Now getting Error: Item not in collection
at this line:
Debug.Print (myRecordSet .Fields(1))

Guess I need to figure out how to use indexing with a RecordSet Field object.

  

YET ANOTHER UPDATE:
Decided to use a For Each loop instead of the For/Next.
Replaced For/Next with
Dim fld As DAO.Field
For Each fld In myRecordSet.Fields
   ' Print field names.
     DebugPrint fld.Name
Next




Although I have been talking to myself here, it seems to be helping me solve this.



[Edited by BlackDuck603 on 15-11-2007 at 01:16 AM GMT]

[Edited by BlackDuck603 on 15-11-2007 at 01:30 AM GMT]

[Edited by BlackDuck603 on 15-11-2007 at 01:54 AM GMT]

Hi BlackDuck
It looks like you are making impressive headway here (wish more people attempted stuff in this manner - it's only a few of you guys that actual try!)
So...
First off. In visual basic (and all variants of it - VBA/VB6/VBNET/7), there is a difference between an Object and a simple data type. All objects MUST have the word SET in front of them when using a variable:
E.G.
Set MyDb - currentdb()
Set MyObject = thatobject
You can tell the difference by the variable type. IF it's not String, Integer, Date, Double or Single - it's probably an Object and should have the keyword "SET"
There is no real reason for this apart from MS deciding that it would be a good idea.

In Microsoft Active DAta Objects and in Data Access Objects (ADO and DAO to you and me), Microsoft declared a "thingy" called a recordset. As you already worked out, one of the issues you had was because access didn't know which particular thingy you wanted (Recordset, Database, etc.) - by telling Access which one you wanted (ie. Dim rs as ADO.Recordset, or Dim rs as DAO.Recordset) you got around this issue.

IMPORTANT!!!
IF YOU IGNORE ANYTHING ELSE I'VE WRITTEN, DON'T IGNORE THIS BIT!!!!
By putting Brackets around something, you are asking vb (or VBA) to "take a quick estimate as to what it is"... really really really a bad idea. NEVER put brackets round something unless it is a calculation (ie. a function call, or a multiple or adding some strings together or numbers or something)! VB also copies the values rather than passing them by reference (brackets mean use resultant not source).

Just my tuppence worth
Well done BlackDuck - keep up the good work



____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

stickleprojects - Thanks for your great and informative comments. I was suspicious of the brackets and have actually managed to remove them all from my code. There are many coding examples out there (even in some of Microsoft's examples I believe) that use the brackets around forms and fields. I will be more more discerning when it comes to use of samples.

I appreciate the explanation on when to use SET. That was very helpful.

This piece of the project is now working fine.

I will probably be back with more questions as I forge on.

Thanks
Bill