MS Access - #Name? Error

[BennyLinton]

I have an Access frontend / SQL Server backend. Everything is working except one new field/textbox that is giving the #Name? Error. I created the new field in SQL Server (DateTime, Null), refreshed my ODBC linked table, checked to see that the field was visible from Access, then changed the query which feeds my form to include the new field, set the datasource for the textbox to the new allowed field from the query, but still I get the MS Access - #Name? Error. Any ideas? Thanks!!

 

[isladogs]

What is the name of that field in SQL Server?

 

[BennyLinton]

In SQL Server it is called "recertificationDate".
The name of the textbox is "txtRecertDate".
I can't figure out why all the other textboxes with dates are working fine on this form.

What is the name of that field in SQL Server?

 

[isladogs]

Thanks but I'm no wiser.
All I can suggest is to check your spelling is correct

 

[Minty]

Check in SQL server that it's not a DateTime2 data type.

Access doesn't like them and treats them as text.

 

[BennyLinton]

It's a standard DateTime, not DateTime2

Check in SQL server that it's not a DateTime2 data type.

Access doesn't like them and treats them as text.

 

[AccessBlaster]

It's a standard DateTime, not DateTime2

Did you set any attributes on the SQL side datetime field.

 

[BennyLinton]

None... I even went back through the process of deleting the field in SQL Server, adding it back, even changed the spelling, refreshed the ODBC link, revised the form's query and still same issue. Compact/Repair, etc. no luck.

Did you set any attributes on the SQL side datetime field.

 

[JHB]

.. revised the form's query and still same issue.

Open the form in design view, open the form's "Record Source" by clicking the 3 "...", then run the query, is the name you've typed in, in the control's "Control Source" shown in the query?

 

[BennyLinton]

Yes the field shows up as expected in the query results.

Open the form in design view, open the form's "Record Source" by clicking the 3 "...", then run the query, is the name you've typed in, in the control's "Control Source" shown in the query?

 

[JHB]

Then post a printscreen from both the output from the query, and the control's "Control Source"

 

[BennyLinton]

How do I insert an image? Can it be done from OneDrive?

Then post a printscreen from both the output from the query, and the control's "Control Source"

 

[JHB]

How do I insert an image? Can it be done from OneDrive?

Click "Post Reply", then scroll down a little then a button "Manage Attachment" appear, click it and select the file you want.

 

[BennyLinton]

Here is a screenshot of both

 

[The_Doc_Man]

That error says that the .ControlSource somehow does not correctly match up to any name that appears in the form's .RecordSource context. Could be spelling. Could be a hidden non-printing character. Could be that if you chose a query as the .RecordSource, that particular field isn't in that query. But the error is clearly Access telling you "I could not find this thing that you named."

The definitive test would be to put a breakpoint somewhere in the code on that form, perhaps at the end of the form's .Current routine. Then open up the Locals window, open the form's properties by clicking on the "+" sign next to "Me" in that window, and drill down to find the form's .Recordset property. Expand THAT with a "+" sign so that you can find the .Fields collection. Then examine the collection to see each item. You would have to visit each .Item in turn to expose the name. Yes, tedious - but you would only do this once. And that will unequivocally tell you what names you were using.

 

[BennyLinton]

I tried to find anything that I could comprehend in the locals window and could not. I've done this exact same task dozens of times with no problem - I create a new field, carefully checking its name and datatype, then query the table to see if it shows up which it does. I copy a textbox, in this case another date value like "applicationDate", then change its datasource from one available name to another in this case "recertDate". I'm puzzled...

 

[JHB]

Is the "recertDate" available in the field list, (click the Arrow down in the "Control Source")?
Could you've give the field another name by using the "Caption" property in the table, (see picture 2.), then in post #3 you wrote it is called "recertificationDate" and not "recertDate"?
Else post your database with some sample data.

 

[BennyLinton]

The database would not upload because of a security warning from this site or maybe the size. The recertDate/recertificationDate was just me shortening the name.

 

[BennyLinton]

Also for datasource the dropdown does show "recertDate" as a usable and available field.:banghead:

 

[isladogs]

Compact and zip your database. If still too big, remove unnecessary items and data unrelated to this issue