Prevent duplicate entries in a subform

[IexGIfate]

Dear all,

I have a mainform (F_main) and a subform associated (F_place).

F_place is the form of the table T_place.
T_place is linked to F_main by the field "BizNumber". I also have the field "Place" and of course the "Place ID" (primary key) in T_place.

"Place" in linked to a combobox (in F_place).

I want to avoid, at a given "BizNumber", the same "place" to be entered in the F_place.

Could you please help me?

 

[burrina]

Here is one way.

Control Source =IIf(Nz([Place] & " " & [Place])="","Untitled",[Place]) 'Hidden Text Box.
Name = Auto_Title0

Before Update Event for Field to check for duplicates.
If (DCount("*", "[T_Place]", "[Place ID]<>" & Nz([Place ID], 0) & " And [Place] = '" & Replace(Nz([Auto_Title0]), "'", "''") & "'") > 0) Then
Call MsgBox("Duplicate Place Was Found,Try Again.", vbExclamation, Application.Name)
Me.Undo
End If

HTH

 

[IexGIfate]

Hi burrina

Thank you very much, it works perfectly.
However, I'm wondering, why is the first part for? I only put the second part and everything works, so why change control source and name?

Thanks again for your help!

 

[Mihail]

Happy that you solved the issue, but I suspect that this is not the right way.
The duplicates should be stopped at table level, not at form level.
So, do not allow duplicates at table level and, doing this, the form will no longer accept duplicates.

 

[IexGIfate]

Good point Mihail,

The previous solution does not work.

Say, we are in BizNo called "A", are in a subform called "AA" and we would like that in this subform "AA" it should be impossible to write twice the same 'place'.

AA_LOAPDORT = OK
AA_LOADPORT 2 = OK
AA_LOADPORT = "NO! ALREADY EXISTING"

However in same subform but under an other BizNo this very name of place should be applicable but not twice as well.

BA_LOADPORT = OK
BA_LOADPORT 2 =OK
BA_DISPORT = OK
BA_LOADPORT = "NO! ALREADY EXISTING"

The problem currently is that the code is not linked to the current Biz No and we have subforms...

 

[Mihail]

Ok ?

 

[jdraw]

You really need to understand your Tables and how they relate. Forms are just a means to display data from tables(or queries).
A typical 1 to Many relationships between tables is handled by the Form/subForm set up in Access.
Examples of the 1 to many relationship:

A Department can have 1 or many Employees.

A Person can have 1 or Many Hobbies.