Access redefined the Date function

[dokuin]

I have a form based on a query based on a table.
I imported the original table from another database. I initially failed to realize the problem with a field in that table named “Date”. I set up a query using the same name, then a form to display the info with that same field name.

Now, when I try to do an auto-fill-in of the Date in the BeforeInsert event (this is a notes type of form), I run into a problem. Access has redefined Date from a function to a field. So when I try
Me!Date.Value = Date()
the Date field does not change.

I have gone through the entire database and changed all field names from Date to UpdateDate. I have thoroughly scoured the entire database to remove all references to the old date field. I have saved, compacted, and decompiled the database, all to no avail. Because when I try again to enter this line in the BeforeInsert event
Me!UpdateDate.Value = Date()
I get an error stating that my database “can’t find the field “Date” referred to in your expression”. It also removes the parenthesis after Date.

When I highlight and right click on the word “Date” in the BeforeInsert event module and click on “definition”, I get the definition as an access field under my form. If I go up to the DateTime section in the classes window and go to Date in the member window and click on it there, it defines date as a variant under VBA.datetime (the correct one for the date function).
So both “Date” definitions exist.

I have also tried to highlight and copy the correctly defined Date from the object browser window and paste it in the module, but access still uses the wrong Date field definition in the module.

How do I get rid of the no longer existing definition of Date as a field in the classes and members area so that access will stop referring to a long dead field?

 

[KevinM]

Not sure 'UpdateDate' was a good choice when you re-named it.

Anyway have you tried....

Me![UpdateDate] = Date

 

[dokuin]

I just tried putting in your suggestion:
Me![UpdateDate] = Date
It gave me the same error message. I am using other Me!FieldName.Value = function code and the other code works fine. The problem seems to be in the definition of Date itself.

And ya, I know Updatedate doesn't look good by itself, but it is inline with other field names in use: UpdateBy (who last updated this), UpdateDate (date of last update), etc. But I am still considering alternate names, like NotesUser, NotesDate, etc.

 

[KevinM]

Me.UpdateDate = Date ?

Can you post a demo of the db with the form and table (state Access version).

Also have you checked to see are there any missing references in the vb window?

 

[dokuin]

Attached is a simplified sample of my database. There are 2 forms, frmCustomerNotesWorkingVersion and frmCustomerNotesNONWorkingVersion. If you go into the BeforeInsert event in each and highlight the Date or Time word, right click for the menu, then go to definition, you will see that in the NON working form the Date and Time are defined as fields (which no longer exist). In the working form those two words are defined as variant.

If you try to add a new note by clicking into the note field, the NON working version gives you an error message.

I am trying to find out how to get Access to reference the correct definition of Date and Time for their functions to fill in the current date and time.

Because I can make a working version of the form, I know I can rebuild my forms to (apparently) correct this error for now. But if I don't figure out how to correct it, I am afraid that this problem might creep back into my database in the future. And my actual database has far too many and too complex of forms to make the task of a complete rebuild anything but a headache that I would like to avoid at all costs.

 

[Jack Cowley]

Create a new, blank database and import your tables, queries, etc. Now your code should work. I tried this with your sample bd and it cleared up the problem.

hth,
Jack

 

[dokuin]

Thanks, Jack! That worked! And it was pretty easy.
Informative details for anyone else:
From my blank new database,
File - Get External Data - Import
Locate old database to import from.
Import all tables (and links), queries, forms, reports, macros, modules,
and under Options: custom tool bars, relationships, import/export specs.
Copy by hand: startup settings, security settings.
Relink the linked tables, rename the old and new databases. This cleared up the invalid definitions in the code. It also reduced the database size.

 

[neileg]

If this reduced your database size, you need to consider doing a repair and compact on your application from time to time.