Navigation code doesnt show all records on userform

[ipbr21054]

Hi,
I open up my userform & see the customers name along with his records.
I click on Next Record which then shows the next customer as expected,i can do this for the next & the next
BUT
The values in Textboxes 2-7 dont change.
Example.
Customer is Tom Jones Textboxes 2-7 show Green Green Grass Of Home.
I select Next Record who is Elvis Presley & i still see Gren Green Grass Of Home.

Textboxes 2-7 dont alter.

I looked at the code for the Next Record button which just shows this.

Private Sub NextRecord_Click()
    Navigate Direction:=xlNext
End Sub

But i did also find this.

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

My problem is i am unable to locate the code for where lets say i need to change row A-T for A-W etc etc to populate "maybe not correct word" but maybe the range to look at is incorrect ?

Thanks

 

[dmt32]

The Navigation code is referring to control names in an array

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

If you have expanded the number of controls (textboxes?) in your userform then you need to include them (in their correct order) in the array for this code to work

Dave

 

[ipbr21054]

I did see that but i looked on because i have a TextBox1 which i see on the userfoam no problem.
I didnt see TextBox1 there so i kept looking.

You are correct in that ive added TextBox 2-7 now.
Can you then show me TextBox1 in the code.

I will search on how to add the others

 

[dmt32]

I did see that but i looked on because i have a TextBox1 which i see on the userfoam no problem.
I didnt see TextBox1 there so i kept looking.

You are correct in that ive added TextBox 2-7 now.
Can you then show me TextBox1 in the code.

I will search on how to add the others

You have a Function called ControlNames - In the function is an Array listing the control names - You need to add your additional textbox controls to the array in the function.
Do be aware that when adding controls, they must be in the correct order in relation to their place (column) in the worksheet.

Dave

 

[ipbr21054]

Well thats easy as shwon below
TextBox2 = Column R
TextBox3 = Column S
TextBox4 = Column T
TextBox5 = Column U
TextBox6 = Column V
TextBox7 = Column W

Just need to work out how to add them in now

 

[ipbr21054]

So like this ???

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")
End Function

 

[dmt32]

Looks right, just need to try it
Navigation code should self adjust based on the Ubound of the array.

Dave

 

[ipbr21054]

Yes it works & thanks.

I was searching around on how to do it as i was stuck and came across this.

Database form with two methods to select data

I have a simple vba form for showing the customer database with First, Previous, Next and Last Buttons in the bottom of the form for de-/incrementing the database row number to display each customer’s data (20 columns). But I want to be able to also choose the actual customer in the form via its...

www.mrexcel.com

Base 1 jogged my memory & remembered where it was.

Thanks

 

[dmt32]

Yes it works & thanks.

I was searching around on how to do it as i was stuck and came across this.

Base 1 jogged my memory & remembered where it was.

Thanks

I recognised code as one of mine - Array has lbound = 0 Base 1 changes it to lBound = 1.

glad you resolved

Dave