how to solve the error when creating duplicate records?

[Rafegh]

hi
i have designed a form that creates records in a table. As I don't want to have duplicate records, I have made 3 fields as primary key in that table. By pressing a button in the form, records are created in the table. When User wrongly clicks the button with the same data on the form, access shows a message that records could not be created and the form could not be closed. I have tried to solve the problem with showing a message box and not entering the sub, but it didn't raise the problem. How can I fix this problem?

This my code:



Private Sub cmdCreate_Click()


On Error GoTo aaa
bb:


For i = 0 To Me.SampleCounter + 1
Me.LayerSubform.Visible = True
LayerSubform.SetFocus
DoCmd.GoToRecord , , acNewRec
[LayerSubform]![ProjectCode] = ProjectCode
[LayerSubform]![ProjectName] = ProjectName
[LayerSubform]![Employer] = Employer
[LayerSubform]![Contractor] = Contractor
[LayerSubform]![SectionNo] = SectionNo
[LayerSubform]![LabName] = LabName
[LayerSubform]![TestType] = TestType
[LayerSubform]![LayerType] = LayerType
[LayerSubform]![AggType] = AggType
[LayerSubform]![MineName] = MineName
[LayerSubform]![SampleDistance] = SampleDistance
[LayerSubform]![SampleCounter] = i



Next i



Exit Sub

aaa:


MsgBox (" data already Exist")
Exit Sub

[ranman256]

You should not be using code to add records. Access does all this for you.

The form should be connected to the table/query so IT can do the data work, either via :
connected to table for direct entry
or
unbound form that runs an append query.

This will prevent or enable to stop dupliacate entry.

[orange]

@rafegh,

As ranman said you shouldn't be updating your table via code. If you use a bound form, Access will update your table and add the new record when you move off that record.

However, I do have some questions/comments.
Your table seems to be a mixture of ideas ProjectCode, Employer, Layer, MineName .... which indicates a non Normalized structure (at first glance).
Can you tell us WHAT your database is about in plain English? If you have a data model or a jpg of your relationships window, could you attach a copy to your next post.
Good luck with your project.

[June7]

I also do not understand use of code in this situation to create records.

Personally, I would avoid compound primary key. Set an autonumber field as primary key and save that as foreign key in related tables. Can set the 3 fields as compound index to prevent duplicates.

[Rafegh]

thank you for your answers. The database is for entering the results of tests during a highway construction project. I have used the append query somewhere else in my database but in this case, I want to create a number of records with different value for two fields of them. Because of that I thought that using a loop in vba will be better. This is not a critical problem for me, as I will be the user of the database and I know that I should not create duplicate records. Now my problem is that after creating this records, I want to update them through another form. How can I filter the records that I want (whose project code, section number and layer type has a specific value) to update?

[orange]

Can you post a jpg (screen capture) of your Relationships window?

You can open the form to do the edits and specify the record(s) to work on via the OPENARGS or you can filter the recordsource of the form.

[Rafegh]

This is the relationships of my database:

[June7]

Options for filtering form:

1. dynamic parameterized query as form RecordSource

2. VBA code that constructs filter criteria and applies to form Filter property or pass filter to form when it opens, review http://www.allenbrowne.com/ser-62code.html
DoCmd.OpenForm "formname", , , strCriteria

[orange]

Rafegh,

What is the purpose of the tables with suffixes in the names?
LayerProperties, LayerProperties_1, LayerProperties_2 this is usually a sign of a non normalized structure.

The key to database is to get the database designed to meet the business needs.

Where are you in the database design and development process?

[June7]

Looks like tables are included in relationships builder multiple times. This is not correct. If you want to use a compound primary/foreign key then there should be 3 link lines from 4-LayerData to one copy of 3-LayerProperties. And 2 link lines from 3-LayerProperties to 2-LayerProperties. Compound primary/foreign keys will drive you nuts.

[orange]

What are the PKs of each of your Tables? Specifically Sections.