Primary Keys

[Guest]

I have a table with 10 fileds. Out of which, a combination of 4 fields make a
primary key.

In the dataentry form, when the user enters these 4 fields, i would like to
check if they make a unique record and not a duplicate.

What i want to do is when the user tabs out of the 4 th field control, i
would like to check for this condition and give out a message "duplicate
record". How do i write code for this???

I would also want to switch off all system messages and give oput my
messages to the user. I used "set warning" but doesnt trun off the system
messages.

Please help!!!!

Thanks

 

[Albert D. Kallal]

the main problem here is you don't know which of the four fields he's
entered.

So you'll have to make a common routine in which you call after any one of
the four fields is updated.

I would just create a public function in the form.

eg:

Public Function CheckForDuplicate as boolean

dim strWhere

if isnull(me.field1) then
exit function
end if
....... check for all 4 fields as above...

Then go:

strWhere = "field1 = " & me.field1 & _
" and field2 = " & me.field2 & _
" and field3 = " & me.field3 & _
" and field4 = " & me.field4

if dcount("*","nameoftable",strwhere) > 0 then
checkForDuplicate = true
msgbox "this will cause a duplicate"
end if

end function

Now, in the before udpate for the 4 fields, you can go:


cancel = CheckForDuplicate

The above is air code but it should give you the idea.

 

[Guest]

I am so sorry, it is kind of airy. Could some one please be more specific..

Thanks again!!

 

[pietlinden]

I am so sorry, it is kind of airy. Could some one please be more specific..

Thanks again!!

Airy? You mean *you* don't understand it... BIG difference.
Basically, you have to use a method to count the records in the table
with the same combination of 4 key values. If you get a match, then
the record exists. If not, you can add it the record. Otherwise,
you'll get a duplicate violation.

If ViolationExists() Then
'Cancel the insert
Cancel = True
'Show your message
MsgBox...
Else
'do nothing?
End If

Is that clearer?

 

[Albert D. Kallal]

I am so sorry, it is kind of airy. Could some one please be more specific..

You going to have to ask for more details as to what part you don't
understand here.

The code I had was:

Public Function CheckForDuplicate as boolean

dim strWhere

if isnull(me.field1) then
exit function
end if
....... check for all 4 fields as above...

Then go:

strWhere = "field1 = " & me.field1 & _
" and field2 = " & me.field2 & _
" and field3 = " & me.field3 & _
" and field4 = " & me.field4

if dcount("*","nameoftable",strwhere) > 0 then
checkForDuplicate = true
msgbox "this will cause a duplicate"
end if

end function

So, I was just trying to save some time here.

The idea here is that you want to check if any of the 4 fields is NOT yet
entered...(you can't check for duplicates until all four fields have been
entered
-- thus we don't run the code until all four fields have been entered ).

if isnull(me.field1) then
exit function
end if

if isnull(me.field2) then
exit function
end if

if isnull(me.field3) then
exit function
end if

if isnull(me.field4) then
exit function
end if

I thought it was quite obvious that you have to repeat the "over four times"
(once for each field).

And, course you'll replace "field1" ..."field4" with your actual 4 field
names used. (if I could actually read your mind I don't think I'd be sitting
here in this newsgroup!).

If any of your fields are text, and not number, then the following code will
have to be changed

strWhere = "field1 = " & me.field1 & _
" and field2 = " & me.field2 & _
" and field3 = " & me.field3 & _
" and field4 = " & me.field4

for "text" fields, you have to go:

strWhere = "field1 = " & me.field1 & _
" and field2 = '" & me.field2 & "'" & _
" and field3 = " & me.field3 & _
" and field4 = " & me.field4

In the above example the second field is considered a text field (and is
surrounded with quotes). The where clause is simply standard SQL code
without the where clause.