Unique Record

[TinaH]

Please Help!!

I have a database that has several tables. The main table being COMPLAINT, where the CUSTOMER_ID is the Primary Key and is set to Auto Number.

The COMPLAINT table has a one to many relationship to the COMPL_VIOL table



COMPLAINT -----------< COMPL_VIOL

I do not have a primary key defined in the COMPL_VIOL table because for every CUSTOMER_ID there could be multiple Violations (Violations against the customer is what is stored in the COMPL_VIOL table).

However, in the COMPL_VIOL table I want to be able to state that for each CUSTOMER_ID only one PRIMARY_IND is allowed (PRIMARY_IND states what the main complaint from the customer is). Below is an example of the data I want to store in the COMPL_VIOL table.

How can I make the PRIMARY_IND allowable only one time for each CUSTOMER_ID (sample data of what I want is attached).

FYI - On the form I am hiding the CUSTOMER_ID (because the frmCOMPL_VIOL is a sub-form to COMPLAINT) and at this time the Business Owners only want the ACT to equal FLSA so this is set to a constant and the is visible but not enabled.

[ssanfu]

The One -----------------------The many
COMPLAINT ------------------> COMPL_VIOL
CUSTOMER_ID (PK)-------------> CUSTOMERID_FK (Long Integer) (NOT autonumber!!!)

The PK - autonunber is a Long Integer. The FK (foreign key) must also be a Long.
The COMPLAINT PK field number is stored in the FK in the many table.

[June7]

Would need code, probably in the BeforeUpdate event of control bound to PRIMARY_IND field. This code would check if Customer_ID already has a record with Y value in PRIMARY_IND field. Is PRIMARY_IND a text type field?


Something like:

If Not IsNull(DLookup("PRIMARY_IND", "COMPL_VIOL", "CUSTOMER_ID=" & Me.Customer_ID)) Then
Cancel = True
Me.PRIMARY_IND = Null
MsgBox "This customer already has a record identified as primary."
End If

[TinaH]

Yes, the CUSTOMER_ID FK is set to Long Integer and not number

[TinaH]

PRIMARY_IND is a True/False field

[June7]

Maybe:

If Not IsNull(DLookup("PRIMARY_IND", "COMPL_VIOL", "CUSTOMER_ID=" & Me.Customer_ID & " AND PRIMARY_IND=True")) Then
Cancel = True
Me.PRIMARY_IND = False
MsgBox "This customer already has a record identified as primary."
End If

[ssanfu]

Boy, I really misread this one....... I hate it when i do that..... Sorry, shouldn't try and do three things at once.

[TinaH]

This did not work. The provided information will only allow me to enter one Violation per customer whether or not it is marked as a PRIMARY_IND

[TinaH]

I am attaching the Table and Form documentation from Access to show how both TABLES are being used. (I know the names are different then orginally stated, that was because I was at home researching the issue after work and did not have access to the database, but I did change the names is the scripts provided to what they are in the acutal database).

[June7]

Where did you put the code?

If you want to provide db for analysis, follow instructions at bottom of my post.

[TinaH]

I attempted to place the code on both the form COMPLAINT_VIOL and then when that did not work (would not allow me to enter a second field) I added the code to the field PRIMARY_VIOLATION_IND in the COMPLAIN_VIOL form which is a subform under the page tab named "Status of Complaince". On both attempts I placed the code in the Control BeforeUpdate.

The database is attached and the only data in it is test data (a couple of rows)

[June7]

You have the VBA procedure but it is not associated with the control event. In the event BeforeUpdate property select [Event Procedure] then click the ellipsis (...) to open VBA editor at the procedure.

Also, the DLookup syntax is wrong. Doesn't have the table/query argument. Don't need table/form name prefix. Also, I goofed, don't need line to set field to False. I got this to work:

Private Sub PRIMARY_VIOLATION_IND_BeforeUpdate(Cancel As Integer)
If Me.PRIMARY_VIOLATION_IND = True Then
    If Not IsNull(DLookup("PRIMARY_VIOLATION_IND", "COMPLAINT_VIOL", "CUSTOMER_ID =" & Me.CUSTOMER_ID & " AND PRIMARY_VIOLATION_IND =True")) Then
        Cancel = True
        'Me!PRIMARY_VIOLATION_IND = False
        MsgBox "This customer already has a record identified as Primary Violation."
     End If
End If
End Sub