Duplicate records created using two subforms in a main form

[wirejp]

I have created a database in microsoft access 2010 to show invoices for different customers in different countries. In doing so, I created using a two subforms in a main form. I have used the "country name" to link the subforms to the main form. When I enter new records into the subforms for a specific country, I realized that a duplicate record of an old record are being created in the subforms. What can I do to prevent this from happening? I tried to change the query link between the main form and subform to "invoice number" but the same problem has occurred.

P.S. I am novice to microsoft access 2010 and any help will be greatly appreciated.

[twinnyfo]

wirejp,

Is this a duplicate record in the subform or in the table beneath the subform?

Depending on how you have your subforms linked to your main form, you may just be looking at a pre-existing record.

Please explain better what is happening.

[wirejp]

hi twinnyfo,

Thank you for your prompt response. Can I can send the database file and the spreadsheet to you for your assistance? If so, kindly send your email to my inbox.

I am at my wits end trying to figure this out. I have browsing the web for solutions and looking at youtube videos for help but I am no wiser.

[twinnyfo]

No, you cannot send the db, as I can't open it at work. Also it is a violation of this site's policy to request personal e-mail addresses.

If you are a novice, that's OK--I was one, once, too!

But we can work toward a solution if we get more information concerning what is exactly happening.

[wirejp]

I am trying a database to track insurance premium fee invoices and administrative fee invoices. I created a main form called Premium Header which contain the insurer name and the contact name and address, along with the country name. I then created two subforms called: Premium fee invoices and Admin fee invoices. I had wanted to link the main form to the two subforms by the "country" name because a country can have more than type of insurance policy. Once I started inputting new records in the subforms, I realized that once I entered the "country" name in the main form, the database will suddenly populate the subforms with data from a previous record. So I thought nothing of this and I just manually update the current record with the new insurance invoice data. After inputting each record, I will save the record. It was only after I was scrolling through the records to check the data, that I noticed that in the instances where the same country had more than one record, the database will erase the data in the record and add a duplicate copy of a different recent record for this particular country. I hope this explanation helps.

[twinnyfo]

wirejp,

the way your forms are set up, every time you lok for a country, the subforms are filtering by all the records associated with that country.

First, you shouldn't be filtering those subforms by country, because that is not a uniquely identifiable index. In essence, what your forms are saying is that everyone from France has X insurance X address, X etc. Everyone from New Zealand has Y insurance, Y Address, Y etc.

This may not be what you want your forms to be saying, but that is the impression I get.

If you want something as simple as making sure that when you type in country, you always go to a new record on your subforms, then open your subforms in design mode, and change the "Data Entry" property to True. This will force a new record every time the form opens.

However, I don't think this will solve your long-term problems, as I don't believe your DB is properly normalized and indexed, as jimatqsi alluded to.

[wirejp]

I really appreciate your responses. Regarding your above suggestion to change the "Data Entry" property to True in microsoft access 2010, I am not sure where exactly I should go to change this property. I right-clicked on the "country" field box and selected properties. When I looked in the "Data" tab, I see the option "Enabled" as "Yes". Is this what you are referring to?

You are right about the country not being the unique index. The invoice number is the unique field for each invoice. But I did not select this field as the primary key in the either the Premium Fees invoice Table or Admin Fee invoice Table, as I had received an error message stating that "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Changes in the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit the duplicate entries and try again". Instead I selected the ID number is the primary key for all of the different tables. When I had created by the two queries for the premium fee invoices and the admin fee invoices, I created a link between the country for the main table and the two subform tables. However, the database characterized this relationship type as "indeterminate" and I was unable to enforce the referential integrity. So I think my database is not properly indexed and normalized as you have stated.

[wirejp]

I did some research and I think I found the Data entry property: - I right-clicked on the subform, in Design View, and selected the "Form Properties". In the Data tab, I changed the Data entry property from "No" to "Yes". Then I saved the subform. When I switched back to the Form View, all of the records were erased. So I started to manually input the records again. Then I saved the form. When I reopened the form, record was blank. Fortunately, I have a backup copy of the database and the data was not lost. Any help will be greatly appreciated.

[twinnyfo]

wirejp,

Your records should not have been "erased", just not visible, because you are adding new records. This may not be what you want either. With data entruy, whenever you open the form, there will be no records showing.

It is still difficult to understand exactly what you are trying to do with your form/subforms. It looks like there will be multiple records for each country, but how are you asking the user to go to the particular record that you want?

This is the key to your problem.

[wirejp]

You are correct. An invoice number relate to a particular country but there may be multiple records for different regions within this country under this invoice number. So I guess that the invoice number is not an unique identifiable index to use. I was thinking about assigning an "PremiumFeeID" or "AdminFeeID" (which will be autonumbers) to each record in the Premium fee subform and Admin fee subform respectively, while the ID number (an autonumber) will be the primary keys. The fields ("PremiumFeeID" and "AdminFeeID") in the subforms will be linked to the main form as the foreign keys while the ID number will be the primary key, when I create the query. Is this a good idea?

[zmbd]

This thread has been split to:

"Invoice layout in tables"

"Calculation of invoice Fees"

"Calculation of invoice Fees - Special cases"