I need to move next / previous after search a record

paddybear

New Member
Joined
Jun 3, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
Dear all, I have been headache for this 2 functions for many days already. I hope someone can help me to solve my problem.
I have a search button which allow me to search record by using product_id, and display the picture. Then, i wish to move next / previous start from the searched record. I have tried many codes but not work. all move start from first record. 😭😭😭

So far the search and show picture work good for me... but i dunno how to write the code for move next / previous....
the search code i get from online, i tried can use then i just use it. if you want to help me to modify also can, i will really appreciate that.

Additional to that: in my showPic() function, if no picture found, i want to display a default picture, how should i do it?

Rich (BB code):
Private Sub cmdSearch_Click()
    Dim rngID As Range

    Set rngID = Sheets("Main").Range("B:B").Find(txtID, , xlValues, xlWhole, 1, 1, 0)
    
    If Not rngID Is Nothing Then
        txtCategory.Text = rngID.Offset(0, -1).Value
        txtName.Text = rngID.Offset(0, 1).Value
        txtStock.Text = rngID.Offset(0, 2).Value
        txtPrice.Text = rngID.Offset(0, 3).Value
        txtType1.Text = rngID.Offset(0, 4).Value
        txtType2.Text = rngID.Offset(0, 5).Value
        
        txtID.Tag = rngID.Address  'store the location of the matched ProductID
        Call showPic
    Else
        MsgBox StrConv(txtID.Text, vbUpperCase), vbExclamation, "No Match Found"
    End If  

End Sub

Private Sub showPic()
    Dim **** As String   
    **** = ThisWorkbook.Path & "\" & "AP_Folder" & "\" & txtID.Value & ".jpg"
        
    If Dir(****) <> "" Then
        Image1.Picture = LoadPicture(****)
    Else
        Image1.Picture = LoadPicture(none)
    End If

End Sub
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,993
Next what?
Are there multiple occurrences of the same txtID value on the worksheet? If so, that code loads an image based on txtID.Value only, multiple occurrences looks like they would all have the same image.
Or are there multiple txtID's that you want to search for. If so, where is that list of txtID's?
 

paddybear

New Member
Joined
Jun 3, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
Next what?
Are there multiple occurrences of the same txtID value on the worksheet? If so, that code loads an image based on txtID.Value only, multiple occurrences looks like they would all have the same image.
Or are there multiple txtID's that you want to search for. If so, where is that list of txtID's?

right now, when i enter the Product ID and click search button, it will show the product category, name, stock, price..etc for the product.
I need to move to next / previous record start from this searched record.
eg. my ProductID : A100, so next record would be A101
 

Attachments

  • cats.jpg
    cats.jpg
    226.7 KB · Views: 6

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,993
Can you give an example based on that data, of what ID the user might be viewing and which of the others you consider Next or Previous?
 

paddybear

New Member
Joined
Jun 3, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Can you give an example based on that data, of what ID the user might be viewing and which of the others you consider Next or Previous?

Sir, my userform screenshot as below for your reference.
I key-in the Product ID (eg. A100), click Search button. then those info & picture will be displayed.
but this A100 is not the first record in my listing, the first record start from A001.

I got a Next button & Previous button.
I wanna to move to next record (suppose to be A101) to view the info & picture.

I dunno how to refer/tag the search record Product ID, then write coding to move next / previous
 

Attachments

  • cats2.jpg
    cats2.jpg
    83.8 KB · Views: 7

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,993
I think that adding this code would work
VBA Code:
Sub ButNext()
    txtID.Text = myNext(txtID.Text)
    Call cmdSearch_Click
End Sub

Sub butPrevious()
    txtID.Text = myPrev(txtID.Text)
    Call cmdSearch_Click
End Sub

Function myNext(aString As String) As String
    Dim Pointer As Long
    Pointer = Len(aString)
    myNext = aString
    
    Do
        If Mid(myNext, Pointer, 1) = "9" Then
            Mid(myNext, Pointer, 1) = "0"
            Pointer = Pointer - 1
        Else
            Mid(myNext, Pointer, 1) = Chr(1 + Asc(Mid(myNext, Pointer, 1)))
            Pointer = 0
        End If
    Loop Until Pointer <= 0
End Function

Function myPrev(aString As String) As String
    Dim Pointer As Long
    Pointer = Len(aString)
    myPrev = aString
    
    Do
        If Mid(myPrev, Pointer, 1) = "0" Then
            Mid(myPrev, Pointer, 1) = "9"
            Pointer = Pointer - 1
        Else
            Mid(myPrev, Pointer, 1) = Chr(Asc(Mid(myPrev, Pointer, 1)) - 1)
            Pointer = 0
        End If
    Loop Until Pointer <= 0
End Function
 

Tom.Jones

Active Member
Joined
Sep 20, 2011
Messages
340
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

@Mike,

In your VBA code is missing myPrev function
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,993
On my screen, myPrev is the last procedure that is posted. This sites code window might need to be scrolled to see it.
 

Tom.Jones

Active Member
Joined
Sep 20, 2011
Messages
340
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Very strange. I checked the code several times and did not see "myPrev".
I scrolled to the last row and didn't see "myPrev"
Thanks.
 

paddybear

New Member
Joined
Jun 3, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
I think that adding this code would work
VBA Code:
Sub ButNext()
    txtID.Text = myNext(txtID.Text)
    Call cmdSearch_Click
End Sub

Sub butPrevious()
    txtID.Text = myPrev(txtID.Text)
    Call cmdSearch_Click
End Sub

Function myNext(aString As String) As String
    Dim Pointer As Long
    Pointer = Len(aString)
    myNext = aString
   
    Do
        If Mid(myNext, Pointer, 1) = "9" Then
            Mid(myNext, Pointer, 1) = "0"
            Pointer = Pointer - 1
        Else
            Mid(myNext, Pointer, 1) = Chr(1 + Asc(Mid(myNext, Pointer, 1)))
            Pointer = 0
        End If
    Loop Until Pointer <= 0
End Function

Function myPrev(aString As String) As String
    Dim Pointer As Long
    Pointer = Len(aString)
    myPrev = aString
   
    Do
        If Mid(myPrev, Pointer, 1) = "0" Then
            Mid(myPrev, Pointer, 1) = "9"
            Pointer = Pointer - 1
        Else
            Mid(myPrev, Pointer, 1) = Chr(Asc(Mid(myPrev, Pointer, 1)) - 1)
            Pointer = 0
        End If
    Loop Until Pointer <= 0
End Function


YES! YES! YES! 😀😀😀😀😀 This code work perfectly! Now I can move to next / previous record
Thank you very much Mikerickson~~~~ You saved me...
🌹Thank you for helping me to solve this problem🌹
 

Watch MrExcel Video

Forum statistics

Threads
1,130,400
Messages
5,641,924
Members
417,247
Latest member
Chitaah

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top