PDA

View Full Version : Next record problem



malarvel
09-09-2016, 10:00 PM
I have a userform for search record in worksheet. I have placed five command button Edit,(next, previous, clear, exit). I have a code for search record in Textbox1 change event. the code is below for reference. It works fine.

Sub GetData()

If IsNumeric(UserForm1.TextBox1.Value) Then
flag = False
i = 0
Empid = UserForm1.TextBox1.Value


Do While cells(i + 1, 1).Value <> ""


If cells(i + 1, 1).Value = Empid Then
flag = True
For j = 2 To 34
UserForm1.Controls("TextBox" & j).Value = cells(i + 1, j).Value
Next j
End If


i = i + 1


Loop


If flag = False Then
For j = 2 To 34
UserForm1.Controls("TextBox" & j).Value = ""
Next j
End If


Else
ClearForm

End If

for example i have searched employee id 104 means i entered 104 in textbox1, it display all the value in all text boxes.

Now I want to move next record from the current record position. I have a code in cmdnext button. The code is below for reference.


Private Sub cmdnext_Click()i = 0
i = i + 1
flag = True
For j = 1 To 34
UserForm1.Controls("TextBox" & j).Value = cells(i, j).Value
Next j

End Sub

Now i have clicked the command button it wouldn't move to next record and no record shown.

I have attached sample data for reference.

kindly help in this task.

offthelip
09-10-2016, 02:39 AM
it is because your cmdnext routine always uses the value of 1 for i, in the search routine you search to find the right value for I before copying the data. So you have the option either save the value for i a public variable, or do the search again with the value from textbox 1 and then increment it.

SamT
09-10-2016, 08:13 AM
That is very good code for a beginner. IMO, you will be a very good programmer when you develop a good Programming Style

There (<<<<<<<<<<<<) are the changes I recommend at this time.


Option Explicit '<<<<<<<<<<<<<<

Dim Empid As Integer, i As Long, j As Long, flag As Boolean '<<<<<<<<<<<<<<<

Private Sub cmdnext_Click()
'i = 0 <<<<<<<<<<<<<<<<<<<<<<<

i = i + 1

'flag = True'<<<<<<<<<<<<<<<<<<<<
For j = 1 To 34
UserForm1.Controls("TextBox" & j).Value = cells(i, j).Value
Next j
End Sub


Sub GetData()

If IsNumeric(UserForm1.TextBox1.Value) Then
flag = False
i = 0
Empid = UserForm1.TextBox1.Value

Do While cells(i + 1, 1).Value <> ""

If cells(i + 1, 1).Value = Empid Then
flag = True
For j = 2 To 34
UserForm1.Controls("TextBox" & j).Value = cells(i + 1, j).Value
Next j
Exit Do '<<<<<<<<<<<<<<<<<<
End If

i = i + 1
Loop

If flag = False Then
For j = 2 To 34
UserForm1.Controls("TextBox" & j).Value = ""
Next j
End If

Else
ClearForm
End If

End Sub

Sub ClearForm()

For j = 1 To 34
UserForm1.Controls("TextBox" & j).Value = ""
Next j

i = 0 '<<<<<<<<<<<<<<
j = 0 '<<<<<<<<<<<<<<
flag = False '<<<<<<<<<<<<<<

End Sub

Sub EditAdd()

Dim emptyRow As Long

If UserForm1.TextBox1.Value <> "" Then
flag = False
i = 0
Empid = UserForm1.TextBox1.Value
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

Do While cells(i + 1, 1).Value <> ""

If cells(i + 1, 1).Value = Empid Then
flag = True
For j = 2 To 34
cells(i + 1, j).Value = UserForm1.Controls("TextBox" & j).Value
Next j
Exit Do '<<<<<<<<<<<<<<<<<
End If

i = i + 1

Loop

If flag = False Then
For j = 1 To 34
cells(emptyRow, j).Value = UserForm1.Controls("TextBox" & j).Value
Next j
End If

End If

End Sub

SamT
09-10-2016, 09:46 AM
For your reading pleasure, I rewrote your code with my Code Style.