bind field to adodb.recordset field

[gem1204]

Hello,
I trying to create an adodb record set, set recordset as the form recordset then bind one of the fields to a text box. When I ran the code it apparently worked because the navigation showed that there were seven records, but the text box I tried to bind the field to showed # error. Can someone explain to me what I doing wrong?

Set MyCnn = New ADODB.Connection
Set MyCnn = CurrentProject.AccessConnection
Dim rst As New ADODB.Recordset
rst.Fields.Append "Field1", adVarChar, 50
rst.Fields.Append "Field2", adVarChar, 50
rst.Fields.Refresh
'Create Recordset
rst.Open
'Add rows into recordset
rst.AddNew Array("field1", "field2"), Array(" A", "val1")
rst.AddNew Array("field1", "field2"), Array("string2", "val2")
rst.AddNew Array("field1", "field2"), Array("string2", "val2")
rst.AddNew Array("field1", "field2"), Array("string2", "val2")
rst.AddNew Array("field1", "field2"), Array("string2", "val2")
rst.AddNew Array("field1", "field2"), Array("string2", "val2")
rst.AddNew Array("field1", "field2"), Array("string2", "val2")
Set Me.Recordset = rst
'Me.txt1.ControlSource = "=[" & rst.Fields(0).Name & "]"
rst.MoveFirst
MsgBox rst.Fields(0).Name 'This shows the field value of the second record
'when I bind the field I get #Error, I get the same result when I set the control source to field1 in design view
Me.txt1.ControlSource = "=[" & rst.Fields(0).Name & "]"

[orange]

I'm not sure what exactly you are trying to do. Perhaps you could describe your intentions more clearly.

For info on ADO tables and recordsets
http://allenbrowne.com/func-ADOX.html#CreateTableAdox
http://allenbrowne.com/func-ADO.html

[gem1204]

I thought I explained you intentions fairly clearly but I guess not, so I will list them

Purpose:
Create an adodb record set in code, add fields to the record set, add records to the data set then then set the record set of a form to the record set I created. The purpose is only for learning purposes. Instead of creating a record set with an sql statement I wanted to create one from scratch and learn how to add the fields, then add the records to the record, set then set the record set for the form and finally to set the control sources of the text boxes on the form to the fields of the record set I created. My only intention is to learn to write the code. I don’t need any advice on long term goal I might have suggesting a better way to achieve some long term objective . MY ONLY OBJECTIVE IS TO WRITE THE CODE TO CREATE THE RECORDSET.

My Accomplishments:
I created the record set
I added fields to the record set
I added records to the record set
I set the record set for the form to the record set I created. I know I was successful because after I ran the code the navigation bar showed seven records, the number of records I created.

My Failures:
I could not set the control source of the text box to a field from the record set. When I added the field name as the control source (Field1) I got the error #Error after running the code. When I tried to set the control source of the text box in code using ‘Me.txt1.ControlSource = "=[" & rst.Fields(0).Name & "]" I got the same error. I used this syntax before when setting a control source for a text box from a field in a record set I created using an sql statement.

My investigation:
I thought maybe I was doing something wrong and maybe the fields weren’t being saved. I used a message box, MsgBox rst.Fields(0).Value, I got the value of first field in the record set so I know the fields and data are there.

What I need:
I would like someone to look at my code and tell me what I’m doing wrong when trying to set the control source of my text box Txt1.

Again my only questions is how do I set the control source to my text box.

Here is my code. I have bolded the text in my code to show where I'm having a problem

Dim rst As New ADODB.Recordset
'rst.Open "Select * from [##TblDataBaseObjects]", Me.MyCnn
'Add columns
rst.Fields.Append "Field1", adVarChar, 50
rst.Fields.Append "Field2", adVarChar, 50
rst.Fields.Refresh
'Open the recordset
rst.Open
'Add rows into recordset
rst.AddNew Array("field1", "field2"), Array(" A", "val1")
rst.AddNew Array("field1", "field2"), Array("string2", "val2")
rst.AddNew Array("field1", "field2"), Array("string2", "val2")
rst.AddNew Array("field1", "field2"), Array("string2", "val2")
rst.AddNew Array("field1", "field2"), Array("string2", "val2")
rst.AddNew Array("field1", "field2"), Array("string2", "val2")
rst.AddNew Array("field1", "field2"), Array("string2", "val2")
'Set the recordset of the form to recordset just created.
Set Me.Recordset = rst
'Set the control source of the first text box to the name of the first field
'from the recordset
Me.txt1.ControlSource = "=[" & rst.Fields(0).Name & "]"

[orange]

A text box would exist on a form. Where did you create the form?

The Me. works for controls on forms.

What is Array, did you Dim it somewhere?
Where do you actually run this code?

I think there is a lot you aren't showing in your post. We can only respond to what we see.

Did you look at the Allen Browne links I sent?

I have Acc2003, not 2007.

[gem1204]

Of course ‘me’ refers to a form. I mentioned that in My Purpose section that I laid out in my second post. I showed a lot more detail in my second post as you requested and you still missed the point! If you had read my second post carefully you would have known that I was creating a form and wouldn’t have wondered about why I was using ‘me’.

Where the array came form is irrelevant to my question. As I explained in the My Accomplishments section, I created the record set, added the fields, added the records and bound the record set to the form.

In my failures section my question was ‘How do I set the control source of the text box?’ . I have a record set with seven records; how the records got in the record set have nothing to do with how to set the control source of the text box. So where the array come from is irrelevant. This code would work in any version of access except for the text box. Any one with access could create a form with a text box named txt1, a button and copy the code and it should work…except for the text box. Of course you would have to have a reference to ADO if one didn’t already exist.

I looked at the links and none of them had nothing to do with setting the control source of a text box to field from a record set.

You are trying to read to much into the question.

[RuralGuy]

Orange is just trying to help. Please don't bite his head off just because he does not yet understand your explaination.

[gem1204]

You are right RuralGuy, I guess I was a little frustrated with Orange so “Orange – I apologize, I know you were just trying to help.”

If he doesn’t understand my question by now he never will.

I may have been a little frustrated in my previous post but what else was I supposed to do? I’m smart enough to know when I’ve provided enough information to get my question answered. Orange wanted even more information and giving him more information would have been pointless – there was no more information to provide. I don’t know how else I could have explained it.

You had administrator under you name so I assume you might be an administrator of the forum. As an administrator I’m sure you don’t want threads that go on and on without getting resolved. You were correct in admonishing me but I hope you do understand my frustration.
GEM

[JTassoff]

I had the same problem. The solution seems to be to set the locktype and cursorlocation. My code works after the change.

Dim rs As ADODB.Recordset
Set rs = CreateObject("ADODB.Recordset")
rs.LockType = adLockPessimistic
rs.CursorLocation = adUseServer

Hope this helps,
JT