Subforms/ADODB

[deesheeha]

Hi

Can anyone help me with this code?? I'm having a problem setting my recordset to a subform with my ADODB connection. Heres my code, and my error is on the highlighted line:


Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sql As String
Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=robert-ch; DATABASE=cslogcalls; UID=root;PWD=epos5;OPTION=3"
conn.Open
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
rs.Open "SELECT DesignCode, Range, Module from designs where Range = '" & range & "'", conn, , adLockOptimistic
Forms!frmAddRange!frmSubRange.Form.Recordset = rs

and the error i get it:

Run-time error '438':
Object doesn't support this property or method


Any help at all is much appreciated!

Dee

 

[Remou]

Is frmSubRange the name of the subform control? Sometimes the subform control has a different name to the form contained. You must use the control name, not the name of the form contained.

 

[deesheeha]

yes, this is the control name. i first coded this without using an adodb connection and it worked fine but since adding the adodb connection its not working

 

[deesheeha]

any other ideas??

 

[Remou]

Try

rs.Open "SELECT DesignCode, Range, Module from designs where Range = '" & range & "'", conn, adOpenKeyset, adLockOptimistic
Set Forms!frmAddRange!frmSubRange.Form.Recordset = rs

 

[deesheeha]

no this also didnt work

 

[Remou]

How about trying something stipped down? This works for me:

Private Sub Form_Load()
Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim sql As String
    Set conn = New ADODB.Connection

'Use current connection
    conn.ConnectionString = CurrentProject.Connection '"DRIVER={MySQL ODBC 3.51      Driver}; SERVER=robert-ch; DATABASE=cslogcalls; UID=root;PWD=epos5;OPTION=3"
    conn.Open
    range = "ABC"
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseServer
'Open local table, change to suit    
    rs.Open "SELECT Code, Identifier from Designs where Range = '" & range & "'", conn, adOpenKeyset, adLockOptimistic
'Test recordset
    For Each fld In rs.Fields
    Debug.Print fld.Name
    Next
'Sub form, change to suit
    Set Me.Members_subform.Form.Recordset = rs

End Sub

 

[deesheeha]

That worked!! Excellent. Cheers