Prevent duplicate entries in a table.

I have a form with a sub-form.  When a text field in the sub-form is double-clicked a new record is created in a table and the new record has 4 text fields.  But if the same combination of the 4 text fields already exists in the table, I want the user to get a message letting them know this record already exists and not let the new record be created.

The 4 fields are txtCategory, txtSubCategory, txtJobNo, and txtDescription.

What would the VBA code look like?
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
Use the before update event of the subform to check to see whether there are other records in the table with the same 4 values and the same FK to the main form, something like:
private sub form_BeforeUpdate(cancel as integer)

    Dim strCriteria as string

    strCriteria = "(Parent_ID = " & me.parent.txt_ID & ") AND " _
                       & "(ID <> " & me.txt_ID & ") AND " _
                       & ("Category = '" & me.txtCategory & "') AND " _
                       & ("SubCategory = '" & me.txtSubCategory & "') AND " _
                       & ("JobNo = '" & me.txtJobNo & "') AND " _
                       & ("Description = '" & me.txtDescription & "')"
    if DCOUNT("*", "SubformTableName", strCriteria) > 0 then 
        msgbox "This combination of values already exists"
        Cancel = true
    end if

End Sub

Open in new window

This assumes that the table being used for the subform contains its own Autonumber (ID) column, and a column (Parent_ID) which relates the main form to the subform.
0
PatHartmanCommented:
Create a compound unique index on the four fields.  To do this:
1. Open the indexes dialog
2. On the first available open line, add name for the index. and select the first field.  Mark the index as unique.
3. On the next three lines, leave the name field blank and select each of the next three fields.
4. Save and close

In the double-click event, you can use a dCount() to determine if a record already exists.

If DCount("*", "yourtable", "Fld1 = " & Me.fld1 & " AND fld2 = " & Me.fld2 & " AND fld3 = " & Me.fld3 & " AND fld4 = " & Me.fld4)

Keep in mind that if your fields are strings, the text values will need to be enclosed in single or double quotes and if the fields are dates, they need to be enclosed in #'s.
0
SteveL13Author Commented:
Starting with Dale's suggestion:

The table for the subform DOES have its own autonumber field.  But there is no field on the main form that relates to the subform.  The records are being created by double-clicking a field in a sub-form next to the sub-form that has records being added to it.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Dale FyeOwner, Developing Solutions LLCCommented:
Can you post a screenshot of the form(s) so I can get a better understanding of what is going on.

so are you saying that the table that is being displayed in this subform does not have a Foreign Key value that relates to a control or field on the main form?
0
SteveL13Author Commented:
Ok.  Here goes.  To explain:

1)  I had to hide a lot of the data for confidential purposes.
1)  The main form is at the top.
2)  There are 3 sub-forms under the main form.
4)  You will note that I have double-clicked one item in the 1st sub-form (circled in red),  and one item in the 2nd sub-form (circled in blue).
5)  The selections that I double-clicked appear in the green sub-form.
6)  If I were to select an item from the red OR the blue sub-form AGAIN, I do NOT want to allow it to appear in the green sub-form.

NOTE:  Each of the 3 sub-forms is a datasheet form with 5 fields.  The 5 fields in each are Category, SubCategory, JobNo, and Description, and CategoryID which is an autonumber field.

I hope this helps.

Form-with-3-sub-forms.bmp
0
Dale FyeOwner, Developing Solutions LLCCommented:
It helps, some.
1.  So, JobNo is the FK column that relates each record in this table back to the Job # at the top of the page.
2.  What is the name of the table (or query) used to populate the subform in green?
3.  What are the other field names associated with that table (query).
0
SteveL13Author Commented:
1)  Yes.
2)  tblCategoryInfo
3) Category, SubCategory, Description, and CategoryID

Category, SubCategory, and Description are text fields.  CategoryID (autonumber) is number field.
0
Dale FyeOwner, Developing Solutions LLCCommented:
private sub form_BeforeUpdate(cancel as integer)

    Dim strCriteria as string

    strCriteria = "(JobNo = " & me.parent.txt_JobNo & ") AND " _
                       & "(CategoryID <> " & me.txt_CategoryID & ") AND " _
                       & "(Category = '" & me.txt_Category & "') AND " _
                       & "(SubCategory = '" & me.txt_SubCategory & "') AND " _
                       & "(Description = '" & me.txtDescription & "')"
    if DCOUNT("*", "tblCategoryInfo", strCriteria) > 0 then 
        msgbox "This combination of values already exists"
        Cancel = true
    end if

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
Not sure what is going on.  I put a breakpoint at the strCriteria line but the beforeupdate event is not even firing.
0
Dale FyeOwner, Developing Solutions LLCCommented:
Open that subform in design view and make sure has "Event Procedure" in the BeforeUpdate event on the properties dialog box.
0
SteveL13Author Commented:
Yes. It does. Very strange.
0
SteveL13Author Commented:
I discovered my issue. When I resolved it Dales suggestion worked.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.