Run-Time error '13' Type Mismatch on recordsetclone

[pm65151]

I am getting the above message on the below code can someone tell me what I am doing wrong?

Dim Db As dao.Database
Dim Rs As dao.Recordset
Dim Rsfrm As Recordset
Dim crtl As Control

Set Db = CurrentDb
Set Rs = Db.OpenRecordset("TBL_ATU_Daily_Numbers", dbOpenDynaset)
Set Rsfrm = Me!SUBFRM_DAILY_NEW_UPDATED.Form.RecordsetClone

Do Until Rsfrm.EOF

Rs.AddNew
Rs![Work_Day] = Forms!FRM_RESULTS!SUBFRM_DAILY_NEW_UPDATED!TXT_Wor k_Day
Rs![Region] = Forms!FRM_RESULTS!SUBFRM_DAILY_NEW_UPDATED!TXT_Reg ion
Rs![Inc_Type] = Forms!FRM_RESULTS!SUBFRM_DAILY_NEW_UPDATED!TXT_Inc _Type
Rs![Inc_Total] = Forms!FRM_RESULTS!SUBFRM_DAILY_NEW_UPDATED!TXT_Inc _Total
Rs.Update
Rsfrm.MoveNext
Loop

[MMcCarthy]

I don't think you can set a recordsetclone on a subform. I could be wrong.

Try setting it on the form rather than the subform object.

Set Rsfrm = [Forms]![MainFormName]![SUBFRM_DAILY_NEW_UPDATED].Form.RecordsetClone

If that doesn't work try using the Record Source (assuming theres no filters.

Set Rsfrm = [Forms]![MainFormName]![SUBFRM_DAILY_NEW_UPDATED].Form.RecordSource

You will have to add a Rsfrm.MoveFirst it this does work.

Let me know how you get on.

[pm65151]

When I try

Set Rsfrm = Forms!FRM_RESULTS!SUBFRM_DAILY_NEW_UPDATED.Form.Re cordsetClone

I get Run-Time error '13' Type Mismatch on recordsetclone

When I try

Set Rsfrm = Forms!FRM_RESULTS!SUBFRM_DAILY_NEW_UPDATED.Form.Re cordSource

I get run-time error '424' Object required.

[pm65151]

Microsoft website show the syntax as

Set Rsfrm = Forms!FRM_RESULTS!SUBFRM_DAILY_NEW_UPDATED.Records etClone

However, when I execute this I get an Run-Time Error '438' Object doesn't support this property or method.

The code for the form I am trying to get the recordset of looks like this.

Private Sub Form_Load()
With cnnCMS
.Open "Connectionstring"

End With
strSQL = "SELECT Q2.LOAD_DATE, Q2.C_SUBGRP, Q2.C_FLAG , COUNT(Q2.C_CCASE) 'CASES' " & _
"FROM (SELECT DISTINCT Q1.LOAD_DATE, Q1.C_SUBGRP, Q1.C_FLAG, Q1.C_CCASE FROM " & _
"(SELECT convert(varchar,D_PRE_STG_RCLP.LOAD_DT,101) 'LOAD_DATE', D_PRE_STG_RCLP.C_SUBGRP, " & _
"D_PRE_STG_RCLP.C_FLAG, D_PRE_STG_RCLP.C_CCASE FROM D_PRE_STG_RCLP " & _
"WHERE D_PRE_STG_RCLP.LOAD_DT>='" & Forms.FRM_MAIN.Text0 & "' " & _
"AND D_PRE_STG_RCLP.LOAD_DT<='" & Forms.FRM_MAIN.Text1 & "') AS Q1) AS Q2 " & _
"GROUP BY Q2.LOAD_DATE, Q2.C_SUBGRP, Q2.C_FLAG"
With rstCMS
Set .ActiveConnection = cnnCMS
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open strSQL
End With
Set Me.Recordset = rstCMS
Me.TXT_Work_Day.ControlSource = "LOAD_DATE"
Me.TXT_Region.ControlSource = "C_SUBGRP"
Me.TXT_Inc_Type.ControlSource = "C_FLAG"
Me.TXT_Inc_Total.ControlSource = "CASES"

cnnCMS.Close
Set cnnCMS = Nothing
Set rstCMS = Nothing
End Sub

[MMcCarthy]

Sorry I had to go out for a few hours.


Just had a thought. The name of the subform object is not always the name of the subform.

In the main form design click on the frame surrounding the subform and open the properties window. Under the Other tab look at the value in the Name property.

Is it the same?

If not, go back to your original code with

Set Rsfrm = Me!SubformObjectName.Form.RecordsetClone

[pm65151]

I checked the tab it list the same name as the form. Other than requery the rs from the subform, can you think of anything that would work?

[MMcCarthy]

I checked the tab it list the same name as the form. Other than requery the rs from the subform, can you think of anything that would work?

What event are you running this code in and is it from the main form or the subform.