check for entry of existing records

[tekila]

I've a data entry form that only allow entry of new records into the combo boxes. How do I reject entry of existing records?

tekila

 

[etrain]

I am not quite clear on what you are asking. Can you please explain? You talk of Data Entry form, I assume you mean you set the form to "Data Entry=True", but then you talk of Combo Boxes. It is probably me so can you explain more.

Thanks Remember the Past, Plan for the Future, yet Live in the Now for tomorrow may never come.
-etrain

 

[tekila]

This form serves the purpose of entering new product which consists of 3 entities, namely the product family, product code and product description. There are 3 combo boxes for these entities because a new product may have a different product description from an existing product (meaning it has the same family and code as an existing product). There are also cases whereby the new product has different code and description, but belongs to an existing family in the database.

In short, the contents of 1 combo box is limited by the value selected in the previous combo box. The combo boxes are not limited to list so as to allow entry of new records and 'data entry' is set to true for the form.

My question is how do I reject the entry of an existing record (the 3 entities in the combo boxes telly with a record in the database)?

Thanks in advance.

 

[etrain]

OK let’s see if I have got you right. You have 3 combo boxes for a record. Each box limits the available options in the next box. Say you choose "A" in the first, "B" in the second, and "C" in the third. So it is "ABC". Now you enter another record, it can be "ACC", "BBC", etc but it cannot be "ABC". Am I on the right track? In other words you in a sense have 3 primary keys. You could set up the table so that these are your primary keys but I don't really like to use more than one primary key.

I have setup tables that have a uniqueID (an autonumber and primary key), code (or short name), description, and a group (similar to your family). The only thing unique key I set on the table is the AutoNumber field. But, I wanted the code to be unique but without making it a primary key field. The way I prevent this is from the before update event of the field. I created a function in a module called fnCheckDup. So I could make these global for about 15 different setup forms I had to change the name of the “code” field on the form to txtType. I am not the most knowledgeable VBA programmer but it works for me. Here is the function listed below. It may not work as written for you but might get you in the right direction. Let me know if this is what you were looking for.

''''Code Start''''
Public Function fnCheckDup(frmA As Form)
Dim Ctl As Control
Dim strSource As String
Dim strCriteria1 As String
Dim strCriteria2 As String
Dim strB As String
Dim strFullCriteria
Dim strmsg As String
Dim strTitle As String
Dim strResponse As String
Dim strStyle

strmsg = "Duplicate Type Name. Please enter another name."
strTitle = "Duplicate Entry"
strStyle = vbOKOnly + vbExclamation + vbDefaultButton1
strSource = frmA.RecordSource 'replace with frmA.RecordSouce
strCriteria2 = "=Form.txtType" 'replace with frmA.txtType

For Each Ctl In frmA.Controls 'replace with frmA.Controls
If InStr(1, Ctl.Tag, "CheckDup&quot > 0 Then
strCriteria1 = Ctl.ControlSource
'Exit For
strFullCriteria = strCriteria1 & strCriteria2
If (IsNull(strB = DLookup(strCriteria1, strSource, strFullCriteria)) = False) Then
strResponse = MsgBox(strmsg, strStyle, strTitle)
Docmd.CancelEvent
Docmd.RunCommand acCmdUndo
End If
End If
Next

End Function
''''Code End''''
Remember the Past, Plan for the Future, yet Live in the Now for tomorrow may never come.
-etrain

 

[tekila]

I appreciated your help but your code seems to check for entry of an existing field in a record but I want to perform a check on entry of existing record (all three fields telly).Let me quote an example:

Existing records in table
----------------------------
Family Code Description
------ ---- -----------
A A
A
A A
B
B A A
B B A
C C C



I want to enter a new product, say AAC. On my form, A,B,C will appear in the list of Family combo, when I select A, only A will appear in Code combo list. Next, I've to select A and A,B appear in Description combo list. I shouldn't select A or B, but just key in C. However, if I've selected A or B, an error msgbox should prompt me for entering existing record.

I was thinking if I use DLookup function for all 3 combos in the afterupdate event of Description combo, it wouldn't work cos C exists in the Description field of the table. So in a sense, it doesn't check for existing record.