problem with returning multiple values from a query to a text box!

[jeremypaule]

I have restarted my previous thread because I pretty much have a whole new question, thanks to gemma-the-husky for helping me with the code!

What this code does is goes through the values in a query, makes them strings, and outputs them to a text box. Although I have a problem, when the code runs through the while statement the tempstrg overwrites itself, so it’ll ONLY output the last values of my query instead of ALL the values in my query. So, how can I make it repetitively add ALL the data into one string to output into the textbox. Let me know if I need to make my question clearer! Thanks!!!

These screenshots of my form will help you see how I’m trying to output into the textbox. The text box has the label Customers over it. The first ss shows how I want it to output, The second screen shot shows what it is outputting. (Also, when I run the debugger it shows that it reads all of the values, but it just writes over it with the next values)
http://i14.tinypic.com/2d9prnk.jpg
http://i14.tinypic.com/3584f9y.jpg

Private Sub but3_Click()

Dim rst As Recordset
Dim tempstrg As String 'use a tempstring to hold the results

tempstrg = ""
Set rst = CurrentDb.OpenRecordset("Customer Variance")
rst.MoveFirst ' rst now contains first record from the query

'the next bit will gradually insert terms from Customer Variance query into a single text string

While Not (rst.EOF)
tempstrg = rst!Account & " "
tempstrg = tempstrg & rst!VAR & " "
tempstrg = tempstrg & vbCrLf 'this will insert a line feed to format the result string a bit clearer - can format these contents any way you need.

rst.MoveNext

Wend 'end while

rst.Close 'close recordset when you are done

'now the variable tempstrg contains the foramtted text

CUSTOMERS = tempstrg 'display the tempstrg in the textbox. (or do anything else you want to do with it)

End Sub

 

[gemma-the-husky]

do you mean you have several customers, and you want to produce a text box with all the results.

if so

have a module variable

dim fullstrg as string

then instead of customers=tempstrg
fullstrg = fullstrg & tempstrg
or even fullstrg = fullstrg & vbcrlf & tempstrg to format it better

when all the customers have been checked, then say

customers = fullstrg

you could immediately say

customers = customers & tempstrg, but this might cause the customers text box to flicker as it keeps getting rewritten

 

[jeremypaule]

thanks i love you.. lol .. im finally done w/ this database

 

[jhomeyer]

Wow! Thank you for this. This solved my problem which is in a separate thread.