input mth/yr into table

[ntr81]

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:

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

[Mark Gambo]

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.

[ntr81]

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

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