Run query for current record only

[lmh24]

Hello,

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 BdayNameFROM (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())))),(DatePa rt("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())))),(DatePa rt("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;

[June7]

Why is criteria within quotes?
WHERE "file=Forms!hhroster!Household_Roster!file"

Instead try:
WHERE [file]=Forms!hhroster!Household_Roster!file

[lmh24]

Why is criteria within quotes?
WHERE "file=Forms!hhroster!Household_Roster!file"

Instead try:
WHERE [file]=Forms!hhroster!Household_Roster!file

Thank you -- I've tried that, but I get an "Enter parameter value" message prompting me to enter a value for file when I have it set up like that. If I enter the current file number it will work, but I need it to work without me having to enter the file number each time.

The quotes are there because that was the only way I could get it to run without throwing the enter parameter value message. But it loops through the entire data set, not just the data for the current record.

Any other suggestions?

[June7]

The parameter prompt pops up because Access can't find the referenced item.

Want to provide project for analysis? Follow instructions at bottom of my post. Identify objects involved in the issue.