Find Next Record Command Button

[aearce2000]

This is a command button meant to take me to the next record that has the same value as ComboBox17. TextBox1 is just an index with all unique values that is located on the far right column of my table that I use to keep tabs on which record I am on. I had it working for a minute, but it must not have saved my changes and I can’t get it right again. The first time I press the button, everything appears to work properly, but then the second time the d value (record number) does not change.

Do you see why that might be?

Please Login or Register  to view this content.

[Tinbendr]

I got it to work like this.

Please Login or Register  to view this content.

But a 'feature' of Range Find is that it starts looking on row two of the range you give it. Since you are using named ranges that do not include the headers, range find will start looking at actual row 3 of the filtered range. You could adjust the named ranges to include the headers.

Same goes for the Next Record button.

You'd have to subtract one row from D.Address to get the next record.

Might I ask, why so many comboboxes? Will this eventually be used to input records?

Normally in a situation like this, I'd use dependent comboboxes. (I would do away with Userform2.) In the initialize, I would stuff the Group names in the Group combobox. Once the user selected one of those, I'd stuff the Geography combobox with only Group names from combobox1. Once Geography is selected stuff all the departments, and do one until you get to the rLead # you want. Then you could navigate through those with a spinbutton. The trick is, once you get down to the lead combobox, you will also stuff the row # were the lead came from in column 2 of the combobox. Then, as you iterate though the lead, you already have the row number where the record came from. Then it's just a matter of passing the row number to the event code.

Each remaining bits of the record can be shown in Labels (or textboxes if you want to edit them) instread of comboboxes.

[aearce2000]

Thank you so much! I am using this as a form for inputting records, as well as for filtering. UserForm2 is used so that each department only sees their own records (this isn't a security thing as much as a practicality thing or else I'd use something more secure).

I haven't used dependent comboboxes yet, but that sounds like a simpler way to code this form than what I am currently doing. I will definitely look into them. Thank you!!

[6StringJazzer]

But a 'feature' of Range Find is that it starts looking on row two of the range you give it. Since you are using named ranges that do not include the headers, range find will start looking at actual row 3 of the filtered range. You could adjust the named ranges to include the headers.

The textbook method would be to use the After argument and specify the last cell of the range. Find wraps around so specifying After:=<last cell>, it will start its search in the first cell. This avoids searching cells that you don't really want to be included in the search.

[deucekhk]

I have a similar issue with a userform with 20 textboxes (locked to user) and a Next command button I'm trying to code. There is 12 pieces of equipment each with a command button that directly opens the userform displaying the 20 specs for the specific device. I want the user to be able to click any device command button to open userform with it's data then cycle through the list if they want. So far all command buttons work but hitting Next just displays blanks. Here is what the code looks like:
' For one Device command button
Sub SystemSettingsMain()
'assigns textboxes the main campus' array values and launches userform1
systemsettingscounter = 5
UserForm1.TextBox_ECN = Sheet4.Range("B" & systemsettingscounter)
UserForm1.TextBox_KNum = Sheet4.Range("C" & systemsettingscounter)
UserForm1.TextBox_AETitle = Sheet4.Range("D" & systemsettingscounter)
UserForm1.TextBox_SW = Sheet4.Range("E" & systemsettingscounter)
UserForm1.TextBox_IP = Sheet4.Range("F" & systemsettingscounter)
UserForm1.TextBox_Gateway = Sheet4.Range("G" & systemsettingscounter)
UserForm1.TextBox_Subnet = Sheet4.Range("H" & systemsettingscounter)
UserForm1.TextBox_PCSN = Sheet4.Range("I" & systemsettingscounter)
UserForm1.TextBox_Worklist = Sheet4.Range("J" & systemsettingscounter)
UserForm1.TextBox_PACS = Sheet4.Range("K" & systemsettingscounter)
UserForm1.TextBox_WAP_IP = Sheet4.Range("L" & systemsettingscounter)
UserForm1.TextBox_SSID = Sheet4.Range("M" & systemsettingscounter)
UserForm1.TextBox_Passphrase = Sheet4.Range("N" & systemsettingscounter)
UserForm1.TextBox_Pwr_Lvl = Sheet4.Range("O" & systemsettingscounter)
UserForm1.TextBox_Channel = Sheet4.Range("P" & systemsettingscounter)
UserForm1.TextBox_DRXsn = Sheet4.Range("Q" & systemsettingscounter)
UserForm1.TextBox_DRX_IP = Sheet4.Range("R" & systemsettingscounter)
UserForm1.TextBox_Sensitivity = Sheet4.Range("S" & systemsettingscounter)
UserForm1.TextBox_FW = Sheet4.Range("T" & systemsettingscounter)
UserForm1.TextBox_DEC = Sheet4.Range("U" & systemsettingscounter)
UserForm1.TextBox_Net_Speed = Sheet4.Range("V" & systemsettingscounter)
UserForm1.Show
End Sub

and for the Next button:

Private Sub CommandButton2_Click()
'uses counter to cycle through system settings rows
systemsettingscounter = systemsettingscounter + 1

TextBox_ECN.Value = Sheet4.Cells(systemsettingscounter, "B")
TextBox_KNum.Value = Sheet4.Cells(systemsettingscounter, "C")
TextBox_AETitle.Value = Sheet4.Cells(systemsettingscounter, "D")
TextBox_SW.Value = Sheet4.Cells(systemsettingscounter, "E")
TextBox_IP.Value = Sheet4.Cells(systemsettingscounter, "F")
TextBox_Gateway.Value = Sheet4.Cells(systemsettingscounter, "G")
TextBox_Subnet.Value = Sheet4.Cells(systemsettingscounter, "H")
TextBox_PCSN.Value = Sheet4.Cells(systemsettingscounter, "I")
TextBox_Worklist.Value = Sheet4.Cells(systemsettingscounter, "J")
TextBox_PACS.Value = Sheet4.Cells(systemsettingscounter, "K")
TextBox_WAP_IP.Value = Sheet4.Cells(systemsettingscounter, "L")
TextBox_SSID.Value = Sheet4.Cells(systemsettingscounter, "M")
TextBox_Passphrase.Value = Sheet4.Cells(systemsettingscounter, "N")
TextBox_Pwr_Lvl.Value = Sheet4.Cells(systemsettingscounter, "O")
TextBox_Channel.Value = Sheet4.Cells(systemsettingscounter, "P")
TextBox_DRXsn.Value = Sheet4.Cells(systemsettingscounter, "Q")
TextBox_DRX_IP.Value = Sheet4.Cells(systemsettingscounter, "R")
TextBox_Sensitivity.Value = Sheet4.Cells(systemsettingscounter, "S")
TextBox_FW.Value = Sheet4.Cells(systemsettingscounter, "T")
TextBox_DEC.Value = Sheet4.Cells(systemsettingscounter, "U")
TextBox_Net_Speed.Value = Sheet4.Cells(systemsettingscounter, "V")
End Sub


It seems like the text boxes aren't refreshing with the new values once assigned. I'm sure there is an easier way to go about this, like a lookup/find function etc. any help would be appreciated

[Tinbendr]

Welcome to the board!

Unfortunately, you have broken forum rule #2.

Please start your own thread and link back to this one.

But, in short, you'll have to make systemsettingcounter global Or store the value somewhere as it's value is lost at the end of each Sub.