Problem in a recordset with the next one

[nilses]

Hello,

I have a problem with a recordset. I have a form with a lot of fields. I can find the first item and i can find the next one. My problem is the next one.

1/ I find the first serial number like 0. I'am looking for the first item in my table with the serial number like 0 with the RI number like 1514. (I have no problem)

2/ I find a record with the serial number like 0 but i would like the next on after the RI number 1514 like 1517. My code doesn't run here and i can't find my error.

This is my code:

Private Sub Suivant_Click() 
On Error Resume Next 
    Dim rs As DAO.Recordset, strSQL As String, db As DAO.Database 
    Dim NumeroSerie As Variant 
    Dim NumeroRI As Variant 
    
    Set db = CurrentDb 

    NumeroSerie = Me.ZtxtNumSerie.Value 
    NumRI = Me.ZtxtNumRI.Value 
        
    strSQL = "SELECT * FROM BaseDeDonnees WHERE NumSerie LIKE '" & Me.ZtxtNumSerie.Value & "' ORDER BY NumRI" 
    
    Set rs = db.OpenRecordset(strSQL) 
    
    Debug.Print strSQL 
    
    While NumeroRI <> rs("NumRI") 
        rs.MoveNext 
        If CBool(Err.Number) Then 
            Err.Clear 
            Exit Sub 
        End If 
    Wend 

    rs.MoveNext 
    If CBool(Err.Number) Then 
        Err.Clear 
        Exit Sub 
    End If 

    With Me 
    .ZtxtNumSerie = rs.Fields("NumSerie") 
    .ZtxtNumRI = rs.Fields("NumRI") 
    .ZtxtNumSV = rs.Fields("NumSV") 
    .ZtxtTechnicienLabo = rs.Fields("TechnicienLabo") 
    .ZtxtModule = rs.Fields("Module") 
    .ZtxtReference01 = rs.Fields("RefPieces1") 
    .ZtxtDesignation01 = rs.Fields("Designation1") 
    .ZtxtQte01 = rs.Fields("Qte1") 
    .ZtxtPrixHT01 = rs.Fields("PrixHT1") 
    .ZtxtPrixTOTALHT01 = rs.Fields("PrixPieces1TotalHT") 
    .ZtxtReference02 = rs.Fields("RefPieces2") 
    .ZtxtDesignation02 = rs.Fields("Designation2") 
    .ZtxtQte02 = rs.Fields("Qte2") 
    .ZtxtPrixHT02 = rs.Fields("PrixHT2") 
    .ZtxtPrixTOTALHT02 = rs.Fields("PrixPieces2TotalHT") 
    .ZtxtReference03 = rs.Fields("RefPieces3") 
    .ZtxtDesignation03 = rs.Fields("Designation3") 
    .ZtxtQte03 = rs.Fields("Qte3") 
    .ZtxtPrixHT03 = rs.Fields("PrixHT3") 
    .ZtxtPrixTOTALHT03 = rs.Fields("PrixPieces3TotalHT") 
    .ZtxtReference04 = rs.Fields("RefPieces4") 
    .ZtxtDesignation04 = rs.Fields("Designation4") 
    .ZtxtQte04 = rs.Fields("Qte4") 
    .ZtxtPrixHT04 = rs.Fields("PrixHT4") 
    .ZtxtPrixTOTALHT04 = rs.Fields("PrixPieces4TotalHT") 
    .ZtxtReference05 = rs.Fields("RefPieces5") 
    .ZtxtDesignation05 = rs.Fields("Designation5") 
    .ZtxtQte05 = rs.Fields("Qte5") 
    .ZtxtPrixHT05 = rs.Fields("PrixHT5") 
    .ZtxtPrixTOTALHT05 = rs.Fields("PrixPieces5TotalHT") 
    .ZtxtReference06 = rs.Fields("RefPieces6") 
    .ZtxtDesignation06 = rs.Fields("Designation6") 
    .ZtxtQte06 = rs.Fields("Qte6") 
    .ZtxtPrixHT06 = rs.Fields("PrixHT6") 
    .ZtxtPrixTOTALHT06 = rs.Fields("PrixPieces6TotalHT") 
    .ZtxtReference07 = rs.Fields("RefPieces7") 
    .ZtxtDesignation07 = rs.Fields("Designation7") 
    .ZtxtQte07 = rs.Fields("Qte7") 
    .ZtxtPrixHT07 = rs.Fields("PrixHT7") 
    .ZtxtPrixTOTALHT07 = rs.Fields("PrixPieces7TotalHT") 
    .ZtxtPrixPiecesTotalHT = rs.Fields("PrixPiecesTotalHT") 
    .ZtxtMainOeuvre = rs.Fields("MainOeuvre") 
    .ZtxtCoutMainOeuvre = rs.Fields("CoutHoraireMO") 
    .ZtxtCoutMainOeuvreTotal = rs.Fields("CoutMOTotal") 
    .ZtxtPrixTotal = rs.Fields("PrixTotal") 
    .ZtxtProvenance = rs.Fields("Provenance") 
    .ZtxtEtatArrivee = rs.Fields("EtatArrivée") 
    .ZtxtDateReparation = rs.Fields("DateReparation") 
    .ZtxtEtatSortie = rs.Fields("EtatSortie") 
    .ZtxtTechTerrain = rs.Fields("TechnicienTerrain") 
    .ZtxtIDMS = rs.Fields("IDMS") 
    .ZtxtConstat = rs.Fields("Constats") 
    .ZtxtStatusActivite = rs.Fields("StatusActivite") 
    .ZtxtObservations = rs.Fields("Observations") 
    End With 
    rs.Close 
    db.Close 
    
    Set rs = Nothing: 
    Set db = Nothing: 
End Sub

Thanks for your help.

Nilses

 

[ColinEssex]

Just an observation, but 54 fields on a form does seem an awful lot.

Col

 

[modest]

I'm not really sure what you're trying to do, I had a hard time understanding you.

(2) Things to look at:

1. Make sure you're processing all the records

    Change:
    Set rs = db.OpenRecordset(strSQL)
 
    To:
    Set rs = db.OpenRecordset(strSQL) 
    rs.MoveLast                           'makes sure all records are in the recordset
    rs.MoveFirst                          'points to the first record

2. Make sure you're not skipping any files

    While NumeroRI <> rs("NumRI") 
        If CBool(Err.Number) Then 
            Err.Clear 
            Exit Sub 
        End If 
        rs.MoveNext                       'moved this to the end of the loop
    Wend

Because I don't understand what it is you're trying to get at, you may or may not want to make the changes suggested in #2.