• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 57
  • Last Modified:

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?
0
SteveL13
Asked:
SteveL13
  • 6
  • 5
1 Solution
 
Dale FyeCommented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Dale FyeCommented:
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 FyeCommented:
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 FyeCommented:
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
 
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 FyeCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now