Solved ADODB In Memory Recordset

[ADIGA88]

Hi guys,

I am trying to create an in-memory ADO recordset and bind it to a form, while I am able to create and bind the recordset to the form, the textbox will not bind with the recordset field.

Is this possible at all or I am doing something wrong?

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim myForm As Form_Form1
Sub RunMe()

    Set cn = New ADODB.Connection
    cn.ConnectionString = CurrentProject.Connection
    cn.CursorLocation = adUseClient
    cn.Open
    Set rs = New ADODB.Recordset
    rs.Fields.Append "Name", adChar, 20
    rs.Open
    rs.AddNew
    rs.Fields(0) = "Entry 1"
    rs.Update
    rs.AddNew
    rs.Fields(0) = "Entry 2"
    rs.Update
    
    Set myForm = New Form_Form1
    myForm.Visible = True
    Set myForm.Recordset = rs
    
End Sub

Thanks

 

[MajP]

Since Name is a reserved word that could cause some problems. Rename the field to something else and try.

 

[arnelgp]

you don't need Connection there:

'Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim myForm As Form

Sub RunMe()

    'Set cn = New ADODB.Connection
    'cn.ConnectionString = CurrentProject.Connection
    'cn.CursorLocation = adUseClient
    'cn.Open
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.LockType = adLockOptimistic
    rs.Fields.Append "Name", adVarChar, 20
    rs.Open
    rs.AddNew
    rs.Fields(0) = "Entry 1"
    rs.Update
    rs.AddNew
    rs.Fields(0) = "Entry 2"
    rs.Update
    
    rs.MoveFirst
    
    Set myForm = New Form_Form1
    
    With myForm
        !Text0.ControlSource = "Name"
        Set .Recordset = rs
        .Visible = True
    End With
    
End Sub

 

[ADIGA88]

Thank you guys it is working.

first, it wasn't the name I tried others but didn't work.

@arnelgp your code worked as it is, and really is no need for the cn even I forget to pass it to the rs.open. and after playing with your code I found out that the issue was lock type, I didn't have to reset the ControlSource for the textbox or set CursorLocation to be client side to make it work.

Again Thanks

 

[ADIGA88]

a follow up question, why the textbox has a white space after it?

Thanks

 

[arnelgp]

because adChar is "fixed" length string, use adVarChar (variable length) instead.

 

[ADIGA88]

because adChar is "fixed" length string, use adVarChar (variable length) instead.

that is it, thanks