Hi

I want to open a DAO.Recordset from an opened DAO.Recordset.

My code is like this

Dim db As DAO.Database
Dim rs, rsMarks As DAO.Recordset
Dim strSQL, strMarks As String

Set db = CurrentDb()

strSQL = "SELECT Name, SurName, Marks from Table1"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

strMarks = "Select Sum(Marks) As MarksSum from rs"
Set rsMarks = db.OpenRecordset(strMarks, dbOpenSnapshot)

rs.MoveFirst
Me.txtName = rs!Name
Me.txtMarks = rs!Marks
Me.txtSurName = rs!SurName

rsMarks.MoveFirst
Me.txtMarksSum = rsSur!MarksSum

rs.Close
rsSur.Close
db.Close
Set rs = Nothing
Set rsSur = Nothing
Set db = Nothing

But I got an error message “The Microsoft Jet Database engine can not find the input table or query ‘rs’. Make sure input table or query exist”

While without this second 'rsMarks' my first SQL 'rs' works well

I will be grateful if any one help me.

Regards

Rahulgty

strMarks = "Select Sum(Marks) As MarksSum from rs"

Won't work, as rs needs to represent the name of an existing table or query as the error message also indicates.

Another remark, you should declare the type explicitally for all your variables.
If not, Access will use the default being Variant.

RV

Thanks RV
Is there any way to get the total on form through DAO, except subform.

Rahulgty

There seems to be a lot of people using unbound forms these days. Perhaps a new forum should be created.

With a bound form, you could just add a control to the footer section to sum the Marks field and eliminate ALL your code.

Thanks Pat

But I am displaying the data in a listbox, so it is not possible to put textbox for sum on formfooter.

Any way I have found a way, if u or any one found something wrong please quote. I have made a little changes as below-

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim MarkSum as Double

Set db = CurrentDb()

strSQL = "SELECT Name, SurName, Marks from Table1"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

rs.MoveFirst
Me.txtName = rs!Name
Me.txtMarks = rs!Marks
Me.txtSurName = rs!SurName

MarkSum = 0
Do Until rs.EOF = True
MarkSum = MarkSum + rs!Marks
rs.MoveNext
Loop
Me.txtMarkSum = MarkSum


rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

Yea, its working well and putting total of selected records in a textbox placed just below the listbox.

rahulgty

Opening a recordset and looping through it is slower than running a totals query.

Thanks Pat for you valuable information.

I am new in programing and I feel its very easy to first create application in access, check the result, then write in Visual basic. I think in visual basic only recordset is the option.

Regards

rahulgty

Thanks Pat for you valuable information.

I am new in programing and I feel its very easy to first create application in access, check the result, then write in Visual basic. I think in visual basic only recordset is the option.

Regards

rahulgty