• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 676
  • Last Modified:

Using SQL statement in VBA Access 2007

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?
0
marku24
Asked:
marku24
1 Solution
 
mbizupCommented:
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)


       

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

rs.MoveFirst
0
 
als315Commented:
I hope you have string where rs is opened?
Set rs = bb.Openrecordset(sSQL)
0
 
marku24Author Commented:
ugh, my bad.  This works, thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now