#Name? in subform field

[Zydeceltico]

Hi All -

I've inherited management of a legacy DB at work. The DB was created by a gentleman who is of course no longer with us. On top of that his naming conventions leave quite a lot to be desired and the DB itself is ponderous to put it lightly.

In any regard, I have a form (Calibration Input Form) that has a subform (Calibration Input Subform) on it. On the subform is a text box named "Recalibration Due Date." This is a calculated field. He used an expression for the calculation.

The expression is: =[Format$]([Calibration Date]+[Frequency Number],"mmmm yyyy").

When the main form is opened the textbox "Recalibration Due Date" shows "#Name?" instead of a date value.

The Record Source on the subform's property sheet shows: SELECT [Calibration Device Table].[Frequency Number], [Calibration Results Table].ID, [Calibration Results Table].[Device Number], [Calibration Results Table].[Calibration Results], [Calibration Results Table].[In Tolerance?], [Calibration Results Table].

The Record Source on the main form's property sheet shows:

SELECT [calibration device table].[frequency number],
       [calibration results table].id,
       [calibration results table].[device number],
       [calibration results table].[calibration results],
       [calibration results table].[in tolerance?],
       [calibration results table].[calibration date],
       [calibration results table].[recalibration due date],
       [calibration results table].comments,
       [calibration results table].initials,
       [calibration results table].[has tool soaked?],
       [calibration results table].[calibration test point 1],
       [calibration results table].[calibration result 1],
       [calibration results table].[calibration test point 2],
       [calibration results table].[calibration result 2],
       [calibration results table].[calibration test point 3],
       [calibration results table].[calibration result 3]
FROM   [calibration device table]
       LEFT JOIN [calibration results table]
              ON [calibration device table].[device number] =
                 [calibration results table].[device number]
ORDER  BY [calibration results table].[calibration date] DESC;

If someone can tell me how to strip bulk from the db, to get it to a manageable attachment size, I will attach it to a post.

Thanks,

Tim

 

[Gasman]

Add the {Calibration Date] to the subform recordsource if it is in the Calibration Device Table.?
Or refer to the parent forms controls, as those fields appear to be in the recordsource.?

I'd like to know what has changed however if I was now looking after that DB.

 

[jdraw]

Hi Tim.

First I agree with Gasman's question "What has changed?" to cause this error to happen.

Since the only tables involved are
a) [calibration device table]
b) [calibration results table]
and your main form and sub form
It seems you could create a new database, then import only these 4 objects.
Then post a copy of that database.

I agree with you that the naming and normalization are not following generally recommended approaches.
Good luck.

 

[Zydeceltico]

Hi Tim.

First I agree with Gasman's question "What has changed?" to cause this error to happen.

Since the only tables involved are
a) [calibration device table]
b) [calibration results table]
and your main form and sub form
It seems you could create a new database, then import only these 4 objects.
Then post a copy of that database.

I agree with you that the naming and normalization are not following generally recommended approaches.
Good luck.

That's the thing. Nothing to my knowledge has changed. No one here - except for me - has any experience with Access at all. And I haven't ever touched this DB at all.

The only thing that I know of that may remotely have had some effect is that the network was overhauled recently but that shouldn't have had any effect on this db as it is completely self-contained.

I will try creating a new DB and posting.

 

[arnelgp]

the Recordsource ofmain form and subform are almost identical.
you can modify the recordsource of the subform and include the Calculated Column:

Recalibration Due Date: Format$([calibration date]+[frequency number],"mmmm yyyy")

then instead of Expression in the Controlsource of the textbox, bind it to the Calculated column.

 

[Zydeceltico]

Hi jDraw and Gasman,

In attempting to create a new db by importing the required tables and forms, I'm thrown an error stating "There was an error loading an ActiveX control on one of your forms or reports."

This reminds that when I first took a look at this the other day I received a message upon opening the full DB that Shockwave Flash was missing.

I compacted and repaired and that message went away.

Is Flash ever used with Access?

If it is, I wouldn't put it past the original in-house developer to have tried to do something "fancy" that is now broken if Flash was not located on the network after the recent overhaul.

------- so I'm importing the forms one at at time and it appears that the culprit is somewhere associated with form "Calibration Input Form" as the error was not thrown when I imported the other two subforms.

Thanks!

Tim

 

[Zydeceltico]

I'm wondering if the Format$ portion of the expression is even necessary. I doubt it and I'm wondering if Vaughn (the OG developer) was trying to do something with the formatting of the calculated textbox that we don't in actuality need.

 

[arnelgp]

You already have the "Recalibration Due Date" on your Recordsource (but coming blank) that i why #Name is coming up (meaning dup name).
also i disable DoMouse() or something on the subform, the function/sub is not included on your db.

 

[Zydeceltico]

I changed =Format$([calibration date]+[frequency number],"mmmm yyyy") to =[calibration date]+[frequency number] and guess what?

It works just fine. May not be a fancy font or color but it works.

I'm thinking the ActiveX connection was the issue.

 

[The_Doc_Man]

If so, then you MIGHT have fallen victim to a Windows Update that affected the security issues of the ActiveX stuff. That is one of the more common changes made during an Office update (which would probably occur simultaneously with a Windows update.) By any chance did this stuff start acting up on a Wednesday? 'cause Windows likes to update on Tuesday nights.

 

[Zydeceltico]

If so, then you MIGHT have fallen victim to a Windows Update that affected the security issues of the ActiveX stuff. That is one of the more common changes made during an Office update (which would probably occur simultaneously with a Windows update.) By any chance did this stuff start acting up on a Wednesday? 'cause Windows likes to update on Tuesday nights.

I strongly suspect that it did .......AND we've had a lot of data server issues over the past month.

 

[Zydeceltico]

If so, then you MIGHT have fallen victim to a Windows Update that affected the security issues of the ActiveX stuff. That is one of the more common changes made during an Office update (which would probably occur simultaneously with a Windows update.) By any chance did this stuff start acting up on a Wednesday? 'cause Windows likes to update on Tuesday nights.

Doc - is there anything that can be done to rectify the ActiveX security issue? I very much think that is what has happened. Reinstall Shockwave?

Our network manager is notoriously heavy on what he considers security.

Actually - is there a way to check to see if an ActiveX control is sued in the db?

 

[Zydeceltico]

This DB would have been created around 2008.
So I went into the VBA editor and checked references. It is referencing Microsoft ActiveX Data Objects 2.1 Library and Microsoft Access 16.0 Object Library.

Then I closed the db and opened a new DB that I developed. (All of this is happening in MS Access 2016 btw).
My new - and unrelated DB - is referencing Microsoft Access 16.0 Object Library and Microsoft Office 16.0 Access database engine Object Library.

Is it possible that the reference to Microsoft ActiveX Data Objects 2.1 Library would be broken or corrupted?

 

[The_Doc_Man]

The MS ActiveX Data Objects is not a "default" reference. Someone manually added that reference in 2008. I would have no concern about that reference "missing" in the new DB. Since you said compared references, you would have seen a "Missing" or "Broken" annotation for the ADO library on the old DB.

Your should be aware that Shockwave is a deprecated feature in Win10 due to Adobe's choice to allow it to reach "end of life." I don't offhand know what to do about it. Your IT guys may be totally innocent in this case. You can PERHAPS find clarifying information by using the Win Start >> Settings >> Update and Security >> View Update History path. You will at least be able to see WHEN the last updates occurred.

https://helpx.adobe.com/shockwave/shockwave-end-of-life-faq.html

You could TRY to go into the 2008 DB's references list and uncheck the ADO library, then close the app, then open it again and check the reference (to force it to re-evaluate the linkage). Beyond that, I don't know because I haven't used Shockwave in a long time.