Hi,

I have a query in access 2007 called 'YearlyPointsbyMember-Query'
it only has 3 fields
Trainer Code 'numbers'
Year '4 digit number'
SumOfPoints 'number'

sample data
Trainer Code Year SumOfPoint
4 2002 25
4 2003 16
4 2005 4
4 2006 24
25 2007 12
25 2008 8


I want to make a button on my switchboard form that can run some vba code.
What is the syntax to open the query in vba and how do i refer to these 3 fields.
Also I have 2 fields in my main 'member' database that I may need to change
How do i access that database?
Member ID is = to Trainer Code in the query
and I will need to check the value in 'Pension Start Date' and possible change it
and may need to change the value in 'Vested Status'
and have that data saved in the main database.
(Won’t be making changes on the query but will need to do some record counting)
thanks,
Michael

This i think is the code I need to write. But It needs to be changed to be properly formated in VBA. can any one help with formatting this code so it will work?
thanks,
Michael

Private Sub Command137_Click()

Dim dbsSBOA8000 As DAO.Database
Dim rstRacePoints As DAO.Recordset

Set dbsSBOA8000 = CurrentDb
Set rstRacePoints = dbsSBOA8000.OpenRecordset("YearlyPointsbyMember-Query")
Dim forfitcnt As Integer
Dim vpcnt As Integer
Dim yeartst As Integer

Dim fldtrainercode As DAO.Field
Set fldtrainercode = rstRacePoints![Trainer Code]

Dim fldyear As DAO.Field
Set fldyear = rstRacePoints![Year]

Dim fldSumOfPoint As DAO.Field
Set fldSumOfPoint = rstRacePoints![SumOfPoint]

'move vba code to a dialog box whare user enters in work year

 Do Until rstRacePoints.EOF

'seek trainer code in members database = member id
 currentmember = fldtrainercode
'if members.[pension start date] is null than members.[pension start date] = 2000 end if
forfitcnt = 0
vpcnt = 0
yeartst = 0
Do Until currentmember = fldtrainercode
   ' if fldyear < members.[pension start date] or enteredworkyear < me.year than
       rstRacePoints.MoveNext
    Else
       If fldSumOfPoint < 40 Then
           forfitcnt = forfitcnt + 1
        Else
           vpcnt = vpcnt + 1
           If yeartst = 0 Then
       ' members.[pension start date]=fldyear
              yeartst = 1
           End If
       rstRacePoints.MoveNext
    End If
Loop
 If vpcnt > 9 Then
' set vested status for currentmember = 'VP'
End If
 If forfitcnt > 3 Then
' set pension start date = enteredworkyear+1
' set vested status for currentmember = 'FF'
 End If

Loop
rstRacePoints.Close
Set rstRacePoints = Nothing
Set db = Nothing

End Sub

I can do this in ADO if you want. I only use ADO since I do mostly Access / SQL. DAO is for Jet only.

mmcdonal

Look it up at: http://wrox.books24x7.com

That would be great. The lines that are commented are ones I know are formatted wrong. The rest I’m hoping is correct but I haven’t been able to test it
Thanks,
Michael

I found a problem with my forfeit counter. If they had 0 points for a given year it won't even show up in the database.
What I think can be done is to set a temp var = to the year in the first record for a given member.
Change the for counter to use this temp var instead of the actual year in the record.
as it loops through the records add +1 to the temp var. I can then do an if statement to check that the temp var is = to the year field in the record. if they are not equal than move the record back one and increase the forfeit counter. also would need to check that the member numbers are still the same. if they are not the same than move back one record and bump up the forfeit counter. Since the loop will end once the temp var is > the end date this should work well.

this is an example of the data I am trying to get the correct count on.
Person enters 2008 as ending date

ID year points
13 2001 45
13 2003 42
13 2004 60
13 2005 72
14 2002 50

So for record 13 I want to count that they forfeit years 2002, 2006, 2007 and 2008
That is 4 forfeited years so would need to change the vested field on their record to FF

this is what I came up with. It could probably use some polishing but from my testing it seems to work fine.

Option Compare Database

Private Sub OK_Click()

End Sub

Private Sub OKDues_Click()
Dim dbsSBOA8000 As DAO.Database
Dim rstRacePoints As DAO.Recordset

Set dbsSBOA8000 = CurrentDb
Set rstRacePoints = dbsSBOA8000.OpenRecordset("YearlyPointsbyMember-Query")

Dim rstMembers As DAO.Recordset
Set rstMembers = dbsSBOA8000.OpenRecordset("Members", dbOpenDynaset)
'Set IDSearch = dbsSBOA8000.OpenRecordset(strSQL, dbOpenSnapshot)

Dim forfeitcnt As Integer
Dim vpcnt As Integer
Dim yeartst As Integer
Dim yearcnt As Integer

Dim fldtrainercode As DAO.Field
Set fldtrainercode = rstRacePoints![Trainer Code]

Dim fldyear As DAO.Field
Set fldyear = rstRacePoints![Year]

Dim fldSumOfPoint As DAO.Field
Set fldSumOfPoint = rstRacePoints![SumOfPoint]

Dim fldMemberID As DAO.Field
Set fldMemberID = rstMembers![Member Id]

Dim fldVestedStatus As DAO.Field
Set fldVestedStatus = rstMembers![Vested Status]

Dim fldPensionStartDate As DAO.Field
Set fldPensionStartDate = rstMembers![Pension Start Date]

rstRacePoints.MoveFirst
Screen.MousePointer = 11
 Do Until rstRacePoints.EOF
     rstMembers.FindFirst "[Member Id]=" & fldtrainercode
      currentmember = fldtrainercode
      If fldPensionStartDate < 1900 Then
        rstMembers.Edit
        rstMembers![Pension Start Date] = 1900
        rstMembers.Update
      End If

     forfeitcnt = 0
     vpcnt = 0
     yeartst = 0
     yearcnt = fldyear

     Do While yearcnt <= Me.[Year_Search]
          If fldtrainercode = currentmember Then
               If fldyear < fldPensionStartDate Then
                    yearcnt = yearcnt + 1
                    rstRacePoints.MoveNext
                Else
                     If yearcnt = fldyear Then
                          If fldSumOfPoint < 40 Then
                               forfeitcnt = forfeitcnt + 1
                               yearcnt = yearcnt + 1
                               rstRacePoints.MoveNext
                          Else
                               If yeartst = 0 Then
                                    rstMembers.Edit
                                    rstMembers![Pension Start Date] = fldyear
                                    rstMembers.Update
                                    yeartst = 1
                               End If
                               vpcnt = vpcnt + 1
                               yearcnt = yearcnt + 1
                               rstRacePoints.MoveNext
                          End If
                    Else
                         yearcnt = yearcnt + 1
                         forfeitcnt = forfeitcnt + 1
                    End If
                 End If
             Else
                 Do Until yearcnt = Me.Year_Search
                     yearcnt = yearcnt + 1
                     forfeitcnt = forfeitcnt + 1
                Loop
                currentmember = fldtrainercode
            End If
     Loop

    If vpcnt > 9 Then
         rstMembers.Edit
         rstMembers![Vested Status] = "VP"
         rstMembers.Update
     End If
    If forfeitcnt > 3 Then
        rstMembers.Edit
        rstMembers![Pension Start Date] = yearcnt + 1
        rstMembers![Vested Status] = "FF"
        rstMembers.Update
     End If
     rstRacePoints.MoveNext
Loop
Screen.MousePointer = 0
rstRacePoints.Close
Set rstRacePoints = Nothing
Set db = Nothing
MsgBox ("Finished Processing")
End Sub