Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 25th, 2008, 12:15 PM
Authorized User
 
Join Date: Mar 2008
Location: , NJ, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default how to open an existing query in VBA

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


Reply With Quote
  #2 (permalink)  
Old March 25th, 2008, 02:45 PM
Authorized User
 
Join Date: Mar 2008
Location: , NJ, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old March 26th, 2008, 06:50 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old March 26th, 2008, 07:39 AM
Authorized User
 
Join Date: Mar 2008
Location: , NJ, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old March 26th, 2008, 05:09 PM
Authorized User
 
Join Date: Mar 2008
Location: , NJ, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't open database thru VBA sektor Access VBA 1 September 15th, 2008 11:44 AM
How to Open Existing Winform inside Tab Page maulik33 C# 2 May 24th, 2007 10:05 AM
Handling Non-Existing Web Files with Workbook.Open white_wulff Excel VBA 1 May 14th, 2007 03:07 PM
how to make a query from an existing query raport SQL Language 3 November 13th, 2006 08:59 PM
How to add fields to an existing table using VBA donrafeal Access VBA 3 March 22nd, 2006 06:11 PM



All times are GMT -4. The time now is 02:21 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.