CODEWITHSUNDEEP
ms-accessvba
Rob Hughes
Rob Hughes

Reputation: 11

MS Access textbox update/append

I have some code which populates a text box called text5 Forms!Form3!text5. But everytime I click on the button which updates this text box it refreshes it.

I would like it so the text stay fixed, and add the new data everytime the button is clicked. I have attached the code, that I have done so far

Option Compare Database

Private Sub Command0_Click()

  Set db = CurrentDb

  Dim I As Integer
  Dim varNumber As Integer  ' this takes the number for how many times to loop
  Dim strQueryName As String  ' this is for the sql to find lowest rack number
  Dim P  As Integer  'this value is the prod number
  Dim x As Integer   'value from lowestrackSQL



  varNumber = Me.Quantity 'box from form me means this form
  prodnumber = Me.ProdNo  'box from form

  strQueryName = "SQLToFindLowestRackNumber"   'this will be used to execute the query


  strSQL = CurrentDb.QueryDefs(strQueryName).sql   ' this stores the sql but does not run it

  Forms!form3!txtPrint = strResult
  'Stop

  For I = 1 To varNumber  ' uses the quntity value to count how many times to loop

    x = DLookup("locationrack", strQueryName)  'puts value of query into value x
    prod# = prodnumber

    'below puts into imediate view box
    Debug.Print "Line number = " & I; ";  Rack Location = " & x; ";   Product Number = " & prod#; ";"
    'below puts it into form3 text box
    strResult = strResult & " Line Number = " & I & " Rack Location = " & x & "   Product Numner = " & prod# & vbCrLf & ""
    Forms!form3!Text5 = strResult


    'below executes the SQL
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE [Location] SET [Location].ID = 0 WHERE [Location].RackID =" & x
    DoCmd.SetWarnings True

 Next I



End Sub

As you can see the value of strResult is passed to the text box and I just want to keep adding the value to the text box even after I restart the loop again.

Upvotes: 1

Views: 7651

Answers (2)

Ghost
Ghost

Reputation: 2226

Try declaring strResult outside of the loop with your other variables:

Dim I As Integer
Dim varNumber As Integer  ' this takes the number for how many times to loop
Dim strQueryName As String  ' this is for the sql to find lowest rack number
Dim P  As Integer  'this value is the prod number
Dim x As Integer   'value from lowestrackSQL
Dim strResult as String

As it is I think it clears it every time because its scope is limited to within the for loop

Also, where you say 

Forms!form3!txtPrint = strResult

Add underneath

strResult = Forms!form3!text5

Upvotes: 1

Christian Specht
Christian Specht

Reputation: 36421

Forms!Form3!Text5 = strResult

...overwrites the content of the text box with the value of the variable.

To append the value instead of overwriting it, you need to do this:

Forms!Form3!Text5 = Forms!Form3!Text5 & strResult

Upvotes: 1

Related Questions