Adding values to present column

[charlesfchang]

Hello All,

I would like to ask why my code doesn't add my values?


First off, I use the output of a query (qryTally) to set as my values to a table (tblOrderCountDaily) which sets all the count of a product ordered during a cmdbutton was clicked. If cutoff wasnt clicked for that day, it would create a new field setting the field name as the date. Now, if i click again the the cutoff button, it would check again if the field exists, if yes, i would add the value to the previous value.

:banghead:

Private Sub CutOff_Click()
  
    
    Dim db As DAO.Database
    Dim tbl As DAO.Recordset
    Dim strSQL As String
    Dim CheckOut As String
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("qryTally")
    Set tblctrl = db.OpenRecordset("tblControlTable")
    TblNum = tblctrl.tablenumber.Value
    OrdNum = tblctrl.OrderNumber.Value
    'Set tblcur = db.OpenRecordset("tblOrderCountDaily")
    
    'Datenow = "CheckTotal"
    If fieldexists("tblOrderCountDaily", "CheckOut") Then
        Do While Not rs.EOF
            ProdName = rs!Product.Value
            QtyValue = rs!Qty.Value
            'date2 = "'" & Date - 1 & "'"
            
            DoCmd.SetWarnings False
            strSQL = "UPDATE tblOrderCountDaily " & _
                 "SET " & DateNow & " = " & DateNow & "+" & QtyValue & " " & _
                 "WHERE Product = '" & ProdName & "';"
            Debug.Print strSQL
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
            rs.MoveNext
         Loop
    Else
         DoCmd.SetWarnings False
         strSQL = "ALTER TABLE tblOrderCountDaily " & _
                    "ADD COLUMN CheckOut Number;"
         DoCmd.RunSQL strSQL
         
         strSQL = "UPDATE tblControltable " & _
                  "SET CheckOut = 1;"
         DoCmd.RunSQL strSQL
         DoCmd.SetWarnings True
        
         Do While Not rs.EOF
            ProdName = rs!Product.Value
            QtyValue = rs!Qty.Value
            'date2 = "'" & Date - 1 & "'"
            
            DoCmd.SetWarnings False
            strSQL = "UPDATE tblOrderCountDaily " & _
                 "SET CheckOut = '" & QtyValue & "' " & _
                 "WHERE Product = '" & ProdName & "';"
            'Debug.Print strSQL
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
            rs.MoveNext
         Loop

    End If
       
End Sub

 

[rzw0wr]

Dim db As DAO.Database 
Dim [U][B]rsqry [/B][/U]As DAO.Recordset
[U][B]Dim rstbl AS DAO.RecordSet[/B][/U]
Dim strSQL As String     
Dim CheckOut As String         
 Set db = CurrentDb     
Set[U][B] rsqry[/B][/U] = db.OpenRecordset("qryTally")     
Set [B]rstbl[/B] = db.OpenRecordset("tblControlTable")

Start by changing the bold and adding Option Explicit at the top of you code window.

DAle