How to call a query, loop through it and return values???

[BingeThinker]

Hi,

I have two tables. One table is a "family" table and the other is "family members". There is a one to many relationship.

I have a calculated field which is "family make-up" and I want to query the "family members" table based on the "Family ID" and then retrieve each family member's age and gender. I want to loop through it based on the number of family members

The problem I am having is how to extract that information in Access. Below is what I have tried. It will open the query entitled "familymembersorted" but I don't know how to filter in only those family members based on a family id and then how to extract their age and gender.

DoCmd.OpenQuery "familymembersorted"
DoCmd.GoToRecord acDataQuery, "familymembersorted", acFirst
FamilyMemberGender = Queries!familymembersorted!Gender
FamilyMemberAge = Queries!familymembersorted!Age
FamilyCompositionString = FamilyMemberGender + FamilyMemberAge

For i = 1 To FamilyCount
DoCmd.GoToRecord acDataQuery, "familymembersorted", acNext
FamilyMemberGender = Queries!familymembersorted!Gender
FamilyMemberAge = Queries!familymembersorted!Age

FamilyCompositionString = FamilyCompositionString +
FamilyMemberGender + FamilyMemberAge

Next i


Any help would be greatly appreciated.

 

[stickers]

Hi,

I may not be understanding what you are trying to do properly, but I think I'd use a simple parameter query saying something like:

SELECT Age, Gender FROM FamilyMembers WHERE FamilyID = (parameter)

If that's totally off the mark, give a few more details about what you are trying to achieve, and someone may be able to help better!

 

[BingeThinker]

I guess my basic question is how do I call a query (which is not tied to a particular form) using commands within the form's code window?? What is the correct syntax??