By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,044 Members | 1,330 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,044 IT Pros & Developers. It's quick & easy.

Duplicate records created using two subforms in a main form

P: 75
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.
Jun 23 '14 #1

✓ answered by 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.

Share this Question
Share on Google+
10 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,488
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.
Jun 23 '14 #2

P: 75
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.
Jun 23 '14 #3

twinnyfo
Expert Mod 2.5K+
P: 3,488
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.
Jun 23 '14 #4

P: 75
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.
Jun 23 '14 #5

twinnyfo
Expert Mod 2.5K+
P: 3,488
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.
Jun 23 '14 #6

P: 75
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.
Jun 24 '14 #7

P: 75
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.
Jun 24 '14 #8

twinnyfo
Expert Mod 2.5K+
P: 3,488
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.
Jun 24 '14 #9

P: 75
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?
Jun 24 '14 #10

Post your reply

Sign in to post your reply or Sign up for a free account.