Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Autofill Date Field in Access 2007

Posted on 2009-02-23
Medium Priority
Last Modified: 2013-11-28
I am a novice at Access, I can create a table and create a form etc but that is it. I have built a very basic database for a project I am running. There is 1 table with 3 fields, thats all. The fields are ID, Account Number and Date and Time. The date and time field is set to show the date and the time (obvious I know!)

The Account Number is an 18 digit number which is entered via a barcode scanner. What I want the database to do is open up a form, populat the account number field from the scanner (which it does now without issue) but I want it to then, automatically populate the Date and Time field with the current date and time and create a new record so I can scan the next barcode and so on.

I have 68,000 barcodes to scan so I want to automate this as much as possible.

Any Help Much Appreciated.

Iain G
Question by:iaingibbons
  • 3
  • 2
LVL 85
ID: 23711380
Open your table in Design view, the set your Date field's Default value to GetNow() (if you want the entire date + time) or GetDate() (if you want ONLY the date).

Author Comment

ID: 23711497
That didn't work, I get the error you can see in the picture and the help text says...
This error can appear if:
You have exceeded the maximum number of columns allowed in a table or the maximum number of locks for a single file.
The indexed property of a field was changed from Yes (Duplicates OK) to Yes (No Duplicates) when duplicate data is already present in the table.
If the maximum number of locks per file was exceeded, you can increase the number by editing a registry entry for the local computer.  However, this is not a recommended option.
Find the MaxLocksPerFile registry value using the Windows registry editor (regedit.exe) and increase the value. The MaxLocksPerFile value is stored as part of the following key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\ACE
If you edit a value in the Windows registry, there is no guarantee that the change will not corrupt the registry and render Windows unusable.  Edit the registry at your own risk.  If you do, it is highly recommended that you make a backup of the registry before performing any edits.
If you have changed the Indexed property of a field and duplicate data already exists in the table, you can either reset the Indexed property back to the previous setting, or remove the duplicate records from the table.

LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 23711556
Sorry ... should be =Now() or =Date() ... too many environments running around in my head <g>

Author Comment

ID: 23711709
That works great for the date and time, it populates the field no problem, but the database stays on that field so If i scan another barcode it tries to put the code in the date field for the previous code. How do I tell the database to move to a new record after it populates the date and time field? Thanks for all the help on this!

Author Closing Comment

ID: 31550083
Excellent solution - thank you!

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question