Autofill multiple records into a subform

[djeverett01]

Ok. I have looked almost all day for a solution to this question on this boards, but I can't seem to find it so I am begging now for help from the Experts.

I have a form with a continuous subform. What I want to do is automatically fill in the subform based on the current record the main form is on.

A little more detail. I have a group of people and they each have a set of goals based on their ID. I want to autofill the subform with all of the goals a person may have.

I am using this code to do this:

Private Sub Form_Current()
Dim Info As DAO.Recordset, SQLText
SQLText = "SELECT * FROM [qryGoals] WHERE [ID] = '" & Me![ID] & "'"
Set Info = CurrentDb.OpenRecordset(SQLText)
Me![Goal] = Info![Goal]
End Sub

This works great except for the fact that I hae some records with more than one goal so If teh person has 2 goals I want to fill in the subform with both goals. Is this possible?

Dawn Nelson Everett

http://www.bign.com/jdeverett

 

[TomHW]

My question is why are you trying to do this through code as opposed to having the subform linked to the main form using the Link Child Fields and Link Master Fields properties?

Data source for the subform is "SELECT * FROM [qryGoals]"
Link Child Fields is "ID"
Link Master Fields is "ID"

Now whenever you change something on the main form, the subform filters to only show records where ID on the subform is equal to ID on the main form.

Hope this helps,
Tom