Preventing Access form from creating multiple/duplicate records

Question

Hi,

I have created a form that has a drop down list that allows the selection of multiple values. After entering a few test records, I closed and reopened the database, and I found that I have duplicate form records. I originally entered 3 records, which included a date, name, personnel number, and action type (multiple value field). I choose 3 action types on each test record. Now, I have 9 records, with 3 total duplicate records for each entry. There is only one row per entry in table view, though.

I will be using this database for multiple users and predict that this behavior will confuse people if they need to go back through form records for review/printing. How can I prevent Access from creating multiple/duplicate records in form view?

Thanks in advance for your time and assistance!

Answer 1

I finally figured out how to fix this issue for my form, and I suspect it may work for yours as well. First, make sure the form (in design view) only has the multivalue field's "fieldname" on it and not the "fieldname.value". Then try going to the form's properties, and click the "..." icon. This will open a query builder, look along the bottom and see if the "fieldname.value" field is listed. If it is, try removing it. Mine works when just the "fieldname" is included rather than the "fieldname" and "fieldname.value".

Answer 2

And, if you posted your SQL Statement as requested, we would have noticed that and told you to remove the .Value reference. To explain, the .Value refers to each individual choice. So if you use that in a query, the query will return a record for each selected value.

On the other hand, I don't recommend using a query as the recordsource for a form. Use filters instead if you need to restrict records.

Answer 3

Thank you so much JaimeF!!! This has been driving me nuts and your solution totally worked. I am so grateful to you for posting the solution.

Answer 4

Somehow you have managed to engineer the form so that it is showing the result set of a query in which each row in the table is showing a separate instance for each value in the multi-valued field.  If the form is bound to the table this should not happen, as the multi-valued field will be presented on the form via a combo box.

However, unless you really need to use a multi-valued field, i.e. if your database is interfacing with SharePoint in the very specific context in which such a field is required, the real solution is not to use a multi-valued field at all, but to design the database in accordance with the established principles of the database relational model.

What you have here is a many-to-many relationship type between personnel and action types.  The way in which a many-to-many relationship type is modelled in a relational database is by means of a table which resolves the many-to-many relationship type into two one-to-many relationship types.  So you'd have table like this:

Personnel

....PersonnelID (PK)

....FirstName

....LastName

....etc

ActionTypes

....ActionTypeID (PK)

....ActionType

and to model the relationship type:

PersonnelActionTypes

....PersonnelID (FK)

....ActionTypeID (FK)

The primary key of this last table is a composite one made up of the two foreign key columns.

For an example of this sort of a basic binary (2-way) relationship type and how to represent it in forms see StudentCourses.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file shows three ways of presenting the data in a form, but the usual, and by far the simplest, approach is the conventional one of a form based on the main referenced table, and within it a subform based on the table which model the many-to-many relationship type.  Unlike the other methods this requires no code whatsoever (unless you want to include a navigational combo box to go to a record, or to use the NotInList event procedure of the combo box in the subform to add a new course (action type in your case), as in my demo).

Answer 5

I am experiencing the same problem. I am tired of hearing the answer to just not use this function. I think that is a response from people who simply don't know. There must be a way to make this work.