Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Apr 2012
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Access 2010 VBA: Loop through records in a query

    I know just about enough programming to get me into trouble and frustrated enough when I don't understand how to proceed. Hence, I have come for assistance. I have been trying to figure this out for a week now and am just not understanding how this should be done.

    I have a query "LegDistrictContact" with four fields:
    Legislative_Contact;
    Company_Name; LegContact1112: IIf([Legislative_Contact]="2011-12 Legislative Contact","1112",""); LegContact1213: IIf([Legislative_Contact]="2012-13 Legislative Contact","1213","")

    The same Company_Name may be listed numerous times so I need to loop through the Company_Name to find where they have a LegContact1112 and not a LegContact1213. I have attached a sample of what my data looks like. In the attachment, since Altoona has a 1213 entry and at least one 1112 entry my output field would indicate that "Yes"; Appleton does not have an entry for 1213 but has an entry for 1112 so the output filed would indicate "No". In the sample data, Augusta has a 1213 entry, but not a 1112 entry, their output field would be "Yes"

    If anyone is willing to help this "wannabe" programmer with this, I will be very grateful. There are other places I could use a loop, but I need to first learn how to accomplish this. Most of the reading I have found, uses loops linked to forms. I know I can figure this out manually, but that would take away the fun of learning how to write a loop. Legislative.doc

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    DJ,

    Why do you need programming when a query can handle the problem, at least as I understand it.


    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    djsmith (2012-10-08)

  4. #3
    New Lounger
    Join Date
    Apr 2012
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Your correct, that worked. But I still would like to learn how to write a loop based on data from a query. Thank you Djsmith

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    DJ,

    Here's some information.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    New Lounger
    Join Date
    Apr 2012
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you, already have it bookmarked. It seems to explain things in simple terms, I have been told that I am a visual person and that is why programming is more difficult for me. I have taken courses in VB 2005, Javascript and PHP, while I passed, it was quite a struggle.
    Last edited by djsmith; 2012-10-07 at 04:33.

  7. #6
    New Lounger
    Join Date
    Apr 2012
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I have looked at the link you provided above, but still struggle with loops. I now have a new issue.

    I have 397 school districts that have any number of members between 1 and 7. My goal is to identify the members for each district so that I can get them onto one line for use in a mail merge. There is nothing unique about any of the members associated with each district, so I thought the best way to handle this is to assign each member with a number 1-7 in a new field (BdMbrCount) in my query. How can I use VBA to assign a BdMbrCount to each board member?

    What I have now looks like:
    bdMbrCountPreResults.JPG

    And what I want is:
    bdMbrCountPostResults.JPG

    This is my Query Design:
    bdMbrCountQry.JPG

    For what it is worth this is what I have started for my VBA.

    BdMbrCountVBA.JPG

    Your help is always greatly appreciated. And helps me to learn how to make things happen with Access and VBA.

    Thank you,

    Delta

  8. #7
    New Lounger
    Join Date
    Apr 2012
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Making slow progress - I think

    I have created a countQuery to tell me how many members are with each district, which will allow me to set the Counter to the CountofORG_NAME. And I think I have figured out how to start setting up the VBA

    Code:
    Function BdMbrCount(ORG_NAME As String, PERS_NAME_LAST As String, CountofORG_NAME As String) As String
    
        Dim rst As DAO.Reordset
        Dim StrText As String
        
        Set rs = CurrentDb.QueryDefs("YourBdMbrsRRecognizedQry").OpenRecordset
        
        With rst
            .MoveFirst
            Do Until .EOF
                If CountofORG_NAME > 1 Then
                    Counter = CountofORG_NAME
                        
                    
                End If
                    
            
            Loop
        End With
        
        rst.Close
        Set rst = Nothing
        
    End Function

  9. #8
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    I think you are going about this the wrong way. If you want to concatenate all the related child fields onto a single line then I would return this string as a single field in the main query by using a concatenate function.

    Have a look at the code here
    http://www.tek-tips.com/faqs.cfm?fid=4233

    Can you use that?
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  10. #9
    New Lounger
    Join Date
    Apr 2012
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you, but actually the number that should be assigned to BdMbrCount is a number 1-7 based on how many members are assigned to a district. If countofORG_Name = 5, then the BdMbrcount forthe 5 members will be 1-5.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •