Using SQL statement in VBA Access 2007

Posted on 2012-08-30
Last Modified: 2012-08-30
I am trying to pull records from a table in Access 2007.  The script will run in VBA but only pull 1 record from the table using recordcount but there are two records in the table.  When I run it as a regular query, which pull two records,  and look at the SQL code generated it looks like this:

SELECT tblLoginSecurity.KerberosID
FROM tblLoginSecurity
WHERE (((tblLoginSecurity.KerberosID)="myname"));

When I try to do the same in VBA using the following code, it only pulls 1 record.

'Declares variables
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sSQL As String
'Set the Data Access Object as the current db
Set db = CurrentDb

 sSQL = "Select * from [tblLoginSecurity] where KerberosID = 'myname'"
        If rs.RecordCount < 1 Then
**** This recordcount only shows 1 record.

Any idea on why it doesn't see the two records?
Question by:marku24
    LVL 61

    Accepted Solution

    When you initially open the recordset, it will only display 1.  Put in a MoveLast before your recordcount check to get the actual count.  (It is based on position in the recordset)


            If rs.RecordCount < 1 Then
    **** This recordcount only shows 1 record.

    LVL 39

    Expert Comment

    I hope you have string where rs is opened?
    Set rs = bb.Openrecordset(sSQL)

    Author Closing Comment

    ugh, my bad.  This works, thanks

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now