how to prevent duplicate records?

[ariel81]

i am using a textbox in the main form to transfer the Yr/Mth into the table. (refer to qp.zip)

now my problem is, how do i prevent duplicate records into the table?

e.g: currently whenever i scroll ard the months control in the form, i will get duplicate Mth/Yr into the table. how do i prevent that from happening?

i only want to have updated data inside the Mth/Yr table instead of duplicated Mth/Yr

Public Sub PutInMonthlyRecords()
    
    Dim sql As String
    Dim Db As DAO.Database
    Dim rs As DAO.Recordset
    Dim f As Form
    Dim MthYr As String
    
    Set f = Forms!frmQpi
    
    MthYr = f("txtMthYr")
    
    sql = "SELECT * FROM [tblQpiMonthly] WHERE (([tblQpiMonthly].[Input MthYr] = 'MthYr' ));"
    
    Set Db = CurrentDb()
    Set rs = Db.OpenRecordset(sql)
           
    
        If rs.RecordCount = 0 Then
            If (f!txtTotalPF) = 0 Then
                Exit Sub
            End If
                     
            rs.AddNew
                rs![Input MthYr] = f("txtMthYr")
                rs![Monthly TotalPF] = f("txtTotalPF")
                rs![Monthly Rejection] = f("txtTotalAvoid")
                rs![Monthly TotalAvoid] = f("txtRejection")
            
            rs.Update
            
      Else
            If (f!txtTotalPF) = 0 Then
                rs.Delete
                
                Exit Sub
              End If
              
              
            rs.Delete
            rs.AddNew
                rs![Input MthYr] = f("txtMthYr")
                rs![Monthly TotalPF] = f("txtTotalPF")
                rs![Monthly Rejection] = f("txtTotalAvoid")
                rs![Monthly TotalAvoid] = f("txtRejection")
                
            rs.Update
            
        End If
        
        
Db.Close

End Sub

 

[ariel81]

here is the function which i re-edited. the codes that were not affecting the program have been deleted.

Public Sub PutInMonthlyRecords()
    
    Dim sql As String
    Dim Db As DAO.Database
    Dim rs As DAO.Recordset
    Dim f As Form
    
            
    Set f = Forms!frmQpi
    Set Db = CurrentDb()
        
    
    sql = "SELECT * FROM [tblQpiMonthly];"
    
    Set rs = Db.OpenRecordset(sql)
                           
            If (f!txtTotalPF) = 0 Then
               Exit Sub
            End If
            
            If (f!txtTotalPF) = 0 Then
                  Exit Sub
            End If
            
            If (f!txtTotalPF) = 0 Then
                 Exit Sub
             End If
            
                rs.AddNew
                
                rs![Input MthYr] = f("txtMthYr")
                rs![Monthly TotalPF] = f("txtTotalPF")
                rs![Monthly Rejection] = f("txtTotalAvoid")
                rs![Monthly TotalAvoid] = f("txtRejection")
            
            rs.Update
            
        
Db.Close

End Sub

Basically, when the record is not found in the table, it will add the new record. if the record is found in the table, it should edit the record instead of adding the same record into the table.