Prev: Error on front-end close
Next: Show Date Difference only in a certain range
From: scoebidoo on 6 May 2010 06:56
Hi

In a table with a double primary key, I want, before the input of a new
record, to check it doesn't exist.

To solve this problem, I presume, using a validation rule is the best option.
Assuming that Field1 and Field2 are both primary keys and the combination of
both keys must be unique, what's the best validation rule?

Can something like as follows work?
Dlookup("Field1","Table1","Field1=" & [Field1] & " AND Field2=" & [Field2])
Is Null
From: Wayne-I-M on 6 May 2010 07:45
If ((DLookup("[PrimaryFieldInTable]", "[TableName]", "[1stControlOnForm] ='"
& Form!1stFieldInTable & "' AND [2ndFieldInTable] = '" &
Form!2ndControlOnForm & "'"))) Then
'Something here like
MsgBox "This record may already exist", vbOKOnly, "SomeTitle"
End If

--
Wayne
Manchester, England.



"scoebidoo" wrote:

> Hi
>
> In a table with a double primary key, I want, before the input of a new
> record, to check it doesn't exist.
>
> To solve this problem, I presume, using a validation rule is the best option.
> Assuming that Field1 and Field2 are both primary keys and the combination of
> both keys must be unique, what's the best validation rule?
>
> Can something like as follows work?
> Dlookup("Field1","Table1","Field1=" & [Field1] & " AND Field2=" & [Field2])
> Is Null
From: Jon Lewis on 6 May 2010 08:44
Dlookup is slow.

If your table is an Access database table it will automatically reject a
record with the same combination of primary keys so why not just trap the
error that will be thrown?

Try this in your form's On Error event:

If DataErr = 3022 Then
MsgBox ("This is a Duplicate!")
Response = acDataErrContinue 'suppresses the built in error message
End If

Jon

"scoebidoo" <scoebidoo(a)discussions.microsoft.com> wrote in message
news:565FD306-709F-45F1-9B72-F8EE8A7D40B4(a)microsoft.com...
> Hi
>
> In a table with a double primary key, I want, before the input of a new
> record, to check it doesn't exist.
>
> To solve this problem, I presume, using a validation rule is the best
> option.
> Assuming that Field1 and Field2 are both primary keys and the combination
> of
> both keys must be unique, what's the best validation rule?
>
> Can something like as follows work?
> Dlookup("Field1","Table1","Field1=" & [Field1] & " AND Field2=" &
> [Field2])
> Is Null


From: scoebidoo on 6 May 2010 09:11
Thank you for your quick reply!

All right! It works! TX!!

Using this code in the "beforeupdate"-event of a combobox, how can I prevent
writing a null-value?
My code:
Private Sub combobox1_BeforeUpdate(Cancel As Integer)
....
If check =true Then
Cancel = True
MsgBox("a message")
Me.combobox1.Undo
End Sub

The cursor stays in the combobox and is ready to accept a new value.
But I don't want enter a new value in this case (when the check =true).
When trying to leave the combobox I always get messages about null-values
aren't allowed (which is correct).
How can I leave the combobox without any further messages?

"Wayne-I-M" wrote:

> If ((DLookup("[PrimaryFieldInTable]", "[TableName]", "[1stControlOnForm] ='"
> & Form!1stFieldInTable & "' AND [2ndFieldInTable] = '" &
> Form!2ndControlOnForm & "'"))) Then
> 'Something here like
> MsgBox "This record may already exist", vbOKOnly, "SomeTitle"
> End If
>
> --
> Wayne
> Manchester, England.
>
>
>
> "scoebidoo" wrote:
>
> > Hi
> >
> > In a table with a double primary key, I want, before the input of a new
> > record, to check it doesn't exist.
> >
> > To solve this problem, I presume, using a validation rule is the best option.
> > Assuming that Field1 and Field2 are both primary keys and the combination of
> > both keys must be unique, what's the best validation rule?
> >
> > Can something like as follows work?
> > Dlookup("Field1","Table1","Field1=" & [Field1] & " AND Field2=" & [Field2])
> > Is Null
From: scoebidoo on 6 May 2010 10:25
Hi Jon Lewis

Thanks for the reply!

I've tested your suggestion.
The result of trapping the error leaves me with a combobox with a wrong value.
When the error appears is there also a solution to leave the combobox
without any messages and no record added to the table?

Maybe it is important to know that the combobox is on a subform.

"Jon Lewis" wrote:

> Dlookup is slow.
>
> If your table is an Access database table it will automatically reject a
> record with the same combination of primary keys so why not just trap the
> error that will be thrown?
>
> Try this in your form's On Error event:
>
> If DataErr = 3022 Then
> MsgBox ("This is a Duplicate!")
> Response = acDataErrContinue 'suppresses the built in error message
> End If
>
> Jon
>
> "scoebidoo" <scoebidoo(a)discussions.microsoft.com> wrote in message
> news:565FD306-709F-45F1-9B72-F8EE8A7D40B4(a)microsoft.com...
> > Hi
> >
> > In a table with a double primary key, I want, before the input of a new
> > record, to check it doesn't exist.
> >
> > To solve this problem, I presume, using a validation rule is the best
> > option.
> > Assuming that Field1 and Field2 are both primary keys and the combination
> > of
> > both keys must be unique, what's the best validation rule?
> >
> > Can something like as follows work?
> > Dlookup("Field1","Table1","Field1=" & [Field1] & " AND Field2=" &
> > [Field2])
> > Is Null
>
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Error on front-end close
Next: Show Date Difference only in a certain range