recordsets - need to understand concept as well as code 1

[solun]

Okay, I understand what a record set is and how it's created - with a SQL statement. What I don't understand is when they're public or private? in a module or a procedure? I'm getting errors with this code which is simply behind a cmd button:

Public Sub Command167_Click()

Dim rstApps As doa.Recordset 'this might be part of the problem

strSQL = "SELECT applicationid, loanid FROM LoanDetailsApplications where loandetailsapplications.loanid=loanid"

'I get the error:Compile error: user-defined type not defined with this line:
Set rstApps = CurrentDb.OpenRecordset(strSQL)

If IsNull(rstApps.RecordCount) Then
    MsgBox "No applications have been entered for this loan.", vbOKOnly, "No applications entered!"
    Set rstApps = Nothing
    Set rstAllSubs = Nothing
    Exit Sub
    End If
    
Exit_Command167_Click:
    Exit Sub

Err_Command167_Click:
    MsgBox Err.Description
    Resume Exit_Command167_Click

End Sub

Heidi I. Jones
Ridgerunner Consulting
Questions Answered. Problems Solved.

http://www.ridgerunnerconsulting.com

 

[traingamer]

doa.recordset? ado.recordset!

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[solun]

*giggle*

well, greg, now that it's spelled right, it still gives the same error: "Compile error: user-defined type not defined" at Dim rstApps As ado.Recordset

thanks

Heidi I. Jones
Ridgerunner Consulting
Questions Answered. Problems Solved.

http://www.ridgerunnerconsulting.com

 

[solun]

Just to be a smartypants, i also changed it to dao.recordset... the code didnt' error, but also didn't return anything.

Heidi I. Jones
Ridgerunner Consulting
Questions Answered. Problems Solved.

http://www.ridgerunnerconsulting.com

 

[traingamer]

Try:

Dim db as DAO.Database ' or whichever type ado , etc.
Dim rstApps As ...
...
Set db = CurrentDb
Set rstApps = db.OpenRecordset(strSQL)

You may need to Dim strSQL as well

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

Sounds like you're on the right track.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[solun]

so what's the difference b/t ado & dao?

i currently have:

Public Sub Command167_Click()


Dim db As DAO.Database
Dim rstApps As DAO.Recordset
Set db = CurrentDb

strSQL = "SELECT applicationid, loanid FROM LoanDetailsApplications where loandetailsapplications.loanid=loanid"
Set rstApps = db.OpenRecordset(strSQL)

If IsNull(rstApps.RecordCount) Then MsgBox ("No applications have been entered for this loan.")
    Set rstApps = Nothing
    Set rstAllSubs = Nothing

Heidi I. Jones
Ridgerunner Consulting
Questions Answered. Problems Solved.

http://www.ridgerunnerconsulting.com

 

[traingamer]

See Recordset Property in Access help. I really meant to say DAO in my first response above.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[PHV]

You may also consider the DCount function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[solun]

PHV -

Thanks for the DCount reference. I'm learning VBA on the job.

I'm trying out the DCount function:

Private Sub Command167_Click()

Dim str As String
Dim apps As Double

str = "[loanid]=" & Me.LoanID
MsgBox (str) ' THIS WORKS

apps = DCount("appid", "LoanDetailsApplications", str)
'error: Microsoft Visual Basic: Run-time error 2001: You cancelled the previous operation.


If IsNull(apps) Then MsgBox ("No applications have been entered for this loan.") Else MsgBox (apps + " applications have been entered for this loan.")

End sub

why am i getting the error message?

Heidi I. Jones
Ridgerunner Consulting
Questions Answered. Problems Solved.

http://www.ridgerunnerconsulting.com

 

[buhgman33]

I may not have followed the whole thing properly, and I have never used the dcount.
I did try this so give it a shot.
First I have found the using the RecordCount function if the recordset is Null then it returns an answer of 0, not Null.
Second, RecordCount seems to return the count of the current record, so try to put rstApps.MoveLast prior to using the RecordCount function.

Hope this helps.