UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Problem With My Loop, Access 2007    
 
   
LeicsChris
post Oct 27 2017, 03:22 PM
Post#1



Posts: 94
Joined: 23-May 17



CODE
Hi All

I was quite proud of last weeks first coding, and was just  working through my 2nd piece of code and getting the following error;

'Compile error - Loop without Do'

Option Compare Database
Option Explicit

Public Function PayPerformanceBonus()

    'Set the variables
     Dim StaffID As Long 'BookerID is staff who booked appointment
     Dim DateFrom As Date 'DateFrom is Start Date of the Pay Period
     Dim DateTo As Date   'DateTo is End Date of the Pay Period
     Dim BonusRate As Currency 'CommRate is the Commission Rate to be paid for this appointment
     Dim TotalBookings As Long 'TotalBookings is the total appointments booked by each staff in the Pay Period
     Dim Counter As Long 'Counter is the counter to move through the records
     Dim StartLine As Long 'This is the minumim number of points needed for staff to start getting commission
     Dim BonusPay As Boolean 'This is the yes/no field to set if commission was paid on a particular appointment
     Dim BonusDate As Date    'This is the date the commision is paid (usually set as last day of period
     Dim TierCheck1 As Long   'This is a variable to check which tier of commision they are on
     Dim TierCheck2 As Long
     Dim TierCheck3 As Long
     Dim TierFound As String  'This string will set the rate field holding the commission rate to be paid
     Dim DatePaid As Date     'This is the date commision is paid (usually the last day of following Period)
     Dim FormEnd As Date       'This is the date selected as period in the set up form
     Dim WeeksToGet As Long    'This is the number of weeks to hit the target to get the bonus
     Dim CheckSQL As String 'This is the string to open the counting recordset to count number of appointments for each staff
     Dim UpSQL As String 'This is the string to use in the recordset to update the appointment update query
     Dim ParSQL As String  'This is the string to use to get the bonus parameters
     Dim ComStaffSQL As String 'This is the SQL to get the staff entitled to bonus and their target
     Dim BonusAwarded As Boolean 'If bonus has been awarded the true, if not false


     Dim rst1 As DAO.Recordset 'rst1 is database recordset for appointments from appointments table
     Dim rst2 As DAO.Recordset 'rst2 is the database recordset for commisions paid to add to the CommissionTB table
     Dim rst3 As DAO.Recordset 'rst3 is the recordset to look up performance bonus parameters
     Dim rst4 As DAO.Recordset  'rst4 is the recordset that looks up target for each commission staff

     Counter = 0
'____________________________________________________________________________
________________________________

'Set up dates that this calculation is for
     FormEnd = Forms!CommissionSetUpFM!PeriodEnd
     DateFrom = FormEnd - 56 'This is test date - once working change to paramters form
     DateTo = FormEnd - 28  'This is test date - once working change to parameters form
     DatePaid = FormEnd 'This is the date commission paid. will change to parameter form
'____________________________________________________________________________
_________________________________

'Set up general parameters that apply to all staff
     ParSQL = "Select * From CommParamTB"
     Set rst3 = CurrentDb.OpenRecordset(ParSQL)    'Opens PerformBonusTB recordset to get parameters for bonus
     BonusRate = rst3.Fields("CurrentBonusValue")
     WeeksToGet = rst3.Fields("WeeksForBonus")
'____________________________________________________________________________
______________________________________

'Get Staff who get commission/bonus to work through

    ComStaffSQL = "Select * From TeleBonusParQ"

   Set rst4 = CurrentDb.OpenRecordset(ComStaffSQL) 'Opens recordset showing every staff member who gets commission/bonus
'____________________________________________________________________________
_________________________________________________

  'Get recordset to add any bonus too
  
   UpSQL = "Select * From PerfBonusTB"
   Set rst2 = CurrentDb.OpenRecordset(UpSQL)  ' Sets recordset to add any bonus to
  
  '____________________________________________________________________________
_______________________________________________
  
'Get Recordset which counts the total bookings each member of staff has

   CheckSQL = "SELECT BookeBY, Sum(PerformCounter) AS TotalBookings FROM AppointmentCounterQ Where DateBooked >= #" & DateFrom & "# And DateBooked <= #" & DateTo & "# GROUP BY BookeBY"
   Set rst1 = CurrentDb.OpenRecordset(CheckSQL)  'Opens recordset which gives total number of booking points for each staff
  
  '____________________________________________________________________________
_______________________________________________
  
  
     rst4.MoveFirst 'Goes to first member of staff who gets commission
  
  
     Do Until rst4.EOF   'Start the loop to go through and process each employee
  
  
      With rst1
     .FindFirst "BookeBy = " & "rst4.fields('TeleID'"
     If .NoMatch Then rst4.MoveNext
     If .NoMatch Then Loop
  
  
     End If
     End With

     If rst1.Fields("TotalBooings") >= rst4.Fields("ComTarget3") Then BonusAwarded = True
  
     If rst1.Fields("TotalBookings") < rst4.Fields("ComTarget3") Then BonusAwarded = False
  
     If BonusAwarded = False Then rst4.MoveNext
     If BonusAwarded = False Then Loop
  
     rst2.AddNew 'Add new record to table to add a bonus payment
     rst2.Fields("WhoEarned") = rst4.Fields("TeleID")
     rst2.Fields("DateEarned") = DatePaid
     rst2.Fields("BonusRate") = rst3.Fields("CurrentBonusValue")
     rst2.Update
  
     Counter = Counter + 1
     rst4.MoveNext
  
     Loop
  
  '____________________________________________________________________________
___________________________________


    rst1.Close  'Closes the employee recordset
    Set rst1 = Nothing 'Clears the recordset

    rst2.Close 'Closes the appointments recordset
    Set rst2 = Nothing  ' Clears the recordset

    rst3.Close
    Set rst3 = Nothing

    rst4.Close
    Set rst4 = Nothing


    MsgBox "Total number of Bonus Payments = " & Counter



Error_Handler_Exit:
      On Error Resume Next
    If Not rst2 Is Nothing Then    'appointments recordset
        rst2.Close
        Set rst2 = Nothing
    End If
    If Not rst1 Is Nothing Then    'employee recordset
        rst1.Close
        Set rst1 = Nothing
    End If
    Exit Function

Error_Handler:
    'LogError Err.Number, Err.Description, sModName & "\PayExtra", , True, Erl
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: PayExtra" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit



' ...
  Debug.Print UpSQL
' ...


This is the line showing the error " If .NoMatch Then Loop"

Basically, I am trying to loop through each employee who gets a commission bonus and if they have hit their target number of bookings they get an additional £100 bonus.

The code should loop through each employee and check the number of bookings against their target. If they get the bonus then a new record is created in the table. If they don't get the bonus then no record is needed.

Can you not loop on a condition?

Thanks

Chris
Go to the top of the page
 
theDBguy
post Oct 27 2017, 03:30 PM
Post#2


Access Wiki and Forums Moderator
Posts: 71,202
Joined: 19-June 07
From: SunnySandyEggo


Hi Chris,

There must be a technical explanation for it but my guess is the code block has to have a specific syntax. For example, perhaps the compiler expects to see something like the following:

Do
Loop

or

With
End With

or

Loop
Until

or even

If
End If

etc.

Essentially, what you ended up doing is similar to this:

Do
...Loop
Loop

Which I am guessing is confusing the compiler.

If you want to skip the code and go straight to the Loop, assuming my guess above is somewhat correct, then you can just do something like this:

Do
If SomeCondition Then
'do nothing
Else
'do your stuff
End If
Loop

Hope it makes sense...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
LeicsChris
post Oct 27 2017, 05:15 PM
Post#3



Posts: 94
Joined: 23-May 17



Hi DB

I think I understand what you mean, I have changed that code part to this;

CODE
Do Until rst4.EOF   'Start the loop to go through and process each employee
  
  
      With rst1
     .FindFirst "BookeBy = " & "rst4.fields('TeleID'"
     If .NoMatch Then
     'Do Nothing
     Else

     If rst1.Fields("TotalBooings") >= rst4.Fields("ComTarget3") Then BonusAwarded = True
  
     If rst1.Fields("TotalBookings") < rst4.Fields("ComTarget3") Then BonusAwarded = False
  
     If BonusAwarded = False Then rst4.MoveNext
     If BonusAwarded = False Then Loop
  
     rst2.AddNew 'Add new record to table to add a bonus payment
     rst2.Fields("WhoEarned") = rst4.Fields("TeleID")
     rst2.Fields("DateEarned") = DatePaid
     rst2.Fields("BonusRate") = rst3.Fields("CurrentBonusValue")
     rst2.Update
  
     Counter = Counter + 1
     rst4.MoveNext
    
     End If
     End With
    
    
     Loop


I have added everything into the If group of code.

I am guessing that if the If part is true that access will ignore all the code from the 'Else' command to the 'End If' command. If so then I think I have the logic right.

Chris
Go to the top of the page
 
cheekybuddha
post Oct 27 2017, 05:33 PM
Post#4


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


Hi,

You're still missing the point, I think.

Just loop through each record of the recordset.

Test your condiftion. If true, do something.

It will then loop automatically to the next record by itself.
CODE
' ...
  Do Until rst4.EOF
    With rst1
      .FindFirst "BookeBy = " & rst4.fields("TeleID")
'     If TeleID is a text field the use the following line and comment the previous one
'      .FindFirst "BookeBy = '" & rst4.fields("TeleID") & "'"
      If Not .NoMatch Then
        BonusAwarded = .Fields("TotalBookings") < rst4.Fields("ComTarget3")
      End If
    End With
    If BonusAwarded Then
      rst2.AddNew 'Add new record to table to add a bonus payment
      rst2.Fields("WhoEarned") = rst4.Fields("TeleID")
      rst2.Fields("DateEarned") = DatePaid
      rst2.Fields("BonusRate") = rst3.Fields("CurrentBonusValue")
      rst2.Update
    End If
    Counter = Counter + 1
    rst4.MoveNext
  Loop


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
mklein
post Oct 27 2017, 05:34 PM
Post#5



Posts: 264
Joined: 7-August 12
From: BC, Canada


I don't think this line is a valid way to terminate the loop...
CODE
     If BonusAwarded = False Then Loop

I would expect your code (and note the indents) to look something more like....
CODE
    Do Until rst4.EOF   'Start the loop to go through and process each employee
        With rst1
            .FindFirst "BookeBy = " & rst4.fields('TeleID')
            If Not .NoMatch Then
                BonusAwarded = .Fields("TotalBooings") >= rst4.Fields("ComTarget3")
                
                If BonusAwarded Then
                    rst2.AddNew 'Add new record to table to add a bonus payment
                    rst2.Fields("WhoEarned") = rst4.Fields("TeleID")
                    rst2.Fields("DateEarned") = DatePaid
                    rst2.Fields("BonusRate") = rst3.Fields("CurrentBonusValue")
                    rst2.Update
                    Counter = Counter + 1
                End If
                rst4.MoveNext
            End If
        End With
     Loop

hth
Mark

--------------------
| Mark Klein | Access 2010 | Windows 10 | Visual Studio 2013
Go to the top of the page
 
cheekybuddha
post Oct 27 2017, 05:37 PM
Post#6


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


Chris,

Do you have Option Explicit declared at the top of EVERY code module?

--------------------


Regards,

David Marten
Go to the top of the page
 
LeicsChris
post Oct 27 2017, 05:46 PM
Post#7



Posts: 94
Joined: 23-May 17



Thank you David and Mark,

so, BonusAwarded = .Fields("TotalBooings") >= rst4.Fields("ComTarget3") will return a true/False without using an If before it.

I see how you are doing it.

You also mention to take note of the indents, is that for better reading, or does access pay attention to the indents and work differently with indents/ no indents

Thanks

Chris
Go to the top of the page
 
LeicsChris
post Oct 27 2017, 05:47 PM
Post#8



Posts: 94
Joined: 23-May 17



Yes David,

I have Option Explicit set at the top, where you showed me on my first code last week.

Chris
Go to the top of the page
 
cheekybuddha
post Oct 27 2017, 05:55 PM
Post#9


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


Also, double-check your spellings of table field names.

In the BonusAwarded line, you once refer to .Fields("TotalBooings"), and another time to .Fields("TotalBookings").

Indents make no odds to code execution, they only make your life easier to see where you are in the code.

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Oct 27 2017, 06:04 PM
Post#10


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


Also, watch out for this line that you had in your origfinal code and that Mark c&p'd:
CODE
    .FindFirst "BookeBy = " & rst4.fields('TeleID')

When you refer to the field of rst4 you are in VBA code, and string must be delimited with double quotes.

It will error as it stands.

It should read:
CODE
    .FindFirst "BookeBy = " & rst4.fields("TeleID")

"TeleID" is a literal string that you are using as a key to reference the appropriate member of the .Fields collection.

Using the single quote is confusing string delimiters in SQL, where you can use either single or double quotes to indicate a literal string value.

When you build an SQL statement, you do so within a VBA string.

It's useful to be able to use single quotes to delimit a string within the SQL statement because VBA does not think you are terminating the VBA string.

dazed.gif

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
LeicsChris
post Oct 27 2017, 06:05 PM
Post#11



Posts: 94
Joined: 23-May 17



David

I will try to get used to indenting the code as it is easier to read.

I did notice that spelling mistake and changed it after I posted it in the thread

Chris
Go to the top of the page
 
fkegley
post Oct 28 2017, 04:21 PM
Post#12


UtterAccess VIP
Posts: 23,746
Joined: 13-January 05
From: Mississippi


You need to close the If statements ahead of the Loop line.

--------------------
Frank Kegley, Microsoft Access 2010 MVP
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 06:54 AM