Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 5th, 2006, 11:31 PM
Registered User
Join Date: Sep 2006
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Snapshot recordset returning only 1 record

I am trying to create a snapshot-type recordset using a SQL query and the following code:

    Set rsDividends = dbs.OpenRecordset("SELECT * FROM tblDividends WHERE ASX_Code = ""NAB""", dbOpenSnapshot)

It always returns only one record (and it's not necessarily the first record with ASX_Code = "NAB" in the table). I tried to use different conditions or tables, but always end up with only one record. Is there anything wrong with my SQL query and/or my table?

Old September 6th, 2006, 06:49 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

Perhaps it is a combination of things. Here is some code from MS and the answer may be here. How are you looking at the recordset? You may only be seeing the first record it grabs:

Option Explicit
'Define Variables

Private Sub UserForm_Initialize()

Dim dbDatabase As Database
Dim rsNorthwind As Recordset
Dim i As Integer
Dim aResults()

' This code activates the Database connection. Change
' the path to reflect your database.
Set dbDatabase = OpenDatabase("C:\My Documents\NorthWind.mdb")

' This code opens the Customers table. Change the Table
' to reflect the desired table.
Set rsNorthwind = dbDatabase.OpenRecordset("Customers", dbOpenSnapshot)

i = 0

   With rsNorthwind
   ' This code populates the combo box with the values
   ' in the CompanyName field.

      Do Until .EOF
         ComboBox1.AddItem (i)
         ComboBox1.Column(0, i) = .Fields("CompanyName")
         i = i + 1

   End With
End Sub

Notice that to populate a combo box, as here, you have to loop through the recordset to see all the values.

I also found some documentation that states with old DAO, at least, you need to Dim and Set this value, like this:

Dim dbOpenSnapshot

I don't think that is the issue. I think you are only looking at the first record and not looping through. Try doing this:

Set rsDividends = dbs.OpenRecordset("SELECT * FROM tblDividends WHERE ASX_Code = ""NAB""", dbOpenSnapshot)
i = 0
Do Until rsDividends.EOF
   i = i + 1

MsgBox i

and see what the count is.

Old September 6th, 2006, 06:16 PM
Registered User
Join Date: Sep 2006
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts

This works. Thanks a lot! Funny that this "behaviour" is not clearly explained in any of the reference books I have on Access VBA. While slightly unexpected, your solution has the advantage that I can start using the data I am looking for in the "Do...Loop".

The same thing also seems to happen if I try to create a Dynaset.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Returning a recordset John Pennington VB Databases Basics 8 August 18th, 2008 08:37 AM
Stored Proceedure Returning A Recordset rodmcleay C# 3 July 5th, 2004 09:53 PM
Returning record in stored function with ADO nmbarbillo Oracle 0 June 15th, 2004 12:36 PM
problem in Function returning Recordset pankaj_pp Classic ASP Professional 1 June 3rd, 2004 11:23 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.