calculation geomean in form using subform data in excel

[anil]

hi all
I am trying to calculate the Geometric Mean from the filtered data in
the subform(office 2000) in the text box in form.I am trying to
transfer the subform data into excel sheet and then using excel formula
to get GM.(as explained in book)I got this OLE automation code from
book:

Private Sub Form_Current()
Dim R As Recordset, X1 As Object

Set X1 = CreateObject("Excel.sheet.5")
Set R = Me.ExcelTestsubform.Form.RecordsetClone
pointer = 1
R.MoveFirst
Do Until R.EOF
X1.Cells(pointer, "A").Value = R.[Location]
R.MoveNext
pointer = pointer + 1
Loop
X1.Cells(pointer, "A").Formula = "=Geomean(A1:A" & pointer - 1 &
")"
Me.Geomean = X1.Cells(pointer, "A").Value

End Sub

But when I open the form I get the error "runtime error 13 type
mismatch" in line
'Set R = Me.ExcelTestsubform.Form.RecordsetClone'

Also it asks to declare pointer(which I declare variant later on) and
[Location] which is the field name and it do not recognise .

If this is wrong ,can you please recommend me other method.
thanks
anil

 

[Douglas J. Steele]

You've run into a real common problem!

There are two different data access methods available to you: DAO (Data
Access Objects), and ADO (ActiveX Data Objects), both of which have a
Recordset object in their model. To make matters worse, Access 2000 and 2002
don't set the reference to DAO by default.

While in the VB Editor, select Tools | References from the menu. Scroll
through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library and select it. Get out of that dialog, then
change your first line of code to

Dim R As DAO.Recordset, X1 As Object

 

[anil]

Thanks Douglas
I understand that and have done that,but still I am getting error
"method or data member not found" in line
X1.Cells(pointer, "A").Value = R.[Location]
where location is the field in subform.
Actually with recordset it should show all the controls when we press
"." after R but it do not show any controls.
Thanks
anil

 

[Douglas J. Steele]

Try R![Location] instead: the . implies a property or method of the
recordset, whereas Location is the name of a field. Either that, or
R.Fields("Location")

 

[anil]

Hi doug
I have tried that but I think problem may be in LHS of excel as while
pressing F8 it shows "location Name" on RHS but nothing on LHS.
can you help on this one.
Actually I want to transfer subform data in embeded chart(excel) using
unbound OLE object and thus getting the graph rom the data in excel
sheet.
thanks
anil