Prevent duplication across 3 fields in 2 tables (1 Viewer)

[NBRJ]

Is there a way to prevent duplication of data across 3 fields in 2 tables?

I have two tables:

tRequest
IDRequest (PK)
FIDAcademicYear (FK from tAcademicYear)
FIDRequestType (FK tRequestType)
FIDSubject (FK from tSubject)
RequestDate
FIDStaff (FK tStaff)

1:M to:

tRequestDetail
IDRequestDetail (PK)
FIDRequest (FK from tRequest)
FIDLocation (FK from tLocation)
FIDSoftwareItem (FK tVersion)
ConfigInfo

Is there anyway to prevent the following 3 fields from being the same?

• tRequest.FIDAcademicYear
• tRequestDetail.Location
• tRequestDetail.SoftwareItem

This database requests software for a location for a academic year. If it's been requested once, I don't need it requested again by someone else. I may even add ConfigInfo to this.

Sorry, not sure where this should go, not sure if it's an event on a form or what...

Thanks! I keep asking questions and you lot have been so helpful with your time and advice. Maybe one day I can give something back here.

 

[Ranman256]

Can you key the 3 fields?
The autonumber does NOT need to be the key.

 

[NBRJ]

No, I'd rather not.

 

[sneuberg]

You can use Dcount to test the existence of the duplicate in the form's before update like

If DCount("*", "tRequest", "FIDAcademicYear = '" & Me.txtFIDAcademicYear & "'") > 0 And _
   DCount("*", "tRequestDetail", "FIDLocation = '" & Me.txtFIDLocation = "' AND FIDSoftwareItem = '" & Me.txtFIDSoftwareItem & "'") > 0 Then
   MsgBox "Error:  The combination of " & Me.txtFIDAcademicYear & " and " & Me.txtFIDLocation & " and " & Me.txtFIDSoftwareItem & "already exist in the database"
End If

Here the names txtFIDAcademicYear, .txtFIDSoftwareItem, and txtFIDSoftwareItem would be the names of the text box or combo boxes. You would need to substitute the real names that you have. Also all three are assumed to be text. If they are numbers then remove the single quotes from the expressions.

 

[jdraw]

NBRJ,
It might be helpful if you told readers more about your application.
If the combination of those 3 fields must result in unique values, my suspicion is the tables and relationships may not be aligned with your requirements.

see this for composite unique index in Access.

 

[NBRJ]

sneuberg, thank you! I'll give that a go.

jdraw - I think my ERD is pretty sorted now thanks to MarkK's clarifying input (here).

 

[sneuberg]

My previous post also has the assumption in it that the user will never enter a single quote. That's probably not a good assumption as it could be a typo and if it happens it will cause a syntax error. You can guard against this with a simple function like:

Public Function ESQ(str As String) As String

ESQ = Replace(str, "'", "''")

End Function

and use it like

If DCount("*", "tRequest", "FIDAcademicYear = '" & ESQ(Me.txtFIDAcademicYear) & "'") > 0 And _
   DCount("*", "tRequestDetail", "FIDLocation = '" & Me.txtFIDLocation = "' AND FIDSoftwareItem = '" & ESQ(Me.txtFIDSoftwareItem) & "'") > 0 Then
   MsgBox "Error:  The combination of " & Me.txtFIDAcademicYear & " and " & Me.txtFIDLocation & " and " & ESQ(Me.txtFIDSoftwareItem) & "already exist in the database"
End If

 

[Galaxiom]

Database tables should always be configured to enforce business rules. If a record must not be duplicated across three fields then a Composite Unique Index should be applied to those fields. This is the only way to completely ensure that the rule cannot be broken.

The composite index could be used as a key but it does not have to be.