Saving Dates in a Table

[bo16tx]

Have a database that was working well and then: when you enter a date in a date field directly in the table, either click off the field or save, doesn't matter. The date appears to be save, however when you close the database and reopen, the dates have not been saved.



Also, added a new date field to the table and it works just find. There are about 18,000 records in the table.

[JoeM]

Are you sure that you are opening the same copy of the database?
Are these tables directly in Access, or linked from another source?
Do you have any VBA code in your database? There could be some code running to remove those.

[bo16tx]

Yes
Yes, NO
NO

[JoeM]

That doesn't make any sense.

If you are able to, can you upload a copy of your database so we can see it and test it for ourselves?
Just be sure to remove any sensitive data.

I am not able to download it from my current location, but can later tonight when I am home (or someone else may take a look in the meantime).

[bo16tx]

I just joined the forum this morning!

Not sure how to upload?

It's about 20 megs.

[JoeM]

If you go to the Advanced Reply option, take a look at the section entitled "Additional Options". There is one there called "Manage Attachments".
It is usually best to zip up your database before uploading.

[bo16tx]

You system won't let me upload the zip file.

Is there an email I can send it to?

[JoeM]

It is not my system, I am just a member here like you!

If it doesn't like the zipped file, justy try uploading an unzipped version of it. I would recommend running a "Compact & Repair" first, to delete the bloat and get the database size as small as possible.

[bo16tx]

I was out of the office yesterday.

Didn't mean to imply it was your system.

I tried the unzipped version and that didn't work either. Also used compact and repair first.

Is there an email where I could send a copy of the zip file to you?

[JoeM]

I don't share my email address. You could upload it to a file share site (like Google Docs or BoxNet or something like that), and post a link to it here.

[bo16tx]

Understand!

We have posted the zip version to our website: http://www.jble.af.mil/Units/Army/Eustis-Enviromental/

In the Center column, go to the Fort Eustis Compliance tab and under the Hazardous Waste heading, open the Latest HWTS file. This should allow you to download the zip version.

Once in, open the FE Transaction Table.

Under the Manifest Number column, you'll see 2 fields one with zzzz and the other with xxx. These are the 2 records that we are using to show the problem.

When you add dates in the Manifest Date field and either click on another field to save or hit save on the table, the dates appears to be save. If you close the table and reopen, the date appears to be saved.

If you close the database and reopen, the date goes away. This is the issue.

On another copy, I added another date filed and it works just fine. Just don't understand why the Manifest Date is not working.

This database has work well for years and then recently just stopped saving the dates.

[JoeM]

OK. I will try to take a look at it tonight or this weekend when I am at a location where I am able to download files off of the internet.

[JoeM]

I am sorry, but I cannot find this Compliance Tab you mention on your website.

[ssanfu]

@JoeM,
I finally found the download button.


@bo16tx,

I opened the table and added the two dates.
I closed the table and reopened it. Dates still there (I used today's date to ensure I was looking at the correct records)
I closed the dB and reopened it. Opened the table. Dates still there.

When I extracted the dB, the size was almost 21,000 KB. I did a "Compact and Repair". The size dropped to a little over 17,000 KB.
Then I entered the dates. I think doing the "Compact and Repair" probably fixed some corruption in the dB.

Advise you to do the same.


==============

Other issued I saw
------------------
The top two lines of EVERY code module should be

Option Compare Database
Option Explicit

Then do a "DEBUG/Compile HWTS".
You will find several errors - one of which is "End Sub" instead of "Exit Sub"

- The object naming is horrendous (IMHO).
- There are many subroutines that do not have an object. (ie code for the click event of a button that has been deleted. Deleting an object does not delete the attached code.
- You have a *LOT* of spaces in object names. Bad Idea!
- You have special characters (example: in table DODACC - field name "Gp/Bde/Dir")
- You have reserved words as field names
- You have Make Table queries. Make Table queries can cause corruption in your dB. A better option is to create the table once, then delete existing records before appending new records.
- The dB is still an MDB instead of being converted to ACCDB. Since you have Access 2013, converting to the newer format is a very good idea.
Remember - 1)back up, 2)back up, 3)back up BEFORE you try converting to the new dB format.

- Lastly, your table design looks like an Excel workbook was imported into Access. The table designs are not a relational design. This is called "Committing Spreadsheet" in the RDBMS world.
Excel worksheets are typically short and wide. Database tables are typically tall and narrow.

You might also want to read about Autonumbers:
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp


Not trying to slam you - just pointing out things that could/will bite your six............

[John_G]

I was able to download your database. I have your answer, and you're not going to believe it.

The field you are entering data into is called Manifest Date. Apparently Access doesn't like the word "Date" (preceded by a space) in the field name. I changed the field name to Manifest_Date (no space) and voila! - it worked. I suspect any field like [xxx Date] with a space will have the same problem - I found the same thing in another table.

Changing that field caused at least one macro to fail, so you might have some work to do, but I'm quite sure that's the issue.