Looping through recordset problem 1

[DBServices]

I have been building a database for my barber shop. On my unbound "frmHaircuts" I have some option buttons for the user to select which kind of haircut the customer just received. I have corresponding text boxes with DLookups as the control source displaying the price for the selected haircut. Once they select the haircut, then in the payment selection there are some option buttons to select which kind of payment such as cash, credit, etc...Once all the mandatory fields are populated, there is a "cmdSubmit" which runs an Append Query and inserts the record into the "tblHaircuts". At the end of the day, I want to run a "End of Day Totals" query which will sum up the total money received in the "tblHaircuts" and insert a record into the "tblLedger" to show the daily sales for financial info. The "qryDailySales" is only pulling back three fields (the current date grouped, total sales, and the income type which is "6" because that is the ID for haircuts in my "tblIncomeType". All is well up to this point, however, an issue came up. Let's say they run the end of day function and then have to cut another haircut. Then they will have to run the end of day function again so what I want to do is pull back a recordset from the "tblLedger" and loop through it to find the record that has been inserted and update the total with the new value. Below is the code I have so far but I'm at a roadblock, as you can see, I've had to comment out what I want to do because I don't know how to structure my VBA correctly and need some help. Thank you...Dannie.

Private Sub cmdEndOfDay_Click()

'Dim strSQL As String, dbs As DAO.Database, rsSQL As DAO.Recordset , salesDate As Date
' Set dbs = CurrentDb
' salesDate = Date

'  Set rsSQL = dbs.OpenRecordset(strSQL2, dbOpenSnapshot)
'
'
'   rsSQL.MoveFirst
'
'    If rsSQL.Fields(1).Value = Date And rsSQL.Fields(4).Value = True Then
'    If MsgBox("You have already submitted the End of Day totals, run again?", vbYesNo) = vbYes Then
'    run update query to update the DebitIn to reflect the new value
    
' (The first IF statement is looking to see if there's a value in the DebitIn field of my ledger table along with            
'   current date which will be the record I'm looking for....)    



Dim strSQL As String
strSQL = "INSERT INTO tblLedger (LedgerDate, DebitIn, IncomeType)SELECT HaircutDate, SumOfTotalPrice, IncomeType FROM qryDailySales"

If MsgBox("Are you ready to run the end of day totals?", vbYesNo, "END OF DAY") = vbYes Then
DoCmd.SetWarnings (False)
DoCmd.RunSQL strSQL
DoCmd.SetWarnings (True)
Else
MsgBox ("...")
Me.Undo
Me.FrameHaircutStyles.SetFocus
End If
End Sub

 

[dhookom]

Not sure about your code but I wouldn't send a recordset to do the job of a DCount().

Private Sub cmdEndOfDay_Click()
    
 [COLOR=#4E9A06]   'Dim strSQL As String, dbs As DAO.Database, rsSQL As DAO.Recordset , salesDate As Date
    ' Set dbs = CurrentDb
    ' salesDate = Date
    ' [highlight #FCE94F]There is not strSQL2[/highlight]
    '  Set rsSQL = dbs.OpenRecordset(strSQL2, dbOpenSnapshot)
    '
    '
    '   rsSQL.MoveFirst
    '
    '    If rsSQL.Fields(1).Value = Date And rsSQL.Fields(4).Value = True Then
    '    If MsgBox("You have already submitted the End of Day totals, run again?", vbYesNo) = vbYes Then
    '    run update query to update the DebitIn to reflect the new value
        
    ' (The first IF statement is looking to see if there's a value in the DebitIn field of my ledger table along with
    '   current date which will be the record I'm looking for....)[/color]
    
    
    
    Dim strSQL As String
    If DCount("*", "tblLedger", "LedgerDate=Date() AND DebitIn = [some value]") > 0 Then
        If MsgBox("You already have a total for today. Do you want to overwrite it with a new total?", _
                    vbYesNo + vbQuestion, "Confirm") = vbYes Then
            strSQL = "Delete from tblLedger where LedgerDate = Date() AND DebitIn = [some value]"
            CurrentDb.Execute strSQL, dbFailOnError
            strSQL = "INSERT INTO tblLedger (LedgerDate, DebitIn, IncomeType) " & _
                    "SELECT HaircutDate, SumOfTotalPrice, IncomeType FROM qryDailySales"
          Else
            MsgBox ("...")
            Me.Undo
            Me.FrameHaircutStyles.SetFocus
        End If
    End If
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[DBServices]

Thank you DHookom for your help. I've never used the DCount function before but I got it to work and I really appreciate it. I had to modify the code a little but because the "CurrentDb.Execute strSQL, dbFailOnError" would give me an error, "Too few parameters, expected 1". I changed it to the "DoCmd.RunSQL strSQL" and it worked. Below is the code so you can see what I did. In my SQL statement, I changed it to looking for a record that is the current date and where the IncomeType = 6 because that's the ID for "Haircuts" in my tblIncomeType. I use the tblLedger to store accounts Rec/Pay to track all revenue coming in and out and so there are other recs of incoming money such as (product sales, snacks, refunds, etc...) Thank you again!

Dim strSQL As String
    If DCount("*", "tblLedger", "LedgerDate=Date() AND IncomeType = 6 ") > 0 Then
        If MsgBox("You already have a total for today. Do you want to overwrite it with a new total?", _
                    vbYesNo + vbQuestion, "Confirm") = vbYes Then
            strSQL = "Delete from tblLedger where LedgerDate = Date() AND IncomeType = 6 "
             DoCmd.SetWarnings (False)
              DoCmd.RunSQL strSQL
               DoCmd.SetWarnings (True)
               strSQL = "INSERT INTO tblLedger (LedgerDate, DebitIn, IncomeType)SELECT HaircutDate, SumOfTotalPrice, IncomeType FROM qryDailySales"
              DoCmd.SetWarnings (False)
              DoCmd.RunSQL strSQL
             DoCmd.SetWarnings (True)
            MsgBox ("Your new total has been stored"), vbOKOnly, "NEW TOTAL STORED"
        Exit Sub
        End If
        End If
          
        If MsgBox("Are you ready to run the end of day totals?", vbYesNo, "END OF DAY") = vbYes Then
          strSQL = "INSERT INTO tblLedger (LedgerDate, DebitIn, IncomeType)SELECT HaircutDate, SumOfTotalPrice, IncomeType FROM qryDailySales"
             DoCmd.SetWarnings (False)
            DoCmd.RunSQL strSQL
        DoCmd.SetWarnings (True)
      MsgBox ("Your daily sales have been saved."), vbOKOnly, "DAILY SALES SAVED"
    Exit Sub
    Else
    Me.Undo
     Me.FrameHaircutStyles.SetFocus
    Exit Sub
        
    End If

 

[DBServices]

DHookom, I am using the code you wrote and it works fine, but I was thinking of a possible issue that might arise. Let's say a user runs the "End OF Day" totals and then cuts another haircut. If they forget to run it again to update the last haircut and then does it the next day then I need to make the Dcount look for the date selected in the txtHaircutDate. The user would change the date in that text box for the previous day and then run the function. How can I change the criteria in the DCount function to use a variable instead of the Date()??? Here's my code and what I'm trying to do, I can't figure it out...Any help would be greatly appreciated...

Dim strSQL As String, salesDate As Date
salesDate = Me.txtHaircutDate
    If DCount("*", "tblLedger", "IncomeType = 6 AND LedgerDate = & [highlight #FCE94F]salesDate[/highlight] &") > 0 Then
        If MsgBox("You already have a total for today, do you want to overwrite it with a new total?", _
                    vbYesNo + vbQuestion, "Confirm") = vbYes Then
            strSQL = "Delete from tblLedger where LedgerDate = Date() AND IncomeType = 6 "
             DoCmd.SetWarnings (False)
              DoCmd.RunSQL strSQL
               DoCmd.SetWarnings (True)
               strSQL = "INSERT INTO tblLedger (LedgerDate, DebitIn, IncomeType)SELECT HaircutDate, SumOfTotalPrice, IncomeType FROM qryDailySales"
               DoCmd.SetWarnings (False)
              DoCmd.RunSQL strSQL
             DoCmd.SetWarnings (True)
            MsgBox ("Your new total has been stored."), vbOKOnly, "NEW TOTAL STORED"
        Exit Sub
        Else
        DoCmd.CancelEvent
        Exit Sub
        End If
        End If
          
        If MsgBox("Are you ready to run the end of day totals?", vbYesNo, "END OF DAY") = vbYes Then
           strSQL = "INSERT INTO tblLedger (LedgerDate, DebitIn, IncomeType)SELECT HaircutDate, SumOfTotalPrice, IncomeType FROM qryDailySales"
             DoCmd.SetWarnings (False)
           DoCmd.RunSQL strSQL
        DoCmd.SetWarnings (True)
      MsgBox ("Your daily sales have been saved."), vbOKOnly, "DAILY SALES SAVED"
    Exit Sub
    Else
    Me.Undo
     Me.FrameHaircutStyles.SetFocus
    Exit Sub
        
    End If

 

[dhookom]

Try this code that properly handles memory variables in SQL statements

    Dim strSQL As String, salesDate As Date
    salesDate = Me.txtHaircutDate
    If DCount("*", "tblLedger", "IncomeType = 6 AND LedgerDate = #" & salesDate & "#") > 0 Then
        If MsgBox("You already have a total for the selected day. Do you want to overwrite it with a new total?", _
                    vbYesNo + vbQuestion, "Confirm") = vbYes Then
            strSQL = "Delete from tblLedger where LedgerDate = #" & salesDate & "# AND IncomeType = 6 "
            DoCmd.SetWarnings (False)
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings (True)
            strSQL = "INSERT INTO tblLedger (LedgerDate, DebitIn, IncomeType) SELECT HaircutDate, SumOfTotalPrice, IncomeType FROM qryDailySales"
            DoCmd.SetWarnings (False)
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings (True)
            MsgBox ("Your new total has been stored."), vbOKOnly, "NEW TOTAL STORED"
            Exit Sub
          Else
            DoCmd.CancelEvent
            Exit Sub
        End If
    End If  
    If MsgBox("Are you ready to run the end of day totals?", vbYesNo, "END OF DAY") = vbYes Then
        strSQL = "INSERT INTO tblLedger (LedgerDate, DebitIn, IncomeType) SELECT HaircutDate, SumOfTotalPrice, IncomeType FROM qryDailySales"
	DoCmd.SetWarnings (False)
	DoCmd.RunSQL strSQL
	DoCmd.SetWarnings (True)
        MsgBox ("Your daily sales have been saved."), vbOKOnly, "DAILY SALES SAVED"
        Exit Sub
     Else
        Me.Undo
        Me.FrameHaircutStyles.SetFocus
        Exit Sub
    End If

Duane
Hook'D on Access
MS Access MVP

 

[DBServices]

When I was stepping through my code, my locals window was showing my salesDate value surrounded by # signs so I assumed I didn't have to put them in my code, but I assumed wrong. I modified the code to be like yours and it works with no problems, thank you very much. Dannie.

 

[dhookom]

You also had

"... & salesDate & ..."

rather than

"..." & salesDate & "..."

Memory variables and references to control values must be outside the quotes.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[DBServices]

DHookom, I have a small issue I'd like a bit of help on if you can. On the same unbound form where user selects type of haircut, payment method, and customer name; I also have a option box to check if the customer has a coupon before they click the cmdSubmit button. A customer can only use one coupon one time, so if they use one, I run an Update Query to the tblCustomers and update the field CouponFlyer to true. What if that field is already true? There's no way of knowing that so what I want to do is first check to see if they've already used the coupon before the cmdSubmit button runs. Below is the code I have so far but I was wondering how to use the DCount function (if that's what i need to use)?? Thanks for your help, Dannie.

 If Me.optCoupon.Value = True Then
 coupon = Me.optCoupon
    CustomerID = Me.cboCustomerName.Column(0)
 
        strSQL = "UPDATE tblCustomers SET CouponFlyer = True WHERE CustID = " & CustomerID
     DoCmd.SetWarnings False
   DoCmd.RunSQL strSQL
 DoCmd.SetWarnings True
     Me.optCoupon.Value = False
 End If

 

[dhookom]

I would use code when a customer is chosen to check if they have already used a coupon and set the optCoupon to enabled or disabled. Then there is no need to check later and it makes it clear to the user a coupon has already been used.

Maybe something like:

Me.optCoupon.Enabled = ( DLookup("CouponFlyer","tblCustomers","CustID=" & CustomerID) = 0 )

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[DBServices]

That works perfectly. Thank you! Here's the code I used on the AfterUpdate Event on my cboCustomerName combo box...

Private Sub cboCustomerName_AfterUpdate()
Dim CustomerID As Integer
CustomerID = Me.cboCustomerName.Column(0)

Me.optCoupon.Enabled = (DLookup("CouponFlyer", "tblCustomers", "CustID=" & CustomerID) = 0)
End Sub

 

[dhookom]

DBServices,
It would be more efficient if you added the CouponFlyer column to the cboCustomerName combo box row source so you wouldn't need to use DLookup().

Me.optCoupon.Enabled = ( Me.cboCustomerName.Column(x)=0 )

"x" is the column number of CouponFlyer. Remember columns are numbered beginning with 0. Your combo box properties would need updating to account for the new column.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[DBServices]

I did it exactly like you said and it works perfectly... Thanks for showing me how to do that, it's simple stuff, but it's the simple things that make it simple

 

[DBServices]

DHookom, I have a question on how to go about something. On my "End Of Day" function, all works fine but I want to change it up a little bit. Right now it's totaling up the total amount brought in for haircuts (IncomeType=6) that day and inserting it into the tblLedger table. I want to separate the amounts by the different types of transactions. I have a tblTransactionType that I'm using for ("cash, credit, check etc...). So, if I brought in 100.00 in cash, and 150.00 in credit cards I want to insert two records into the tblLedger table. On any given day it could be all cash, all credit cards, or both. And I also need to keep in mind the fact that if I run the end of day totals and insert 2 records into the ledger table (one for cash totals, one for credit card totals) and then cut another client and then run the end of day totals again, I need it to delete both records and then insert them again with the new totals...

 strSQL = "INSERT INTO tblLedger (LedgerDate, DebitIn, IncomeType) SELECT HaircutDate, SumOfTotalPrice, IncomeType FROM qryDailySales"

I know I will have to modify the values and my qryDailySales, will I have to make two? One for cash, one for credit? and then run them both?

Any and all help and guidance is greatly appreciated...Thanks, Dannie.

 

[dhookom]

One query for delete and one for append should be all that are needed. This won't work since I don't recall you mentioning a transaction type field in tblLedger.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016