Is your MainMenuF open?

Value From a Form

Yes the form is open. The problem is the referenced parameter in a recordset. Richard talks about it but I already have an extensive sql statement and I do not know how to add the additional reference to it.

So what part of the query isn't working or which bit is missing that you need to add?

As Richard pointed out the recordset cannot find Forms!MainMenuF!SchoolYearStart from the form and needs to be put in the sql query as a Select/Where statement. I do not know how to merge the Select statement to get the form parameter with the Select statement that I need in the query. I have verified that the problem is the inability of access to get a form value in the recordset. If I replace the location of the form variable with a constant, the routine works fine. Within the recordset the query will not get the value for Forms!MainMenuF!SchoolYearStart

So with a button click or a Debug.Print of Forms!MainMenuF!SchoolYearStart what value is returned?

Forms!MainMenuF! is equal to the start date of a specific year. Currently it = 8/14/2023 and the routine works perfectly if I do not use a recordset and put the statement in the record source of the report itself. But that gives me 1 file with all records returned in it. I need to separate each record so I can email it and that is why I am trying to adapt my query to a recordset. I guess I can always get the returned query results into an Array and then call each record to email in a for next loop.

All the form references should be outside the quotes.  In other words, those form values should be *literal values* when you use string concatenation to form the SQL statement.

That did it. Thank you very much for your help. The routine works perfectly. The only thing I had to add, after removing the quotes from the Form reference, was # before and after the date. I appreciate your help very much.

You're welcome.  A form reference like [Forms]![MyForm]![MyField] is seen as a parameter by the query, and parameters don't always work, such as in a domain aggregate function.  E.g.:

If you put a form reference in the criteria of DMax() like this, it won't work and will give a run-time error, because DMax() can't resolve the form reference into a value:

     DMax(("[Field1]", "Table1","[Field2]=[Forms]![Form1]![Field3]")

If you use a parameter query as the domain as shown below, it also won't work and will give an error:

     DLookup("[Field1]", "ParameterQuery1", ... )

The parameter query has to prompt the user to enter a value for the parameter, and that WON'T HAPPEN here.  DLookup() won't be able to pause, prompt the user, and continue.  It just can't work like that.

DMax(), DLookup(), DCount(), etc. can only work with data that are already available, such as literal values.