looping through records until matching criteria is found, then filling in a record

[TonyB]

Hi,

Not very pro with VBA - been struggling with this for hours!
Here's the code that works so far, and is triggered on form load. It works, but only if it can find a PI, and they are the first entry in the recordset:

If Forms!frmProject!frmSubPerProj.Form.RolePerProj = "Principal Investigator" Then
        Me.Principal_Investigator = Forms!frmProject!frmSubPerProj.Controls("fullname")
End If

To give you a better understanding of what's going on:
Firstly, I have a form open called "frmProject" which has a field called "Principal_Investigator". There is a sub-form called "frmSubPerProj" which holds the records of people associated with a project; including their names and their role within the project.

What I want to do is every time the form is opened, the "Principal Investigator" field on "frmProject" needs to be filled with the name of the Principal Investigator found in the subform ("RolePerProj" is the field in the subform that denotes the PI). If no principal investigator exits, then leave the field blank.

I think I need to use some looping system here, so that if the first record returns a "RolePerProj" value of false, then check the next records until Access finds the first "RolePerProj" value as being "Principal Investigator". I've tried various things I've found on the internet, but none seem to work for me.

Thanks

[ItsMe]

Perhaps the solution is to use the form's On Current event. The On Load event only fires at the time the form is Opened/Loaded.