input mth/yr into table

i typed the following code to input mth/yr into table (qp.zip).
however i always get 01/1905 or 12/1905 into the table "tblQpiMonthly".

refer to my code in the function:

Code:

---

Public Sub PutInData()
    Dim sql As String
    Dim MthYr As String
    Dim f As Form
    Dim Db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Integer
    Dim myDate As Date
    Dim TotalPF As Integer
    Dim TotalAvoid As Integer
    Dim Rejection  As Integer
   
   
    Set f = Forms!frmQpi
   
    TotalPF = 0
    TotalAvoid = 0
    Rejection = 0
   
    f("txtTotalPF") = TotalPF
    f("txtTotalAvoid") = TotalAvoid
    f("txtRejection") = Rejection
 
   
'Empty out the previous month
    For i = 1 To 37
        f("text" & i) = Null
        f("text" & i).BackColor = 10944511
    Next i
   
'Construct a record source for the month
    sql = "SELECT * FROM [tblQpiInput] WHERE ((MONTH(InputDate) = " & f!month & "  AND YEAR(InputDate)= " & f!year & ")) ORDER BY InputDate;"    ' <--original    'added ariel81-->  sql = "SELECT * FROM [tblQpiInput] WHERE ((MONTH(InputDate) = " & USdate & "  AND YEAR(InputDate)= " & USdate & ")) ORDER BY InputDate;"
   
   
       
    Set Db = CurrentDb()
    Set rs = Db.OpenRecordset(sql, dbOpenSnapshot)
   
'Populate the calendar
    If rs.RecordCount > 0 Then
        For i = 1 To 37
            If IsDate(f("date" & i)) Then
                myDate = Format((f("date" & i)), "mm/dd/yyyy")
                rs.FindFirst "InputDate = #" & myDate & "#"
                If Not rs.NoMatch Then
                    f("text" & i) = rs!InputText & Chr(13) & Chr(10) & rs!InputText2
                                       
                    TotalPF = TotalPF + rs!InputText  'calculate monthly total p/f done
                    f("txtTotalPF") = TotalPF
                   
                    TotalAvoid = TotalAvoid + rs!InputText2    'calculate total avoidable p/f rejections
                    f("txtTotalAvoid") = TotalAvoid
                   
                    Rejection = TotalAvoid / TotalPF * 100    'calculate % p/f rejection (avoidable)
                    f("txtRejection") = Rejection
                                       
                    f("text" & i).BackColor = 12058551
                Else
                    f("text" & i).BackColor = 10944511
                End If
            End If
        Next i
    End If
   
   
  Db.Close
 
'---------------------------------------------------------------------------------------
    Set Db = CurrentDb()
    sql = "SELECT * FROM [tblQpiMonthly]"
   
    Set rs = Db.OpenRecordset(sql)
    MthYr = CStr(month(f!month)) & "/" & CStr(year(f!year))
       
    rs.AddNew
                rs![Input MthYr] = MthYr
                rs![Monthly TotalPF] = f("txtTotalPF")
                rs![Monthly Rejection] = f("txtTotalAvoid")
                rs![Monthly TotalAvoid] = f("txtRejection")
            rs.Update
 
Db.Close

End Sub

---

Just a thought, why not convert the Date Field into a Text Field and store the Month Year in the following format:


2007/12

It would probably be easier then trying to convert it into date field unless you enter a day and then change your code to only return the month and year.

ok, now 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

Code:

---

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

---