referencing adp/sql recordset clone in forms

[mapfax]

Hi,

I am having real problems resolving this issue, can someone help?

I have an Access project 2000 attached to SQL Server 7. I am trying to convert code written specifically for Access mdb. The code is designed to set an object to:

Set rs = Forms(Form).Control(Subform).Form.RecordSetClone

This mdb has been upsized to SQL Server.

My question is how do I convert this for ADO, assuming rs is now an ADODB.recordset? I am aware about 'rs.clone', but I cannot figure out how to get by a 'type mismatch' error.

I have found an article by Ken Getz about this problem, referencing recordsets in Access 2000 forms and SQL 7 (Access not sure whether it should be DAO or ADO) but I would presume it does not apply here, as I have an Access project. I just think I need help with the syntax.

Appreciate any help.

ps. I had posted this in SQL Server forum 16/8 but I can't find my post - otherwise not cross posting.

 

[Steve101]

Try the following:

(a) Ensure that ADO library is referenced
(b) Use the following code:
Dim rs As ADODB.Recordset
Set rs = Forms("frmMain&quot.Controls("frmSub&quot.Form.RecordsetClone

or you can use the shortened syntax:

Set rs = Forms("frmMain&quot("frmSub&quot.Form.RecordsetClone

Replace the "frmMain" and "frmSub" string constants as appropriate (use variables if you like). As you said, your problem was with syntax.

Hope this helps,
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[cmmrfrds]

This should work also in the OnOpen event of the subform. You can copy the form recordset to a new recordset object unless you want to reference the one recordset.

Dim rst As New ADODB.Recordset
Set rst = Me.Recordset
Debug.Print rst(0)
OR
Set rst = Me.RecordsetClone
Debug.Print rst(0)

 

[mapfax]

Thanks for the replies guys.

You are right my syntax I posted was incorrect (mistyped), but I have narrowed my problem down. I thought the above line wasn't working but debuggings shows it does. My problem lies with referencing the fields collection for the recordset to determine the field type. I was getting <ADO could not find the object in the collection...requested by the application> for:

rs.Fields.Item(vFieldname).Type = adChar

This is because (vFieldname) is set to &quot;[Customer number]&quot; rather than &quot;CustomerNumber&quot;.

The white space or brackets causes the problem. I was doing these tests with a test dB, my dB won't having these naming conventions and hopefully these problems.

But I am interested to know how this can written for this scenario without using an index.
i.e. rs.Fields.Item(&quot;[Customer Number]&quot.Type