Prev: Error on front-end close
Next: Show Date Difference only in a certain range
From: a a r o n . k e m p f on 6 May 2010 13:09
SQL Server allows you to have triggers.. that means that you can run
this, even when people have their VBA and macros disabled

-Aaron



On May 6, 3:56 am, scoebidoo <scoebi...(a)discussions.microsoft.com>
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: Bob Quintal on 6 May 2010 17:31
=?Utf-8?B?c2NvZWJpZG9v?= <scoebidoo(a)discussions.microsoft.com> wrote
in news:E30BD97F-D3A2-4093-8097-F151EC385ABA(a)microsoft.com:

> 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?

undo the whole record, not just the combobox.
If check =true Then
Cancel = True
MsgBox("a message")
Me.combobox1.Undo
me.undo
me.undo ' a second time just to be safe.
end if
End Sub



>
> "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 7 May 2010 02:52
Bob,

Thank you! My problem is solved!

Thanks to everyone who responded!

scoebidoo

"Bob Quintal" wrote:

> =?Utf-8?B?c2NvZWJpZG9v?= <scoebidoo(a)discussions.microsoft.com> wrote
> in news:E30BD97F-D3A2-4093-8097-F151EC385ABA(a)microsoft.com:
>
> > 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?
>
> undo the whole record, not just the combobox.
> If check =true Then
> Cancel = True
> MsgBox("a message")
> Me.combobox1.Undo
> me.undo
> me.undo ' a second time just to be safe.
> end if
> End Sub
>
>
>
> >
> > "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
>
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Error on front-end close
Next: Show Date Difference only in a certain range