Need help with For statement 2

[access101]

I have a continuous subform with 5 text boxes.
text1, text2,text3, text4, text5

I created an unbound text box on the main form that combines the results of the 5 subform text boxes and displays them using vba.
me.textbox =form_subform.text1 & text2 & text 3 etc...

I need a For or a Do statement that will display the other rows in my subform. Any help would be great.

 

[dhookom]

Are you suggesting these text boxes on the subform aren't bound to a table? Do you want the text to display records from one record in the subform or multiple? Are these text values or numbers? What type of values are you displaying?

Duane
Hook'D on Access
MS Access MVP

 

[access101]

the textboxes on the subform are bound to a table. and i would like the textbox to display multiple records the values are text.

 

[dhookom]

Is this the same question as your other post in this forum? Did you check faq701-4233.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

You do not need any code. You need a calculated control.
1)Put an unbound control on your subform
2)make its recordsource
= [text1] & Chr(13) & Chr(10) & [text2] & chr(13) & chr(10) & [text3].....

 

[access101]

no what i need is a loop or a for statement that will go through all the records in the table.

 

[access101]

so i would need = [text1] & Chr(13) & Chr(10) & [text2] & chr(13) & chr(10) & [text3] looped to show the other rows in my table

 

[MajP]

You need to spend a couple minutes and articulate a clear concise question.
Are you saying you want a single textbox in the main form that has the concatenated values for 5 fields for all records in the subform. Something like:

Record1 Field1
Record1 Field2
Record1 Field3
Record1 Field4
Record1 Field5
Record2 Field1
Record2 Field2
Record2 Field3
Record2 Field4
Record2 Field5
...
Recordn Field5

If so why? If I was attempting to do that at a minimum I would put that into a listbox. I cannot imagine, how you would use that information in a textbox.



If you are trying to show this in a continous form

dog cat bird Plane dog
cat
bird
Plane

dog cat fish bug dog
cat
fish
bug

then my post is correct. I am not the only one possibly confused because I think Duane is thinking something even a different possibility than these two possibilities.

Any chance this is a homework assignment? Because it makes little sense for real use.

 

[TheAceMan1]

[blue]I've been a member since Sep 4, 2010, ...[/blue]

Isn't [red]Sep 4, 2010[/red] right around the start of school?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[access101]

Just looking to get a For statement that will loop through a recordset in a query. So when I click the unbound textbox on my form the textbox will populate with all the records in my query. The query i have has only one column(Expr1) with multiple records.

vba
me.unboundtext = dlookup("expr1", "query1")

I know the code above won't work because it will only show me the first record in my query but I'm not sure how to write the loop.

I hope i gave a better explanation.

 

[dhookom]

Did you try my suggestion from 14 Sep 10 22:18?

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Duane's link shows how. But basically something like

dim rs as dao.recordset
set rs = me.recordsetclone
dim strOut as string
do while not rs.eof
strOut = strOut & rs!somefield & chr(13) & chr(10)
rs.movenext
loop

 

[TheAceMan1]

access101 . . .

Perhaps the following code in a button on the mainform will do:

[blue]   Dim rst As DAO.Recordset, Ctl As Control
   Dim NL As String, DL As String
   
   Set Ctl = Me.Controls("[purple][b]MainFormTextBoxName[/b][/purple]")
   Set rst = [[purple][b]subFormName[/b][/purple]].Form.RecordsetClone
   NL = vbNewLine
   DL = NL & NL
   Ctl = Null
   rst.MoveFirst
   
   Do Until rst.EOF
      Ctl = Ctl & rst![purple][b]TextboxName1[/b][/purple] & NL & _
                  rst![purple][b]TextboxName2[/b][/purple] & NL & _
                  rst![purple][b]TextboxName3[/b][/purple] & NL & _
                  rst![purple][b]TextboxName4[/b][/purple] & NL & _
                  rst![purple][b]TextboxName5[/b][/purple] & DL
      rst.MoveNext
   Loop
   
   Set ctl = nothing
   Set rst=nothing[/blue]

[blue]Your Thoughts? ...[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[access101]

Thanks for the help! It works great