Getting #Name? error on form

[cnstarz]

Hello! I'm a newbie trying to build a new database from scratch, and am learning as I go.

I have a linked table called Main (this table already exists in a previous, hard-to-use database); the form for Main is called "MainForm".

I have a query called "Expiration" which which calculates the Expiration dates of TS Clearances for each record using the DateAdd function.



On MainForm, I have a field called "Expiration Date". This field is supposed to return the calculated expiration date from the "Expiration" query. Of course, this date is different from record to record because not everyone's clearance expires on the same date.

The problem is that the "Expiration Date" field is returning a value of "#Name?". The control source of that field is "= [Expiration]![ExpirationDate]". I arrived at this control source by clicking the "..." button next to "Control Source" in the Property Sheet while in design view of MainForm, and simply navigating to Queries>Expiration>ExpirationDate>"<Value>". I then hit "OK" and it populated the Control Source field for me.

Any ideas as to why the field displays "#Name?" when i switch over to Form Mode to view the form? Thanks!

[kimmer]

It just doesn't work like that. A Control Source property cannot reference an unopen object that isnt its own record source.

You can base the form on a query that includes your calculated field or
you can do the calculation directly in the Control Source property (provided the original date field is in the record source for the form).

If your original date isnt part of the record source for the form, you can insert a subform based on your query.

[cnstarz]

So I can just create a query, and then create a new form based off the query. If the end-user were to edit any of the fields of this new form, would the changes also be reflected in the table that the query is based off? If so, then this solution will be what I need.

[slave138]

Yes as long as all the required fields are included in the query, the user as the permissions to write to the table, etc...

[slave138]

Unless you are recording the expiration date in a table, I would suggest using Kimmer's second suggestion and just do the calculation on the form itself.

In the Expiration Date field of your form, set the Control Source to something like:
=DateAdd("d", 14, recordDate)

If the recordDate (or whatever you call it) isn't on the main form you would have to add it but you could hide it from sight by setting the Visible property to False if you don't want both dates displayed.