Loop through query to assign a value to a new field

[djsmith2005]

Using Access 2010: I have a query with four fields: ORG_NAME, PERS_NAME_LAST, CountOfORG_NAME, and BdMbrCount.
There are a couple hundred companies in the database with 1-7 people associated with each company. I need to number each person so that they have a number, 1-7 in the MemberCount field of my query.

I understand the concept of loops, but am not sure how to make this happen in VBA. I have my query connected to VBA code, thanks to a post, I came across on Access World Forums.

I have experimented with code that I have found on the forum, just to see if I could get something to work and I am getting “Undefined function ‘BdMbrCount’ in expression. I am trying to pass [ORG_NAME],[PERS_NAME_LAST] to my function and assign the value of BdMbrCount to a new field in my query, BdMbrCount.

Is there anyone here who can assist me in figuring this one out?

Function BdMbrNumber()


    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.QueryDefs("YourBdMbrsRRecognizedQry")
    'Set rst = CurrentDb.OpenRecordset("SELECT [ORG_Name],[PERS_NAME_LAST],[CountofORG_NAME], [BdMbrCount] FROM YourBdMbrsRRecognizedQry")
    Dim ORG_NAME As String
    Dim PERS_NAME_LAST As String
    Dim CountofORG_NAME
    Dim BdMbrCount As Double
      
    
    'qdf.Fields = [ORG_NAME]
   ' qdf.Fields = [PERS_NAME_LAST]
   ' qdf.Fields = [CountofORG_NAME]
    'qdf.Fields = [BdMbrCount]
    'qdf.Execute
    
    
    
    Dim i As Integer
    i = 1
    
    If rst.BOF Then
        rst.MoveFirst
            Do While Not rst.EOF
                rst.Edit
                rst![BdMbrCount] = i
                i = i + 1
                If i > 12 Then i = 1
                rst.MoveNext
            Loop
    
    End If
       
        rst.Close
        Set rst = Nothing
        
End Function

 

[billmeye]

Are you trying to save a value to a table? You can't save a value to a query. In a query you can use an equation or reference a function to show a value that is not in the underlying table.

 

[djsmith2005]

Thank you for getting back to me

If I am understanding this correctly, I am trying to reference a function to show a value that is not in the underlying table. This is what I had in my query for the new field, BdMbrCount: BdMbrCount([ORG_NAME],[PERS_NAME_LAST]) but that was before I was able to get my query to be recognized in my Function.

At one time I was successful with connecting a different query to a function in VBA, which consisted of several nested IF Statements. That is still working, so I am afraid to see if I could get it working another way.

 

[billmeye]

Could you explain how you are determining the value of BdMbrCount and is this something that is only used on a temporary basis? Are you just counting by 1 - 7 and starting over again and it doesn't matter which record gets what value?

 

[djsmith2005]

I created a query that counts the number of board members for each organization, the coungofOrg_NAME comes from that query. I think that this would be the counter for each Org_NAME.

Org_NAME1 count = 4 the board members (PERS_LAST_NAME) would be numbered 1-4
Org_NAME2 count = 6 the board members (PERS_LAST_NAME) would be numbered 1-6

Thank you for your continued assistance!

 

[billmeye]

Is your ultimate goal to show this list in a report? The reason I ask is the report has an easy way to do so, it allows you to create a control with the datasource =1 and then do a running sum over the group essentially counting by 1 so your list would be 1, 2, 3 etc. You then setup the report to be grouped by the Org_Name and it would re-start counting anew for each group.

 

[Mihail]

I see, somewhere, far away, in the fog, a solution.
But I need your DB with some simple data in order to try to develop it and, of course, to test.
So, if you can, upload the DB in Access 2003 or 2007 version.
ZIP the DB before upload.

 

[djsmith2005]

Ultimately I need to put each board member's name on the same line as the ORG_NAME so that I do create a mail merge that will include all of the board members' names for each organization.

My thought is that if I can assign a number to each board member, I can then set up a query that will put their names on one line. Right now, I don't have anything unique through all of the records to do this as the data stands.

 

[billmeye]

If you have an autonumber primary key for the table you do something like this to count based on the Org_Name:

SELECT tblOrgs.PKID, tblOrgs.ORG_NAME, tblOrgs.PERS_NAME_LAST, DCount("*","tblOrgs","[ORG_NAME]='" & [ORG_NAME] & "' And [PKID]<" & [PKID])+1 AS BdMbrCount
FROM tblOrgs;

It will just assign the incremental number based on the order they were entered into your DB.

 

[djsmith2005]

Thank you very much billmeye!

I have stripped a lot of the data out, but left a good sample of records. Please let me know if you need anything else.

 

[billmeye]

Here is the query:

SELECT MemberRecognition_Final.ORG_NAME, MemberRecognition_Final.PERS_PK, MemberRecognition_Final.PERS_NAME_LAST, DCount("*","MemberRecognition_Final","[ORG_NAME]='" & [ORG_NAME] & "' And [PERS_PK]<" & [PERS_PK])+1 AS BdMbrCount
FROM MemberRecognition_Final
ORDER BY MemberRecognition_Final.ORG_NAME, MemberRecognition_Final.PERS_PK;

 

[djsmith2005]

That worked perfectly, It is amazing how you could solve my issue in a matter of minutes, when it has taken me days to re-enforce how little I know and understand about ACCESS.

 

[billmeye]

I find myself in the same position, this time I was able to come up with a solution. I'm glad I could help, good luck with the rest of your project.

 

[Mihail]

@billmeye
Smart. Very smart.
My intention was to use external function.
But your solution is a lot beyond of this.

 

[billmeye]

Thank You Mihail. Sometimes things click and you can come up with a simple solution.