Creating a recordset in Access with VBA

Question

From the title, i know it seems like this has been answered too many times over, but I have a series of incomprehensible problems with it. This is also my first time asking for help through posts, so I might forget to mention some stuff.

Function update_location_id()

    Dim rs As DAO.Recordset
    Dim db As Database
    Dim strSQL As String

    Set db = CurrentDb
    strSQL = "select id from location"

    Set rs = db.OpenRecordset(strSQL)

    MsgBox (rs.RecordCount)


End Function

I removed almost all the code from this function just to try to figure out why i couldn't get a record. this code generates a "too few parameters. expected one on the 'set rs = ...' line.

However if i change the select query's id to *, it works fine. However it returns 1. This is somewhat confusing seeing as there are a total of 3 records in the locations table right now.

Just incase its needed the location table looks like

id    description
1     "Location 1"
2     "Location 2"
3     "Location 3"

This is causing me to pull my hairs out and i can't really move on with my project if i can't do such a basic database action as...getting information from it.

References: Visual Basic for Applications, Microsoft Access 14.0 Object Library, OLE Automation, Microsoft Office 14.0 Access database engine object, Microsoft Internet Controls.

KekuSemau KekuSemau · Accepted Answer · 2012-10-06T11:36:36

Typically, the error "too few parameters" comes up when you use a column name that does not exist in the table, so it is interpreted as a parameter.
So, despite your given table data, I'd really check again the table name (location) and the column name (id).
As for the "1", yes, you have to insert
rs.MoveLast
rs.MoveFirst
after opening the recordset to get the correct RecordCount. Before moving to the last one, the recordset does not know how many records it yields (there are some more details to this problem, which I don't remember exactly right now, concerning recordset-type or so).

Creating a recordset in Access with VBA

1 Answers