Textbox on userform not updating when navigating through records

[ipbr21054]

Morning.
I have a userform where the Textboxes show values for that customer from my worksheet.
I have a command button which is a navigation for the next customer.
Each time i press the command button the next customer is shown & the Textboxes are updated to show the correct values for that customer in question.

I have now added another Textbox for Payment Type
The Textbox in question is Textbox11 & is in column AC

My issue is when i navigate through my customers this new Textbox isnt updating the value like the others.

Does anything spring to mind as i dont see in the code anything out of place & i have changed AB to AC to reflect the new column on my worksheet
This is the code for the Textboxes.

    Me.TextBox1.Value = ws.Range("Q" & rw).Value
    Me.TextBox2.Value = ws.Range("R" & rw).Value
    Me.TextBox3.Value = ws.Range("S" & rw).Value
    Me.TextBox4.Value = ws.Range("T" & rw).Value
    Me.TextBox5.Value = ws.Range("U" & rw).Value
    Me.TextBox6.Value = ws.Range("V" & rw).Value
    Me.TextBox7.Value = ws.Range("W" & rw).Text
    Me.TextBox11.Value = ws.Range("AC" & rw).Text

On my worksheet i double click the customers name,the userform opens & i see all the Textboxes with all the values shown.
Textbox11 of which is payment type shows CASH as that was the way this customer paid.

Lets just say all the other customers in my database paid using via BANK
So on the userform pressing the command button shws the next customers records.
Textbox11 should now be showing BANK but it still shows CASH

I continue to press the command button loading each customer BUT the value still shows CASH

Below is the navigate code.

Sub Navigate(ByVal Direction As XlSearchDirection)
    Dim i As Integer
    Dim lastRow As Long
    
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    r = IIf(Direction = xlPrevious, r - 1, r + xlNext)
    
    'ensure value of r stays within data range
    If r < startRow Then r = startRow
    If r > lastRow Then r = lastRow
    
    'get record
    For i = 1 To UBound(ControlNames)
         Me.Controls(ControlNames(i)).Text = IIf(Direction = xlNone, "", ws.Cells(r, i).Text)
        
    Next i
    
    Me.Caption = "Database"
    
    'set enabled status of next previous buttons
    Me.NextRecord.Enabled = IIf(Direction = xlNone, False, r < lastRow)
    Me.PrevRecord.Enabled = IIf(Direction = xlNone, False, r > startRow)
    
    EventsEnable = False
    Me.ComboBoxCustomersNames.ListIndex = IIf(Direction = xlNone, -1, r - startRow)
    EventsEnable = True


End Sub

 

[RoryA]

Why do you need the first piece of code when the Navigate routine is clearly set up to populate controls with the relevant text? I suspect that is the root of your problem - perhaps you didn't add the new control name to the ControlNames array.

 

[ipbr21054]

I have since found this in a module & when i change say Textbox 12 & Textbox 13 around i see the values also swap on the userform fields

Function ControlNames() As Variant
ControlNames = Array("txtCustomer", "txtRegistrationNumber", "txtBlankUsed", "txtVehicle", _
                    "txtButtons", "txtKeySupplied", "txtTransponderChip", "txtJobAction", _
                    "txtProgrammerCloner", "txtKeyCode", "txtBiting", "txtChassisNumber", _
                    "txtJobDate", "txtVehicleYear", "txtPaid", "txtInvoiceNumber", "TextBox1", _
                    "TextBox2", "TextBox3", "TextBox4", "TextBox5", "TextBox6", "TextBox7", "TextBox11", "TextBox12", "TextBox13", "TextBox14", "txtPinCode")
End Function

 

[RoryA]

TextBox11 is at position 24 in that array, so it's going to be updated with values from column X, not column AC.

 

[ipbr21054]

Correct it shows values from Column X

The array being the code i just found correct
Im at a mental bloke so trying to work out which TextBox in the array needs to go where.

 

[ipbr21054]

Got it

Each value in array represents the columns in order

 

[ipbr21054]

Why do you need the first piece of code when the Navigate routine is clearly set up to populate controls with the relevant text? I suspect that is the root of your problem - perhaps you didn't add the new control name to the ControlNames array.

Ok figured it out.
The first section of code is ONLY for when the user double clicks the customer on the worksheet.

The second piece of code if for when the user then uses the navigations buttons.

On my worksheet there are 2 columns that i dont need to show values on the userform.
These 2 columns are Y & Z
I couldnt work out why the values were in the wrong Textboxes.
So as a test i added 2 etc Textboxes for these 2 columns.
I altered the array of which took ages to find in the Module section.
Now when i use the navigation buttons all values are in the correct Textbox.

I hate this array being in a module,
1,It took ages to find.
2,Dont see how the code relates to it, probably why i didnt know it was in the Module section then going through each 1 by 1
3,I dont want the 2 fields on my userform if not needed, so at present they are hidden so the form works.

I think i will start a new post & see about changing the array so its on the code page.
Can you advise where in the code it calls this array please.

Thanks

 

[RoryA]

Here:

    For i = 1 To UBound(ControlNames)
         Me.Controls(ControlNames(i)).Text = IIf(Direction = xlNone, "", ws.Cells(r, i).Text)
        
    Next i

 

[ipbr21054]

Should i start a new post about the code being on the sheet as opposed a module or can you assist here ?
Just easy to have it like.

Me.txt4Address.Value = ws.Range("U" & rw).Value

Then i can see straight away what Textbox will show what value from the worksheet.

 

[RoryA]

It's up to you what works best for you. You could have a table on a sheet with the control names in one column and the column letters in another, then simply look them up, or you could just use the Tag property of each control to store the column letter that it should get data from.