I need to move next / previous after search a record

[paddybear]

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?

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

 

[mikerickson]

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]

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

 

[mikerickson]

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]

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

 

[mikerickson]

I think that adding this code would work

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]

@Mike,

In your VBA code is missing myPrev function

 

[mikerickson]

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]

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]

I think that adding this code would work

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?