CODEWITHSUNDEEP
formsms-accessvba
MJH
MJH

Reputation: 859

appending text into textBox not adding all fields from recordset

I have a form myForm with a text box myText.

In the onLoad function, I have written code that is supposed to append all the rows from a recordset, a database's lockfile, into the myText text box.

What actually happens is, only the first field of four fields is appended into the textbox.

Dim cn As New ADODB.Connection
Dim cn2 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long

cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open "Data Source=Z:\myDatabase.mdb"    
cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=Z:\myDatabase.mdb"

Set rs = cn.OpenSchema(adSchemaProviderSpecific, , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

    Me.myText.SetFocus
    Me.myText.text = ""
    Do While Not rs.EOF            
        Me.myText.text = Me.myText.text & rs.Fields(0) & vbTab & rs.Fields(1) & vbTab & _
        rs.Fields(2) & vbTab & rs.Fields(3) & vbCrLf
        rs.MoveNext
    Loop

I tried

Debug.Print rs.Fields(0) & vbTab & rs.Fields(1) & vbTab & rs.Fields(2) & vbTab & rs.Fields(3)

and all four fields showed in the immediate window.

EDIT: I took out the vbtab, since it's not supported and the file itself already has spaces, and I changed Me.myText.text in 

Me.myText.text = Me.myText.text & rs.Fields(0) & rs.Fields(1) & _
        rs.Fields(2) & rs.Fields(3) & vbCrLf

to Me.myText. Now my code seems to work in the beginning. When I debug step by step the text box fills like it's supposed to, but when Code exits the do while loop, only the first field of the first row shows in the text box.

debug.print Me.myText before exiting the loop shows all the rows. After exiting the loop still shows all the rows. But in the form, the textbox is only showing the first record, first field.

The form and the textbox both do not have any events other than this event, which is on the forms load.

What is wrong?

Upvotes: 0

Views: 248

Answers (1)

M.Hassan
M.Hassan

Reputation: 11032

Use the property .Value (which is the default property for textbox) , not .Text

so Me.myText.Text ==> Me.myText.Value or Me.myText

The textbox support multi line with vbCrLf.

tab with vbTab is not supported

define myvbTab= " " as a tab separator instead of vbTab

Modify the following fragment of your code:

 myvbTab= "    "
dim row_data
 Do While Not rs.EOF    
    row_data = rs.Fields(0) & myvbTab& rs.Fields(1) & _  
    myvbTab & rs.Fields(2) & myvbTab& rs.Fields(3) & vbCrLf        
    Me.myText= Me.myText & row_data
   ' be sure that data is displayed, to know if control characters in row_data
    Debug.Print  row_data
    rs.MoveNext
Loop

Edit:

To isolate the problem can you run that code:(independent of reading recordset)

 Me.myText.Value = "start  "
 Dim tabChar As String

tabChar = Chr(9) '"   " 
Me.myText = Me.myText & "one" & tabChar & "two" & vbCrLf
Me.myText = Me.myText & "three" & tabChar & "four" & vbCrLf

it should display

start    one   two
three  four

Upvotes: 1

Related Questions