iaingibbons
asked on
Autofill Date Field in Access 2007
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
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
Scott McDaniel (EE MVE )
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).
ASKER
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\SOFTWAR
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.
Picture1.jpg
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\SOFTWAR
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.
Picture1.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
ASKER
Excellent solution - thank you!