Main-Form Date field

[fajast]

Hello All!!!

I have created a table Named Summary containing following fields: 1: AutoNumber, 2: Datefield, 3: Equipment, 4: Job. The idea is that this table will serve as a maintenance logbook for whole year. As there will be many jobs / equipment attended on each date so we will have many records on every date with common value in Datefield.

Next I created a table tblDatefield with just one column that contained all the dates from 1st January to 31st December. This field is the PrimaryKey of the table. I made relationship between both the Datefields and then created a form with sub-form. The main form contains only one field the is the Datefield from tblDatefield. While the sub-form contains all the fields from Summary except datefield.



I did this because I wanted to pick a date in the mainform and view the corresponding maintenance records on that date and enter new records in the sub-form if needed BUT as soon as I select a date in the mainform and press enter it refuses to go ahead because it says that "they would create duplicate values in Index, Primary key or relationship". What am I doing wrong?

[Micron]

I see no reason for a date table from what you describe. By "linking" you've likely created as situation where you're trying to add a duplicate date into the PK field of the date table, which again, doesn't seem to be required. Nor do I think the form/subform design is correct, unless you want to see all maintenance records regardless of what piece of equipment has such records. If not, then the design is suspect.

What you probably need is all desired equipment info on the main form and a subform that also contains a hidden field for the equipment id. The form/subform child relationship is set to be the id field. That will provide all maintenance records for an equipment id, to which you can add new records if desired. This will also expose existing records to edits or deletions, so a separate input form for these records might be a better method.

If none of that helps, a pic of your table relationships might help and provide more information for evaluation.

[fajast]

Thank you for your reply. Actually we maintain logbooks in Excel where we keep record of all the maintenance activities in a day. We create one sheet for every day of the month. The date is entered once and it is copied in front of all the maintenance activities performed on that very date. At the end of the month the data from each sheet is copied to a summary sheet where the cells with dates become useful. I am attaching screen shots of the access table here.

[Micron]

So you are operating workbooks as databases, but I still don't see the need for a table with a date field if you are trying to migrate the functionality of the workbooks to Access. Maybe take a look here http://www.databaseanswers.org/data_models/
there must be a dozen examples (some might be repetitions) of maintenance schemas, I suspect none of which will have a table solely for dates. 32.9 might be most applicable to your needs.

Note: there is no way you can design this properly with 1 or 2 tables. Maybe read up on normalization?
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/

or find others more to your liking.

[ssanfu]

<snip>
Next I created a table tblDatefield with just one column that contained all the dates from 1st January to 31st December. This field is the PrimaryKey of the table. I made relationship between both the Datefields and then created a form with sub-form. The main form contains only one field the is the Datefield from tblDatefield. While the sub-form contains all the fields from Summary except datefield.

I did this because I wanted to pick a date in the mainform and view the corresponding maintenance records on that date and enter new records in the sub-form if needed BUT as soon as I select a date in the mainform and press enter it refuses to go ahead because it says that "they would create duplicate values in Index, Primary key or relationship". What am I doing wrong?

So your main form has a record source of the table "tblDatefield" (or a query based on the table "tblDatefield").
I would guess that you have the main form default view set to Single Form and the text box is bound to the field "Datefield". When you try to go to a different date, you are actually trying to change the date for the current record, which would create a duplicate date.
Since "Datefield" is the PK field, and thus unique, you get the error.


If you are using a text box, combo box,list box, etc to search for records, that control must be unbound. There are many ways to to search for records if you google for them.

Here is one:
Using a Combo Box to Find Records