Solved

db.OpenRecordset gives error

Posted on 2007-04-08
19
465 Views
Last Modified: 2012-06-21
In Access 2003, I am trying to understand how to open recordsets using vb code and manipulate them. However, I am getting an error and I need help to figure out why. The code is below:

    Dim strSQL As String
    Dim strIn As String
    Dim strWhere As String
    Dim numrec As Integer
    Dim rs As Recordset
    Dim db As Database
    Dim qdf As QueryDef
0
Comment
Question by:dottieelliott
  • 7
  • 6
  • 4
  • +1
19 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 125 total points
ID: 18874562
Two types of recordsets u can use, ADO or DAO

Always best to specify the recordset type then
eg

DIm db as DAO.Database
DIM rs as DAO.Recordset


recordsets can be opened based on your db variable and u pass in a query
by db variable I mean, current db or another opened db

eg

set db = currentdb
set rs = db.openrecordset("select * from mytable")

'loop thru, keep looping until EOF (end of file) flag is not true
do while rs.eof = false

    'reference a field
     debug.print rs!somefield

     'add a record
      rs.AddNew
      rs!somefiled = somevalue
      rs.Update

     'edit a record
      rs.Edit
      rs!somefiled = somevalue
      rs.Update
     
      'go to next record
      rs.movenext
loop


'close down
rs.close
set rs=nothing



If u use DAO, u need to ensure you have checked Microsoft DAO Object Library v3.6 in Tools/References
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 18874566
Sorry, should of said, the example given uses DAO.

set db=currentdb  

means use current database. To specify another, u can do this

    Set db = DAO.openDatabase("Fullpathoddb")




0
 
LVL 65

Expert Comment

by:rockiroads
ID: 18874619
What exactly do you want to do with your recordsets. I can then give more specific help then
0
 
LVL 1

Assisted Solution

by:genaughton
genaughton earned 200 total points
ID: 18875570
Looks like rockiroads has you covered for DAO.  Here's sample code for using ADO recordsets.  Best practices note: ALWAYS write your .close and Set rst=Nothing code as soon as you've declared the rst object so your don't forget!

-JN
=================================

      Dim rst As New ADODB.Recordset
      Dim strSQL as String
      
      '*  SQL to select your records, i.e., to get all
      '*  Customer company names in area with zips codes
      '*  in 627__ ...
      
      strSQL = " SELECT Customers.CompanyName " _
            & "FROM Customers " _
            & "WHERE Customers.Zip LIKE 627*"

      With rst
            .Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
            .MoveLast
            .MoveFirst
            Do Until .EOF
                '* Use your records here...
                debug.print rst!CompanyName
                .MoveNext
            Loop
            .Close
      End With

      Set rst = Nothing
0
 

Author Comment

by:dottieelliott
ID: 18876375
I see that my full message did not post. Right now, I simply want to know why I get the error. I cannot move forward in my function until that is solved. Here is the code:

    Dim strSQL As String
    Dim strIn As String
    Dim strWhere As String
    Dim numrec As Integer
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim qdf As QueryDef
0
 

Author Comment

by:dottieelliott
ID: 18876531
I see that my full message did not post again! Right now, I simply want to know why I get the error. I cannot move forward in my function until that is solved. Here is the code:

    Dim strSQL As String
    Dim numrec As Integer
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim qdf As QueryDef
    Dim SCPODATE as Date
    DIM SCID as Integer
    Dim ID as Integer
    DIm SCDATE as Date
    Dim iCan as Integer
      
strSQL = "SELECT *, Customer_ID FROM tblServiceCalls WHERE Customer_ID=" & Me!Customer_ID
   
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSQL)
   
    rs.MoveFirst
    numrec = rs.RecordCount
   
    Do While rs.EOF = False
       SCID = rs!ID
       ID = rs!Customer_ID
       SCDATE = rs![SC DATE]
       SCPODATE = SCPO_Paid
       iCan = rs!Cancelled
       rs.MoveNext
    Loop
   
Now, I orginally had set rs as this:
set rs = db.OpenRecordSet("SELECT *, Customer_ID FROM tblServiceCalls WHERE Customer_ID=Forms![frmCustomersActions]![ID]
0
 

Author Comment

by:dottieelliott
ID: 18876552
However, now I know I should have used:
set rs = db.OpenRecordSet("SELECT *, Customer_ID FROM tblServiceCalls WHERE Customer_ID=" & Forms![frmCustomersActions]![ID])

So that issue is solved now.

I have 2 problems:
Prob. 1 - The dates are sometimes null. When that happens I get error 94 Invalid use of null. I just want my temp variables to get set to null if their recordset counterpart is null. How do I do this?

Prob 2 - How do I get a count of the number of records in the recordset? I thought rs.RecordCount would tell me. However, it returns a 1, even when 3 records are selected.

Thank you!
0
 
LVL 75
ID: 18876580
No real need to use ADO if you are connecting to an Access MDB.  DAO is optimized for that purpose.

mx
0
 
LVL 75
ID: 18876593
Exactly where (what line) does the error occur and what is the error number ?

mx
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:dottieelliott
ID: 18876730
Regarding null dates problem

I get Run-time error 94. Invalid use of Null.

when the program reaches:

SCDATE = rs![SC DATE]

if [SC DATE] is null

I just want to set the temp variable SCDATE to the value of [SC DATE] even if [SC DATE] is null.

How do I do that?
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 175 total points
ID: 18876784
Because "  DIm SCDATE as Date"

A Date/Time Data Type cannot be Null

So ...

If Not IsNull(rs![SC DATE]) then
   SCDATE = rs![SC DATE]
Else
    <do something else
End If

mx
0
 
LVL 75
ID: 18876800
"A Date/Time Data Type cannot be Null"

Sorry ... I mean that ... if you Dim a variable to a Date  data type, you cannot then set it to Null

Same for Dim sDottie as String
....
sDottie = Null   (or some field or value that is) >>> error will occur.

mx
0
 

Author Comment

by:dottieelliott
ID: 18876972
OK, a variable cannot be null but the date in a table can be null so what is the normal way to handle this situation?
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 175 total points
ID: 18877099
Yes ... **in** the table, a date field previously not populated is in fact Null.  That's why I made the previous clarification.

Note ... a Variant variable can be Null ... very useful for cases when you are not sure.

So ... handle it as I showed above:

If Not IsNull(rs![SC DATE]) then
   SCDATE = rs![SC DATE]
Else
    <do something else
End If

Is this what you mean?  If not ... what ?

mx
0
 
LVL 1

Assisted Solution

by:genaughton
genaughton earned 200 total points
ID: 18877825
re: your problem 2 -- With DAO, add rs.MoveLast before your rs.MoveFirst so it's aware of the records:

   
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSQL)
   
    rs.MoveLast
    rs.MoveFirst
    numrec = rs.RecordCount

JN
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 175 total points
ID: 18877881
If you do a Move.First ... and rs.RecordCount = 0 (or .EOF and .BOF) ... then you will get an error.

You must always check for zero record count first ... before executing a Move operation.

mx
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 18878262
Blimey! lots of responses

dottieelliott, I see you got your responses

just to recap (not for points this as already given by others)

your variable definition as to be defined as variant if u want it to hold null, as DatabaseMX has said.

regarding recordcount, if you are going to loop thru the records then u could just use a variable, keep incrementing each time within the loop. Saves the extra processing of having to movelast.

If your not looping thru then stick with moving to last like genaughton has said and check for EOF like DatabaseMX has said
0
 
LVL 75
ID: 18937099
Any resolution to this issue?  Just checking.

thx.mx
0
 

Author Comment

by:dottieelliott
ID: 18940741
Thank you for all of your help!  I finally have this working. Your responses and some messing around let me find the errors and my misconceptions and get it working!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

708 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

18 Experts available now in Live!

Get 1:1 Help Now