Enforcing a unique "business ID"

[Gary Schuldt]

I have a table tblT that's updated by a single form frmT.

tblT has an autonumber primary key TID

However, there is another uniqueness constraint dictated by the business,
and that is that no two records in tblT should have the same value for the
combination of 3 fields--txtA, txtB, and txtC (txtA & txtB & txtC).

txtA is the only field in this trio requiring a value; the other two can be
valued or not, independently.

I'd like to be able to at least warn the user when they're about to create a
violation of this business rule. Here's my idea:

1. Create a BeforeUpdate event for frmT

2. Run a Select query with criteria that txtA, txtB and txtC from the form
equal that in the database, and also include TID in the query. Use the
Top=1 option.

3. (Maybe I need to open a form based on the above query so I can tell the
user the TID of the duplicate record?)

4. If there are no records in the result set, everything is OK.

5. If there is a record in the result set (there shouldn't be more than
one, anyway), tell the user the one in the frmT duplicates another one, and
here's the TID of the other one, and they should fix the problem . . .
something like that.

Questions:

A. Is this approach a good one to achieve my end of preventing violations
of the uniqueness constraint? If not, what would be better?

B. Can anyone point me to some standard code?

Thanks.

Gary

 

[Allen Browne]

Gary, if you enter a record that has a value only for txtA, does it clash
with any other records that have this value in txtA, or only those records
that have the same value in txtA and also are null in txtB and txtC?
Likewise, if you enter a value that has txtA and txtB, does it clash with
record that have only a txtA value?

Whatever you decide for those kinds of decisions will be reflected in the
WHERE clause of the query statement that identifies any matches. This kind
of thing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varTID As Variant

If IsNull(Me.txtA) then
cancel = true
MsgBox "txtA required"
Else
strWhere = "(txtA = """ & Me.txtA & """) AND "
If IsNull(Me.txtB) Then
strWhere = strWhere & "(txtB Is Null) AND "
Else
strWhere = strWhere & "(txtB = """ & Me.txtB & """) AND "
End If

'same for txtC, without the trailing " AND ".

varTID = DLookup("TID", "tblT", strWhere)
If IsNull(varTID) Then
If MsgBox("Duplicate of TID " & varTID & vbCrLf & _
"Continue anyway", vbYesNo+vbDefaultButton2) <> vbYes Then
Cancel = True
End If
End If
End If

End If

 

[Gary Schuldt]

Thanks, Allen.

I think the correct answer to your question is that all three fields have to
match for there to be a clash (duplicate). So it looks like your code is
designed for that interpretation.

But shouldn't the line

If IsNull(varTID) Then

have a NOT inserted in the condition, since that would indicate DLookUp did
in fact find a record satisfying the conditions set up in the Where clause,
and therefore we do have a clash?

Thanks much for the help.

Gary

 

[Allen Browne]

Yes. You want to show the mesage if it is NOT null.

 

[Gary Schuldt]

Allen,

I took your code and tailored it to my object names.

I also added the following test right before the DLookUp:

If Not IsNull(Me.GSV_ID) Then
strWhere = strWhere & " AND ([GSV ID] <> Me.GSV_ID )"
End If

This was to prevent (so I thought) the case where the user is just updating
an existing record, and I didn't want to tell them the update was a
duplicate of itself.

The GSV_ID field is the Autonumber PK of the table I'm searching in the
DLookUP:

varGSVID = DLookup("GSV ID", "tGSV", strWhere)

But I get a run-time error in the DLookUp call statement that there's no
such automation object Me.GSV_ID.

The immediate window shows this:

?strWhere
(Genus = "Cymbopogon") AND (Species = "citratus") AND (Variety Is Null) AND
([GSV ID] <> Me.GSV_ID )

I want the Me.GSV_ID to be evaluated and the integer value inserted instead.
I'm missing the syntax for that and can't seem to get anything that works!

Gary

 

[Allen Browne]

Concatenate the value of GSV_ID into the string, i.e.:
strWhere = strWhere & " AND ([GSV ID] <> " & Me.GSV_ID & ")"

Note that if GSV_ID is a Text type field in your table (not a Number type
field), you need extra quotes:
strWhere = strWhere & " AND ([GSV ID] <> """ & Me.GSV_ID & """)"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I took your code and tailored it to my object names.

I also added the following test right before the DLookUp:

If Not IsNull(Me.GSV_ID) Then
strWhere = strWhere & " AND ([GSV ID] <> Me.GSV_ID )"
End If

This was to prevent (so I thought) the case where the user is just
updating
an existing record, and I didn't want to tell them the update was a
duplicate of itself.

The GSV_ID field is the Autonumber PK of the table I'm searching in the
DLookUP:

varGSVID = DLookup("GSV ID", "tGSV", strWhere)

But I get a run-time error in the DLookUp call statement that there's no
such automation object Me.GSV_ID.

The immediate window shows this:

?strWhere
(Genus = "Cymbopogon") AND (Species = "citratus") AND (Variety Is Null)
AND
([GSV ID] <> Me.GSV_ID )

I want the Me.GSV_ID to be evaluated and the integer value inserted
instead.
I'm missing the syntax for that and can't seem to get anything that works!

Gary

Yes. You want to show the mesage if it is NOT null.

 

[Gary Schuldt]

Thanks, Allen,

I had used the "text type" notation (copying the pattern you had sent
earlier) and got a "type mismatch", so I went all the way the other
direction and completely removed the quotes and ampersands!

Staring at examples with those triple-double quotes and ampersands, together
with the fact that there are alternative ways of writing the same thing
using SINGLE quotes, was just too much for my brain.

I'll give this one a "go".

Gary

Concatenate the value of GSV_ID into the string, i.e.:
strWhere = strWhere & " AND ([GSV ID] <> " & Me.GSV_ID & ")"

Note that if GSV_ID is a Text type field in your table (not a Number type
field), you need extra quotes:
strWhere = strWhere & " AND ([GSV ID] <> """ & Me.GSV_ID & """)"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I took your code and tailored it to my object names.

I also added the following test right before the DLookUp:

If Not IsNull(Me.GSV_ID) Then
strWhere = strWhere & " AND ([GSV ID] <> Me.GSV_ID )"
End If

This was to prevent (so I thought) the case where the user is just
updating
an existing record, and I didn't want to tell them the update was a
duplicate of itself.

The GSV_ID field is the Autonumber PK of the table I'm searching in the
DLookUP:

varGSVID = DLookup("GSV ID", "tGSV", strWhere)

But I get a run-time error in the DLookUp call statement that there's no
such automation object Me.GSV_ID.

The immediate window shows this:

?strWhere
(Genus = "Cymbopogon") AND (Species = "citratus") AND (Variety Is Null)
AND
([GSV ID] <> Me.GSV_ID )

I want the Me.GSV_ID to be evaluated and the integer value inserted
instead.
I'm missing the syntax for that and can't seem to get anything that works!

Gary

Yes. You want to show the mesage if it is NOT null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen.

I think the correct answer to your question is that all three fields have
to
match for there to be a clash (duplicate). So it looks like your

code
is

designed for that interpretation.

But shouldn't the line

If IsNull(varTID) Then

have a NOT inserted in the condition, since that would indicate DLookUp
did
in fact find a record satisfying the conditions set up in the Where
clause,
and therefore we do have a clash?

Thanks much for the help.

Gary

Gary, if you enter a record that has a value only for txtA, does it clash
with any other records that have this value in txtA, or only those
records
that have the same value in txtA and also are null in txtB and txtC?
Likewise, if you enter a value that has txtA and txtB, does it clash with
record that have only a txtA value?

Whatever you decide for those kinds of decisions will be reflected

in
the

WHERE clause of the query statement that identifies any matches. This
kind
of thing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varTID As Variant

If IsNull(Me.txtA) then
cancel = true
MsgBox "txtA required"
Else
strWhere = "(txtA = """ & Me.txtA & """) AND "
If IsNull(Me.txtB) Then
strWhere = strWhere & "(txtB Is Null) AND "
Else
strWhere = strWhere & "(txtB = """ & Me.txtB & """) AND "
End If

'same for txtC, without the trailing " AND ".

varTID = DLookup("TID", "tblT", strWhere)
If IsNull(varTID) Then
If MsgBox("Duplicate of TID " & varTID & vbCrLf & _
"Continue anyway", vbYesNo+vbDefaultButton2) <> vbYes Then
Cancel = True
End If
End If
End If

End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table tblT that's updated by a single form frmT.

tblT has an autonumber primary key TID

However, there is another uniqueness constraint dictated by the
business,
and that is that no two records in tblT should have the same value for
the
combination of 3 fields--txtA, txtB, and txtC (txtA & txtB & txtC).

txtA is the only field in this trio requiring a value; the other two
can
be
valued or not, independently.

I'd like to be able to at least warn the user when they're about to
create
a
violation of this business rule. Here's my idea:

1. Create a BeforeUpdate event for frmT

2. Run a Select query with criteria that txtA, txtB and txtC from the
form
equal that in the database, and also include TID in the query.

Use
the

Top=1 option.

3. (Maybe I need to open a form based on the above query so I can tell
the
user the TID of the duplicate record?)

4. If there are no records in the result set, everything is OK.

5. If there is a record in the result set (there shouldn't be more
than
one, anyway), tell the user the one in the frmT duplicates another one,
and
here's the TID of the other one, and they should fix the problem .

..
.

something like that.

Questions:

A. Is this approach a good one to achieve my end of preventing
violations
of the uniqueness constraint? If not, what would be better?

B. Can anyone point me to some standard code?

Thanks.

Gary

 

[Gary Schuldt]

Allen,

I adapted your code below, and I still have one glitch I can't see the
problem with.

Here is my adaptation of your code in the area where I'm having difficulty:

===========
Dim varGSVID As Variant
' etc several lines of code
varGSVID = DLookup("GSV_ID", "tGSV", strWhere)

If Not IsNull(varGSVID) Then
strMsg = "Duplicate of GSV ID " & varGSVID & vbCrLf & _
"Continue anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2) <> vbYes Then
Cancel = True
End If
End If
============

When the If condition is true, I do get a message box with the Yes No
buttons, but the text that appears reads only (without the quotes)
"Duplicate of GSV ID ?"

I inserted a breakpoint right before the If MsgBox . . . and looked at
strMsg:

?strMsg
Duplicate of GSV ID ?
Continue anyway?

The value of varGSVID at that point I believe should be 1087, according
to what's in the table.

So I have 2 questions:
1. Where did the "?" (as apparently the value of varGSVID) come from?
2. Why doesn't MsgBox display the second line of the message?

Thanks.

Gary

 

[Allen Browne]

What is the datat type of the GSV_ID field in the table?
Is it Number or Text?

If number, it should work. If text, is it possible that the field contains a
zero-length string, space, or perhaps even a null-character? The ZLS would
not show at all; the space would not be visible only if you really notice
it, and the null-character could cause the rest of the string not to
display, but the character count would show them if you ask for Len(strMsg).

 

[Gary Schuldt]

Allen,

there is a GSV ID field in the tGSV, and it's an autonumber field as well as
the PK of tGSV. It does have an embedded blank, but, when I supplied "GSV
ID" (with blank) to DLookUP I got some kind of error, so I put the
underscore in place of the blank like I've seen done. Could that be the
problem?

Gary

 

[Allen Browne]

If the field name has a space in it, try square brackets around the name:
varGSVID = DLookup("[GSV ID]", "tGSV", strWhere)

 

[Gary Schuldt]

Thanks, Allen,

I just tried the square brackets, and it solved that problem: The entire
message, including the new line, comes out as I intended. Now I can
continue testing the code.

I will never use blanks in a database field name again!

Gary

If the field name has a space in it, try square brackets around the name:
varGSVID = DLookup("[GSV ID]", "tGSV", strWhere)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

there is a GSV ID field in the tGSV, and it's an autonumber field as well
as
the PK of tGSV. It does have an embedded blank, but, when I supplied "GSV
ID" (with blank) to DLookUP I got some kind of error, so I put the
underscore in place of the blank like I've seen done. Could that be the
problem?

Gary


in
the Use
the ..
.