# Access: DAO and VBA recordset question



## downwitchyobadself (Oct 13, 2000)

Hi all,

I think there's a way of testing specifically whether or not a DAO recordset has been initialized, so that you can close it conditionally. I'm sure I read it somewhere, but have been all around VBA help, KB etc., can't find it.

In other words, I'm looking for an intrinsic function which does what this line won't do:

If MyRST = Nothing then...

Obscure, to be sure, but would appreciate any help.


----------



## koala (Apr 18, 2001)

I may not be understanding what you are trying to achieve.

When you say you are trying to determine whether the "recordset has been initialized" are you trying to determine whether there are any records returned in the recordset (ie .RecordCount = 0) or whether the recordset was used at all?

Anyway here is a snippet of my code that works (I think you will get the idea):

Set qryDBQuery = dbDataBase.QueryDefs(m_strQryGetItem)
qryDBQuery.Parameters.Refresh
qryDBQuery.Parameters("ReqdNo").Value = txtItemNum
Set recDBRecSet = qryDBQuery.OpenRecordset()

If recDBRecSet.RecordCount = 0 Then
Set recDBRecSet = Nothing
Set qryDBQuery = Nothing
MsgBox "This item does not exist", vbExclamation + vbOKOnly, "Error"
txtItemNum.SetFocus
Exit Sub
End If

recDBRecSet.MoveFirst
txtItemName = recDBRecSet![ItemName]
txtDesc = recDBRecSet![ItemDescription]
DoEvents
Set recDBRecSet = Nothing
Set qryDBQuery = Nothing

If you wanted to know if any object is still current you would say:
"If recDBRecSet is Nothing ..........."

Hope this helps - or please post again.

[Edited by koala on 05-03-2001 at 07:54 PM]


----------



## downwitchyobadself (Oct 13, 2000)

Thanks for your reply. But no, it's not testing for no records; it's testing for initialization of the recordset. If you copy this procedure into a module and run, you'll see that it returns an "invalid use of object" compile error:

Public Sub TestRst()

Dim rst As DAO.Recordset

If rst = Nothing Then
MsgBox "rst not initialized."
Else
rst.Close
End If
End Sub

In other words, if in a (rather long) procedure with numerous recordsets being created, etc., some of the rst's are initialized only conditionally. While I could always create variables saying whether or not they've been created, I'm sure there's a way to test the recordset procedure.

Long way:

If (condition which necessitates rst1) then
set rst1 = blah blah blah
bleRst1Initialized = true
'Do lots of stuff
end if

...two hundred lines later...

If bleRst1Initialized then rst1.Close

I _know_ a shorter way exists, in which you can test the rst directly. Hope this is clearer?


----------



## YSB (Mar 7, 1999)

In the line "rst = Nothing", take out the = and replace it with the word 'Is'.

If rst *Is* Not Nothing then rst.close

[Edited by YSB on 05-06-2001 at 03:48 AM]


----------



## qucho (Jan 10, 2006)

```
Dim rst AS ADODB.recordset
Dim rst AS ADODB.Connection
Dim sql AS String

Set cnn = Application.CurrentProject.Connection
sql = "SELECT * FROM my_table"
If rst.state = 0 Then
      MsgBox "Recordset is not open"
      If cnn.State = 0 Then
         cnn.Open
      End If
      rst.Open sql, cnn
End If
```
Calling a record count or EOF/BOF with a closed recordser will raise an error.

this connection is for a table within the same access database your code is running from.


----------



## slavik (Apr 3, 2008)

Hy!
I am working on my first DB project, and I have a question, that may be interesting for you also.
I created a form for date searching, for this I wont to make a funcion that generate SQL queries

But on Ron Macro it give me an eror
here is my script with coments

Sub Chek_Click()

Dim q, v1 As String
q = "SELECT * FROM Reclamatii WHERE NR = True"

If (C11) Then
v1 = ([Forms]![F_Cautare]![C11])
q = q + " AND DataPrimirii = " + "#" + v1 + "#" _' row 6_
End If

'until here it is OK

Dim v2 As String
If (C2) Then _' here is the problem, and I dont know why_
v2 = ([Forms]![F_Cautare]![C2]) _' if I declare v2 together with q and v1 it _gives eror on row 6 
q = q + " AND Strada = " + "'" + v2 + "'" '
End If

Debug.Print q

Dim rst1, rst2 As Dao.Recordset
Set rst1 = CurrentDb.OpenRecordset(q)
Set rst2 = CurrentDb.OpenRecordset("ReclamatiiCautate")
Dim st1, st2 As String
Do Until rst1.EOF
rst2.AddNew

rst2!NR = rst1!NR
rst2!DataPrimirii = rst1!DataPrimirii
rst2!Strada = rst1!Strada
rst2!Bloc = rst1!Bloc
rst2!Sector = rst1!Sector
rst2!Telefon = rst1!Telefon
rst2!Nume = rst1!Nume
rst2!Prenume = rst1!Prenume
rst2!Operator = rst1!Operator
rst2!Efectuat = rst1!Efectuat
rst2!DataEfectuarii = rst1!DataEfectuarii
rst2!Remarca = rst1!Remarca

rst2.Update
 rst1.MoveNext

Loop

End Sub

Could someone help me, plz! 
thx!!!!!


----------

