db.OpenRecordset gives error

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
dottieelliottAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rockiroadsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rockiroadsCommented:
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
rockiroadsCommented:
What exactly do you want to do with your recordsets. I can then give more specific help then
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

genaughtonCommented:
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
dottieelliottAuthor Commented:
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
dottieelliottAuthor Commented:
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
dottieelliottAuthor Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
No real need to use ADO if you are connecting to an Access MDB.  DAO is optimized for that purpose.

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Exactly where (what line) does the error occur and what is the error number ?

mx
0
dottieelliottAuthor Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"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
dottieelliottAuthor Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
genaughtonCommented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
rockiroadsCommented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Any resolution to this issue?  Just checking.

thx.mx
0
dottieelliottAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.