So you want to use the same query with two different forms and get the value from whichever one is open?

Hi Richard, Yes, I have 2 different forms, 1. enters new student class data. The 2nd form uses a different table from the 1st form but updates which class the student needs to be moved to when the student missed a class because he was absent.  Absentee students are placed in a waitlist table, different from the student schedule table which holds when each student is scheduled to attend his next class.  Each student is an apprentice who is already working with an employer and attends class 5 times a year over a 3 year period for 1, 2 or 3 days.  The query finds and holds whether the employer has another student in a class that either form is being used to enter another employeed student into the same class. If there is then the forms display a message to the user.  Hope this makes sense. The same query is used with 2 different forms

Anything that looks like [Forms]![FormName]![Field] are treated as "parameters" in queries.  This type of queries are called "parameter queries," which Richard has covered in his videos.  A parameter's name is unchangeable, so you cannot dynamically changed them in the manner you described.  You can assign a different value to a parameter, but you cannot change the parameter's name.  

Since you only have 2 forms you need this done to, I suggest you just make 2 different queries.  But if you really want to do it dynamically, there is a way to do it with VBA:

1. Instead of [Forms]![FormName]![Field], use a generic parameter name, e.g. [My Form Value]
2. In the form's Open event, open the "query def" of the query and assign the necessary value (i.e. the value contained in [Forms]![FormName]![Field]) to the parameter.
3. Also in the Open event, assign the query above to the form's record source.

I don't know if Richard has a video on setting up parameter queries with VBA.  The picture below shows what it entails.  You take the name of the parameter and use VBA to assign a value to it in the manner shown.

It sounds to me like there's a more fundamental issue with your database, and that you shouldn't be using multiple tables to store the same type of thing. You're dealing with all students here, so instead of moving students around between different tables, why not just assign them a status or a category? That would be a whole lot easier and better from a database standpoint.

Hi Richard, No fundamentals are OK. I am reading a 3rd party Db that I am only allowed to read and the reason why there are 2 different tables. I actually had programs in place in my Db to do this which worked however the business decided absentees needed to be stored in the 3rd party db not mine.  As you are aware business processes rule, not IT! So yes, unusual but as a tech help or developer lesson I think it would be good lesson.  Thanks for the interest.

No, I understand completely. I've worked with companies in the past, and they were working with legacy systems or getting data from vendors, and we had to deal with how it was provided. When I had absolutely zero say on what the higher-ups could do with the data. With that being said, is this a situation where only one of the two forms could be open, and the query needs to get the data from that form? Or is there a possibility that both forms could be open because we might be able to use some function trickery to figure out which one's open, or if both are open, choose one over the other? Either way, it's going to require a custom function. You can do something like:

X: GetFormParameter()

And then your function could use IsLoaded() to check and see which of those forms is open and get the value from it. And if both are open they can prioritize whichever one you specify. This should get you started at least.