CODEWITHSUNDEEP
vbams-access-2010
user3742015
user3742015

Reputation: 1

How to search a record using find next method

I have multiple records with the same customer number and I using Find next method to search for the next record with the customer number is same. my code will only search for the 2nd record and not go for the 3rd or 4th search for the same customer number. below is the code can you please help

Private Sub Command114_Click()
    Dim db As dao.Database
    Dim rs1 As dao.Recordset
    Dim pn As Long
    Set db = CurrentDb()
    Set rs1 = db.OpenRecordset("Application", dbOpenDynaset)
    If (Text85 & vbNullString) = vbNullString Then
        MsgBox "Please enter the Account no/CIF"
    Else
        pn = Me.Text85.Value
        rs1.FindNext "[Cus_Number] = " & pn
        If rs1.NoMatch Then
            MsgBox ("Sorry The Accountno/CIF is not found")
        Else
            Me.S_No = rs1.Fields("sno").Value
            Me.Cus_Name = rs1.Fields("Cus_Name").Value
            Me.App_level1 = rs1.Fields("App_level1").Value
            Me.App_level2 = rs1.Fields("App_level2").Value
            Me.App_level3 = rs1.Fields("App_level3").Value
            Me.Dec_level1 = rs1.Fields("Dec_level1").Value
            Me.Dec_level2 = rs1.Fields("Dec_level2").Value
            Me.Dec_level3 = rs1.Fields("Dec_level3").Value
            Me.Com_level1 = rs1.Fields("Com_level1").Value
            Me.Com_level2 = rs1.Fields("Com_level2").Value
            Me.Com_level3 = rs1.Fields("Com_level3").Value
            Me.Date1 = rs1.Fields("Date1").Value
            Me.Date2 = rs1.Fields("Date2").Value
            Me.Date3 = rs1.Fields("Date3").Value
        End If
    End If
        rs1.FindNext "[Cus_Number] = " & pn
        Set rs1 = Nothing
End Sub

Upvotes: 0

Views: 1733

Answers (1)

VBlades
VBlades

Reputation: 2251

I am assuming the functionality you want is to change all instances (2, 3, 4 etc.) to the values entered. Remove this (the one near the end, after the End If):

rs1.FindNext "[Cus_Number] = " & pn

and put the FindNext into a loop which will keep on finding and updating your records until there is NoMatch:

    rs1.FindNext "[Cus_Number] = " & pn

    If rs1.NoMatch Then
        MsgBox ("Sorry The Accountno/CIF is not found")
    Else
        Do Until rs1.NoMatch
            Me.S_No = rs1.Fields("sno").Value
            Me.Cus_Name = rs1.Fields("Cus_Name").Value
            Me.App_level1 = rs1.Fields("App_level1").Value
            Me.App_level2 = rs1.Fields("App_level2").Value
            Me.App_level3 = rs1.Fields("App_level3").Value
            Me.Dec_level1 = rs1.Fields("Dec_level1").Value
            Me.Dec_level2 = rs1.Fields("Dec_level2").Value
            Me.Dec_level3 = rs1.Fields("Dec_level3").Value
            Me.Com_level1 = rs1.Fields("Com_level1").Value
            Me.Com_level2 = rs1.Fields("Com_level2").Value
            Me.Com_level3 = rs1.Fields("Com_level3").Value
            Me.Date1 = rs1.Fields("Date1").Value
            Me.Date2 = rs1.Fields("Date2").Value
            Me.Date3 = rs1.Fields("Date3").Value

            rs1.FindNext "[Cus_Number] = " & pn
        Loop
    End If

In general, though, I'm not sure what you are looking to do. Are you looking to update the recordset with the latest information on the form? The code you have will overwrite the current values on the form with the last set of found values in the recordset. I would have thought you want the opposite...

Upvotes: 1

Related Questions