MS Access - #Name? Error (1 Viewer)

BennyLinton · Nov 20, 2018

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 · Nov 20, 2018

What is the name of that field in SQL Server?

BennyLinton · Nov 21, 2018

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.

isladogs said:
What is the name of that field in SQL Server?

isladogs · Nov 21, 2018

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

Minty · Nov 21, 2018

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

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

BennyLinton · Nov 21, 2018

It's a standard DateTime, not DateTime2

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

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

AccessBlaster · Nov 21, 2018

BennyLinton said:
It's a standard DateTime, not DateTime2

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

BennyLinton · Nov 21, 2018

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.

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

JHB · Nov 21, 2018

BennyLinton said:
.. 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 · Nov 21, 2018

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

JHB said:
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 · Nov 21, 2018

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

BennyLinton · Nov 21, 2018

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

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

JHB · Nov 22, 2018

BennyLinton said:
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 · Nov 26, 2018

Here is a screenshot of both

The_Doc_Man · Nov 26, 2018

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 · Nov 26, 2018

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 · Nov 27, 2018

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 · Nov 27, 2018

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 · Nov 27, 2018

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

isladogs · Nov 27, 2018

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

MS Access - #Name? Error (1 Viewer)

Registered User.

MVP / VIP

Registered User.

MVP / VIP

AWF VIP

Registered User.

Registered User.

Registered User.

Have been here a while

Registered User.

Have been here a while

Registered User.

Have been here a while

Registered User.

Attachments

Immoderate Moderator

Registered User.

Have been here a while

Attachments

Registered User.

Registered User.

MVP / VIP

Similar threads

Users who are viewing this thread