Autofill Date Field in Access 2007

Posted on 2009-02-23
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
    LVL 84
    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

    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 84

    Accepted Solution

    Sorry ... should be =Now() or =Date() ... too many environments running around in my head <g>

    Author Comment

    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

    Excellent solution - thank you!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now