Prevent Conditional duplicate in set of records

[Abdulrahman7]

Good morning everyone,

I'm working on a project for the company, which has plants and i have to create daily report so there will be daily one data entry per each day.

I have Sub Form which it has the main data entry, there is 6 lines ( A,B,C,D,E,F ) it's in Combo box, each lines has record this should be filled in daily base, the problem that i wanted to be solved is i don't want to enter Duplicate lines per each report.

e.g

A 25 11
B 55 42
.
.
.
F 88 24


This lines shouldn't be duplicated only per day

*Note that this sub form linked to the main form which contain the date.



Thank you in Advance

 

[Micron]

I don't understand the situation and your details are very sketchy - no field names or examples, just letters and numbers.
You say you must enter data each day, but don't say if that means once per plant per day, or just once per day.
If once per plant per day, then I would set up a composite index, using two field: Date (but not time) and Plant id. Don't allow duplicates based on the combination of plant id and date. This would still allow you to enter a date for a daily report for each plant, but not repeat the date and any one plant id. When the table is in design view, click Indexes on the ribbon to start creating an index.

 

[Abdulrahman7]

Thanks Micron for replying,

Actually, in my database I have two tables, the first table is RecordTable has two columns which are RecordID and RecordDate, i have created the main form from this table, and the other table is called data table which include dataID, DateID(Linked to the record table) , lines and productionPerLine.

So, i have created sub form using Data table which is linked to Main Form( from RecordTable) when i want to insert a data first i'll enter the date then in the sub form i have to insert the lines (sub form is in datasheet view)

The data will be looking like this :

5/9/2017 A 25 11
5/9/2017 B 55 42
.
.
.
5/9/2017 F 88 24
5/9/2017 F 44 55 (this two F shouldn't be acceptable in a record, it should accept only one F)


I need VPA code or any other solution to restrict duplicated Lines to be entered

wish this is clear


Thanks again

 

[Micron]

Suggest you put the letter in its own field and create a composite index using the date and letter prefix field as the two fields in the index (per my previous reply). Then code to control this is not necessary. You could create a composite primary key with those same fields instead of a composite index if you prefer. Some say don't use composite primary keys but I don't see why not. Whenever I've asked why not, no one seems to ever have a good reason.

 

[xenou]

Some say don't use composite primary keys but I don't see why not. Whenever I've asked why not, no one seems to ever have a good reason.

Because ... oh nuts I lost my train of thought ... squirrel!

 

[xenou]

FWIW, if your first table only has RecordID and Date in it then I don't really see a need for it - that's pretty useless as far as the data that it contains.

Agree with Micron that the use of an index or composite key is enough to prevent duplicates. You might need to allow some method to Add or Update -- i.e, so if an entry is put in wrong it can be updated (or, alternatively, deleted and re-entered). Some form validation is usually better for non-expert users to check for duplicates before the form is committed, as the message about key violations can be somewhat confusing otherwise.

 

[Micron]

Agree with the error message thing. Was thinking to trap for it assuming the index was the accepted solution. Didn't pay much attention to the table structure.