I'm new to this forum and re-familiarizing myself with Access, which I haven't used in several years. I am designing a survey questionnaire in Access which collects information from a randomly selected adult in a household along with basic information about each member of that household.

In order to identify the randomly selected adult I have a make-table query that selects the name (hhname) and household member number (hhmemno) into a table (tblBdayName) for the adult who last had a birthday, i.e. minimum number of days from now() to day/month of birth.

The query is called in the OnOpen property of one of my forms (frmAdult_Migration), and it runs without throwing errors. However, it currently loops through all of the data in the database, and I need it to run ONLY for the record that it's currently in, i.e. I need it to loop through only the records for the household members included in that particular household and pull out the name of the person IN THAT HOUSEHOLD who last had a birthday.

My primary key is FILE -- a unique file number for each household, the table in which the household member data are stored is tblhhroster_subform, which is the table for the subform frmhhroster_subform. The main or parent table/form are tblhhroster and frmhhroster. The main form has a field for indicating how many people reside in the household, and the subform then collects info about each of those people.

I have a WHERE clause in my query to read as follows: WHERE "file = frmhhroster!frmhhroster_subform!file".
As I mentioned, the query runs with this designation, but not for that current record (file) only. I have tried writing the syntax several different ways, and this is the only way it runs without prompting a message requiring me to fill in the current file number.

I would be very grateful if anyone could tell me how to run this query for ONLY the current record. My SQL code for the query is below if that helps. Thanks!

SELECT hhname, file, hhmemno INTO BdayName
FROM (SELECT file, hhmemno, hhname, dob, IIf(DatePart("y",Now())-DatePart("y",Str(DatePart("d",dob))+" "+Monthname(Month(dob))+" "+Str(Year(Now())))>=0,(DatePart("y",Now())-DatePart("y",Str(DatePart("d",dob))+" "+Monthname(Month(dob))+Str(Year(Now())))),(DatePart("y","31 December "+Str(Year(Now())-1))-DatePart("y",Str(DatePart("d",dob))+" "+Monthname(Month(dob))+" "+Str(Year(Now())-1)))+DatePart("y",Now())) AS numdays FROM hhroster_subform WHERE "file=Forms!hhroster!Household_Roster!file" And DateAdd("yyyy",18,dob)<=Now())  AS A INNER JOIN (SELECT MIN(IIf(DatePart("y",Now())-DatePart("y",Str(DatePart("d",dob))+" "+Monthname(Month(dob))+" "+Str(Year(Now())))>=0,(DatePart("y",Now())-DatePart("y",Str(DatePart("d",dob))+" "+Monthname(Month(dob))+Str(Year(Now())))),(DatePart("y","31 December "+Str(Year(Now())-1))-DatePart("y",Str(DatePart("d",dob))+" "+Monthname(Month(dob))+" "+Str(Year(Now())-1)))+DatePart("y",Now()))) AS MinimumDays FROM hhroster_subform WHERE "file=Forms!hhroster!Household_Roster!file" And DateAdd("yyyy",18,dob)<=Now())  AS T ON T.MinimumDays=A.numdays;