Solved

db.OpenRecordset gives error

Posted on 2007-04-08
19
471 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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
 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

810 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