Check Duplicate with VBA

Dear all,

Can I do check duplicate using VBA when user enter data in a certain field?
If the data that tuser entered was already in the system, give a message
'Duplicate data' and the user have to enter a new data.

Thank you in advance.

KT

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200711/1

0
KimTong
11/30/2007 8:49:33 PM
access.formscoding 7494 articles. 0 followers. Follow

15 Replies
8283 Views

Similar Articles

[PageSpeed] 11

Use the control's BeforeUpdate event to check your table's data (code sample 
below assumes the field is a numeric data type):

Private Sub NameOfTextbox_BeforeUpdate(Cancel As Integer)
If DCount("*", "NameOfYourTable", "NameOfFieldBeingChecked=" & _
    Me.NameOfTextbox.Value) > 0 Then
    Cancel = True
    MsgBox "Duplicate data!"
    Me.NameOfTextbox.Undo
End If
End Sub

-- 

        Ken Snell
<MS ACCESS MVP>



"KimTong via AccessMonster.com" <u35279@uwe> wrote in message 
news:7c00cb7a836f1@uwe...
> Dear all,
>
> Can I do check duplicate using VBA when user enter data in a certain 
> field?
> If the data that tuser entered was already in the system, give a message
> 'Duplicate data' and the user have to enter a new data.
>
> Thank you in advance.
>
> KT
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200711/1
> 


0
Ken
11/30/2007 9:12:47 PM
On 30 Nov, 22:12, "Ken Snell \(MVP\)"
<kthsneisll...@ncoomcastt.renaetl> wrote:
> Use the control's BeforeUpdate event to check your table's data (code sample
> below assumes the field is a numeric data type):
>
> Private Sub NameOfTextbox_BeforeUpdate(Cancel As Integer)
> If DCount("*", "NameOfYourTable", "NameOfFieldBeingChecked=" & _
>     Me.NameOfTextbox.Value) > 0 Then
>     Cancel = True
>     MsgBox "Duplicate data!"
>     Me.NameOfTextbox.Undo
> End If
> End Sub
>
> --
>
>         Ken Snell
> <MS ACCESS MVP>
>
> "KimTong via AccessMonster.com" <u35279@uwe> wrote in messagenews:7c00cb7a836f1@uwe...
>
>
>
>
>
> > Dear all,
>
> > Can I do check duplicate using VBA when user enter data in a certain
> > field?
> > If the data that tuser entered was already in the system, give a message
> > 'Duplicate data' and the user have to enter a new data.
>
> > Thank you in advance.
>
> > KT
>
> > --
> > Message posted via AccessMonster.com
> >http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200711/1

Hi Ken,
I was trying to use your code to check for duplicates entry. But it's
giving to me an error like this "missing query operator..."
What I'm doing wrong?

Many Thanks.
Stefano.
0
riccifs
12/1/2007 2:59:50 PM
The code I posted uses generic names. Post the code that you're trying to 
use so that we can see the actual names and how you've constructed the code.

-- 

        Ken Snell
<MS ACCESS MVP>


<riccifs@tin.it> wrote in message 
news:7f3c7414-f33f-4636-9131-3f80aa6bc395@v4g2000hsf.googlegroups.com...
> On 30 Nov, 22:12, "Ken Snell \(MVP\)"
> <kthsneisll...@ncoomcastt.renaetl> wrote:
>> Use the control's BeforeUpdate event to check your table's data (code 
>> sample
>> below assumes the field is a numeric data type):
>>
>> Private Sub NameOfTextbox_BeforeUpdate(Cancel As Integer)
>> If DCount("*", "NameOfYourTable", "NameOfFieldBeingChecked=" & _
>>     Me.NameOfTextbox.Value) > 0 Then
>>     Cancel = True
>>     MsgBox "Duplicate data!"
>>     Me.NameOfTextbox.Undo
>> End If
>> End Sub
>>
>> --
>>
>>         Ken Snell
>> <MS ACCESS MVP>
>>
>> "KimTong via AccessMonster.com" <u35279@uwe> wrote in 
>> messagenews:7c00cb7a836f1@uwe...
>>
>>
>>
>>
>>
>> > Dear all,
>>
>> > Can I do check duplicate using VBA when user enter data in a certain
>> > field?
>> > If the data that tuser entered was already in the system, give a 
>> > message
>> > 'Duplicate data' and the user have to enter a new data.
>>
>> > Thank you in advance.
>>
>> > KT
>>
>> > --
>> > Message posted via AccessMonster.com
>> >http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200711/1
>
> Hi Ken,
> I was trying to use your code to check for duplicates entry. But it's
> giving to me an error like this "missing query operator..."
> What I'm doing wrong?
>
> Many Thanks.
> Stefano. 


0
Ken
12/1/2007 4:48:24 PM
On 1 Dic, 17:48, "Ken Snell \(MVP\)"
<kthsneisll...@ncoomcastt.renaetl> wrote:
> The code I posted uses generic names. Post the code that you're trying to
> use so that we can see the actual names and how you've constructed the code.
>
> --
>
>         Ken Snell
> <MS ACCESS MVP>
>
> <ricc...@tin.it> wrote in message
>
> news:7f3c7414-f33f-4636-9131-3f80aa6bc395@v4g2000hsf.googlegroups.com...
>
> > On 30 Nov, 22:12, "Ken Snell \(MVP\)"
> > <kthsneisll...@ncoomcastt.renaetl> wrote:
> >> Use the control's BeforeUpdate event to check your table's data (code
> >> sample
> >> below assumes the field is a numeric data type):
>
> >> Private Sub NameOfTextbox_BeforeUpdate(Cancel As Integer)
> >> If DCount("*", "NameOfYourTable", "NameOfFieldBeingChecked=" & _
> >>     Me.NameOfTextbox.Value) > 0 Then
> >>     Cancel = True
> >>     MsgBox "Duplicate data!"
> >>     Me.NameOfTextbox.Undo
> >> End If
> >> End Sub
>
> >> --
>
> >>         Ken Snell
> >> <MS ACCESS MVP>
>
> >> "KimTong via AccessMonster.com" <u35279@uwe> wrote in
> >> messagenews:7c00cb7a836f1@uwe...
>
> >> > Dear all,
>
> >> > Can I do check duplicate using VBA when user enter data in a certain
> >> > field?
> >> > If the data that tuser entered was already in the system, give a
> >> > message
> >> > 'Duplicate data' and the user have to enter a new data.
>
> >> > Thank you in advance.
>
> >> > KT
>
> >> > --
> >> > Message posted via AccessMonster.com
> >> >http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200711/1
>
> > Hi Ken,
> > I was trying to use your code to check for duplicates entry. But it's
> > giving to me an error like this "missing query operator..."
> > What I'm doing wrong?
>
> > Many Thanks.
> > Stefano.

Hi Ken,
I copied and pasted your code as you posted and I changed it with my
name.

Private Sub Ragione_sociale_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblEsercenti", "Ragione_sociale=" & _
    Me.Ragione_sociale.Value) > 0 Then
    Cancel = True
    MsgBox "Duplicate data!"
    Me.Ragione_sociale.Undo
End If
End Sub

I hope you will understand it, they are in Italian.

Many Thanks for your help!,
Stefano.
0
riccifs
12/1/2007 5:00:12 PM
Ciao riccifs@tin.it wrote:
---cut---

prova cosi aggiugendo delle apici singole se Ragione_Sociale � tipo testo:

> Private Sub Ragione_sociale_BeforeUpdate(Cancel As Integer)


If Not IsNull(DLookup("[Ragione Sociale]", "tblEsercenti", _
   "[Ragione sociale]=" & Chr$(34) & Me.Ragione_sociale.Value & Chr$(34))) _
     And Me.Ragione_sociale.Value <> Nz(Ragione_sociale.Value.OldValue) Then
    Cancel = True
    MsgBox "Duplicate data!"
    Me.Ragione_sociale.Undo
End If

> End Sub

Oppure dai un occhiata qui � il sito da cui ho appreso il codice suddetto:

http://www.donkarl.com/it/FAQ/FAQ4Maschere.htm#4.25

>
> I hope you will understand it, they are in Italian.
>
> Many Thanks for your help!,
> Stefano.

sei Italiano, allora posta su:

Microsoft.public.it.office.access

oppure

it.comp.appl.access

sono due ottimi NewsGroup in italiano.

-- 
Ciao
Geppo 


0
geppo
12/1/2007 5:29:18 PM
On 1 Dic, 18:29, "geppo" <c...@tin.it.invalid> wrote:
> Ciao ricc...@tin.it wrote:
>
> ---cut---
>
> prova cosi aggiugendo delle apici singole se Ragione_Sociale =E8 tipo test=
o:
>
> > Private Sub Ragione_sociale_BeforeUpdate(Cancel As Integer)
>
> If Not IsNull(DLookup("[Ragione Sociale]", "tblEsercenti", _
>    "[Ragione sociale]=3D" & Chr$(34) & Me.Ragione_sociale.Value & Chr$(34)=
)) _
>      And Me.Ragione_sociale.Value <> Nz(Ragione_sociale.Value.OldValue) Th=
en
>     Cancel =3D True
>     MsgBox "Duplicate data!"
>     Me.Ragione_sociale.Undo
> End If
>
> > End Sub
>
> Oppure dai un occhiata qui =E8 il sito da cui ho appreso il codice suddett=
o:
>
> http://www.donkarl.com/it/FAQ/FAQ4Maschere.htm#4.25
>
>
>
> > I hope you will understand it, they are in Italian.
>
> > Many Thanks for your help!,
> > Stefano.
>
> sei Italiano, allora posta su:
>
> Microsoft.public.it.office.access
>
> oppure
>
> it.comp.appl.access
>
> sono due ottimi NewsGroup in italiano.
>
> --
> Ciao
> Geppo

Ciao Geppo,
grazie della tua risposta, ma mi da errore 424 oggetto richiesto...
che faccio!

Stefano.
0
riccifs
12/1/2007 6:15:56 PM
Ciao riccifs@tin.it wrote:
> On 1 Dic, 18:29, "geppo" <c...@tin.it.invalid> wrote:
>> Ciao ricc...@tin.it wrote:
>>
>> ---cut---
>>
>> prova cosi aggiugendo delle apici singole se Ragione_Sociale � tipo
>> testo:
>>
>>> Private Sub Ragione_sociale_BeforeUpdate(Cancel As Integer)
>>
>> If Not IsNull(DLookup("[Ragione Sociale]", "tblEsercenti", _
>>    "[Ragione sociale]=" & Chr$(34) & Me.Ragione_sociale.Value &
>>      Chr$(34))) _ And Me.Ragione_sociale.Value <>
>>     Nz(Ragione_sociale.Value.OldValue) Then Cancel = True
>>     MsgBox "Duplicate data!"
>>     Me.Ragione_sociale.Undo
>> End If
>>
>>> End Sub
>>
>> Oppure dai un occhiata qui � il sito da cui ho appreso il codice
>> suddetto:
>>
>> http://www.donkarl.com/it/FAQ/FAQ4Maschere.htm#4.25
>>
>>
>>
>>> I hope you will understand it, they are in Italian.
>>
>>> Many Thanks for your help!,
>>> Stefano.
>>
>> sei Italiano, allora posta su:
>>
>> Microsoft.public.it.office.access
>>
>> oppure
>>
>> it.comp.appl.access
>>
>> sono due ottimi NewsGroup in italiano.
>>
>> --
>> Ciao
>> Geppo
>
> Ciao Geppo,
> grazie della tua risposta, ma mi da errore 424 oggetto richiesto...
> che faccio!
>
> Stefano.

In che punto del codice ti da l'errore?


Comunque accertati che i nomi dei controlli siano scritti in modo corretto.


-- 
Ciao
geppo 


0
geppo
12/1/2007 6:41:35 PM
On 1 Dic, 19:41, "geppo" <c...@tin.it.invalid> wrote:
> Ciao ricc...@tin.it wrote:
> > On 1 Dic, 18:29, "geppo" <c...@tin.it.invalid> wrote:
> >> Ciao ricc...@tin.it wrote:
>
> >> ---cut---
>
> >> prova cosi aggiugendo delle apici singole se Ragione_Sociale =E8 tipo
> >> testo:
>
> >>> Private Sub Ragione_sociale_BeforeUpdate(Cancel As Integer)
>
> >> If Not IsNull(DLookup("[Ragione Sociale]", "tblEsercenti", _
> >>    "[Ragione sociale]=3D" & Chr$(34) & Me.Ragione_sociale.Value &
> >>      Chr$(34))) _ And Me.Ragione_sociale.Value <>
> >>     Nz(Ragione_sociale.Value.OldValue) Then Cancel =3D True
> >>     MsgBox "Duplicate data!"
> >>     Me.Ragione_sociale.Undo
> >> End If
>
> >>> End Sub
>
> >> Oppure dai un occhiata qui =E8 il sito da cui ho appreso il codice
> >> suddetto:
>
> >>http://www.donkarl.com/it/FAQ/FAQ4Maschere.htm#4.25
>
> >>> I hope you will understand it, they are in Italian.
>
> >>> Many Thanks for your help!,
> >>> Stefano.
>
> >> sei Italiano, allora posta su:
>
> >> Microsoft.public.it.office.access
>
> >> oppure
>
> >> it.comp.appl.access
>
> >> sono due ottimi NewsGroup in italiano.
>
> >> --
> >> Ciao
> >> Geppo
>
> > Ciao Geppo,
> > grazie della tua risposta, ma mi da errore 424 oggetto richiesto...
> > che faccio!
>
> > Stefano.
>
> In che punto del codice ti da l'errore?
>
> Comunque accertati che i nomi dei controlli siano scritti in modo corretto=
..
>
> --
> Ciao
> geppo

In questo punto:

If Not IsNull(DLookup("[Ragione_sociale]", "tblEsercenti", _
   "[Ragione_sociale]=3D" & Chr$(34) & Me.Ragione_sociale.Value & Chr
$(34))) _
     And Me.Ragione_sociale.Value <>
Nz(Ragione_sociale.Value.OldValue) Then

Per i nomi sui controlli credo non siano scritti bene... boo!?

Grazie comunque,
Stefano.
0
riccifs
12/1/2007 7:07:56 PM
On 1 Dic, 20:07, ricc...@tin.it wrote:
> On 1 Dic, 19:41, "geppo" <c...@tin.it.invalid> wrote:
>
>
>
> > Ciao ricc...@tin.it wrote:
> > > On 1 Dic, 18:29, "geppo" <c...@tin.it.invalid> wrote:
> > >> Ciao ricc...@tin.it wrote:
>
> > >> ---cut---
>
> > >> prova cosi aggiugendo delle apici singole se Ragione_Sociale =E8 tipo=

> > >> testo:
>
> > >>> Private Sub Ragione_sociale_BeforeUpdate(Cancel As Integer)
>
> > >> If Not IsNull(DLookup("[Ragione Sociale]", "tblEsercenti", _
> > >>    "[Ragione sociale]=3D" & Chr$(34) & Me.Ragione_sociale.Value &
> > >>      Chr$(34))) _ And Me.Ragione_sociale.Value <>
> > >>     Nz(Ragione_sociale.Value.OldValue) Then Cancel =3D True
> > >>     MsgBox "Duplicate data!"
> > >>     Me.Ragione_sociale.Undo
> > >> End If
>
> > >>> End Sub
>
> > >> Oppure dai un occhiata qui =E8 il sito da cui ho appreso il codice
> > >> suddetto:
>
> > >>http://www.donkarl.com/it/FAQ/FAQ4Maschere.htm#4.25
>
> > >>> I hope you will understand it, they are in Italian.
>
> > >>> Many Thanks for your help!,
> > >>> Stefano.
>
> > >> sei Italiano, allora posta su:
>
> > >> Microsoft.public.it.office.access
>
> > >> oppure
>
> > >> it.comp.appl.access
>
> > >> sono due ottimi NewsGroup in italiano.
>
> > >> --
> > >> Ciao
> > >> Geppo
>
> > > Ciao Geppo,
> > > grazie della tua risposta, ma mi da errore 424 oggetto richiesto...
> > > che faccio!
>
> > > Stefano.
>
> > In che punto del codice ti da l'errore?
>
> > Comunque accertati che i nomi dei controlli siano scritti in modo corret=
to.
>
> > --
> > Ciao
> > geppo
>
> In questo punto:
>
> If Not IsNull(DLookup("[Ragione_sociale]", "tblEsercenti", _
>    "[Ragione_sociale]=3D" & Chr$(34) & Me.Ragione_sociale.Value & Chr
> $(34))) _
>      And Me.Ragione_sociale.Value <>
> Nz(Ragione_sociale.Value.OldValue) Then
>
> Per i nomi sui controlli credo non siano scritti bene... boo!?
>
> Grazie comunque,
> Stefano.

No, no! ho risolto... scusa ora mi funziona ho tolto le parentisi
quadre =E8 ora va bene.
Ti ringrazio veramente molto, per il tuo aiuto.

Ciao Geppo!
Stefano.
0
riccifs
12/1/2007 7:28:23 PM
Ciao riccifs@tin.it wrote:
> On 1 Dic, 20:07, ricc...@tin.it wrote:
>> On 1 Dic, 19:41, "geppo" <c...@tin.it.invalid> wrote:
>>
>>
>>
>>> Ciao ricc...@tin.it wrote:
>>>> On 1 Dic, 18:29, "geppo" <c...@tin.it.invalid> wrote:
>>>>> Ciao ricc...@tin.it wrote:
>>
>>>>> ---cut---
>>
>>>>> prova cosi aggiugendo delle apici singole se Ragione_Sociale �
>>>>> tipo testo:
>>
>>>>>> Private Sub Ragione_sociale_BeforeUpdate(Cancel As Integer)
>>
>>>>> If Not IsNull(DLookup("[Ragione Sociale]", "tblEsercenti", _
>>>>>    "[Ragione sociale]=" & Chr$(34) & Me.Ragione_sociale.Value &
>>>>>      Chr$(34))) _ And Me.Ragione_sociale.Value <>
>>>>>     Nz(Ragione_sociale.Value.OldValue) Then Cancel = True
>>>>>     MsgBox "Duplicate data!"
>>>>>     Me.Ragione_sociale.Undo
>>>>> End If
>>
>>>>>> End Sub
>>
>>>>> Oppure dai un occhiata qui � il sito da cui ho appreso il codice
>>>>> suddetto:
>>
>>>>> http://www.donkarl.com/it/FAQ/FAQ4Maschere.htm#4.25
>>
>>>>>> I hope you will understand it, they are in Italian.
>>
>>>>>> Many Thanks for your help!,
>>>>>> Stefano.
>>
>>>>> sei Italiano, allora posta su:
>>
>>>>> Microsoft.public.it.office.access
>>
>>>>> oppure
>>
>>>>> it.comp.appl.access
>>
>>>>> sono due ottimi NewsGroup in italiano.
>>
>>>>> --
>>>>> Ciao
>>>>> Geppo
>>
>>>> Ciao Geppo,
>>>> grazie della tua risposta, ma mi da errore 424 oggetto richiesto...
>>>> che faccio!
>>
>>>> Stefano.
>>
>>> In che punto del codice ti da l'errore?
>>
>>> Comunque accertati che i nomi dei controlli siano scritti in modo
>>> corretto.
>>
>>> --
>>> Ciao
>>> geppo
>>
>> In questo punto:
>>
>> If Not IsNull(DLookup("[Ragione_sociale]", "tblEsercenti", _
>>    "[Ragione_sociale]=" & Chr$(34) & Me.Ragione_sociale.Value & Chr
>> $(34))) _
>>      And Me.Ragione_sociale.Value <>
>> Nz(Ragione_sociale.Value.OldValue) Then
>>
>> Per i nomi sui controlli credo non siano scritti bene... boo!?
>>
>> Grazie comunque,
>> Stefano.
>
> No, no! ho risolto... scusa ora mi funziona ho tolto le parentisi
> quadre � ora va bene.
> Ti ringrazio veramente molto, per il tuo aiuto.

Di nulla, figurati.

p.s.: se hai bisogno posta sui newsgroup che ti ho segnalato, ci sono 
ragazzi davvero forti.
>
> Ciao Geppo!
> Stefano.

Ciao Stefano
Geppo



0
geppo
12/1/2007 7:47:03 PM
I do not understand Italian, so I do not know if you found an answer in this 
thread from your conversation with geppo or not?

-- 

        Ken Snell
<MS ACCESS MVP>


<riccifs@tin.it> wrote in message 
news:180f0b28-fa86-4e50-a646-b7e5929a5407@b15g2000hsa.googlegroups.com...
> On 1 Dic, 17:48, "Ken Snell \(MVP\)"
> <kthsneisll...@ncoomcastt.renaetl> wrote:
>> The code I posted uses generic names. Post the code that you're trying to
>> use so that we can see the actual names and how you've constructed the 
>> code.
>>
>> --
>>
>>         Ken Snell
>> <MS ACCESS MVP>
>>
>> <ricc...@tin.it> wrote in message
>>
>> news:7f3c7414-f33f-4636-9131-3f80aa6bc395@v4g2000hsf.googlegroups.com...
>>
>> > On 30 Nov, 22:12, "Ken Snell \(MVP\)"
>> > <kthsneisll...@ncoomcastt.renaetl> wrote:
>> >> Use the control's BeforeUpdate event to check your table's data (code
>> >> sample
>> >> below assumes the field is a numeric data type):
>>
>> >> Private Sub NameOfTextbox_BeforeUpdate(Cancel As Integer)
>> >> If DCount("*", "NameOfYourTable", "NameOfFieldBeingChecked=" & _
>> >>     Me.NameOfTextbox.Value) > 0 Then
>> >>     Cancel = True
>> >>     MsgBox "Duplicate data!"
>> >>     Me.NameOfTextbox.Undo
>> >> End If
>> >> End Sub
>>
>> >> --
>>
>> >>         Ken Snell
>> >> <MS ACCESS MVP>
>>
>> >> "KimTong via AccessMonster.com" <u35279@uwe> wrote in
>> >> messagenews:7c00cb7a836f1@uwe...
>>
>> >> > Dear all,
>>
>> >> > Can I do check duplicate using VBA when user enter data in a certain
>> >> > field?
>> >> > If the data that tuser entered was already in the system, give a
>> >> > message
>> >> > 'Duplicate data' and the user have to enter a new data.
>>
>> >> > Thank you in advance.
>>
>> >> > KT
>>
>> >> > --
>> >> > Message posted via AccessMonster.com
>> >> >http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200711/1
>>
>> > Hi Ken,
>> > I was trying to use your code to check for duplicates entry. But it's
>> > giving to me an error like this "missing query operator..."
>> > What I'm doing wrong?
>>
>> > Many Thanks.
>> > Stefano.
>
> Hi Ken,
> I copied and pasted your code as you posted and I changed it with my
> name.
>
> Private Sub Ragione_sociale_BeforeUpdate(Cancel As Integer)
> If DCount("*", "tblEsercenti", "Ragione_sociale=" & _
>    Me.Ragione_sociale.Value) > 0 Then
>    Cancel = True
>    MsgBox "Duplicate data!"
>    Me.Ragione_sociale.Undo
> End If
> End Sub
>
> I hope you will understand it, they are in Italian.
>
> Many Thanks for your help!,
> Stefano. 


0
Ken
12/1/2007 11:23:32 PM
On 2 Dic, 00:23, "Ken Snell \(MVP\)"
<kthsneisll...@ncoomcastt.renaetl> wrote:
> I do not understand Italian, so I do not know if you found an answer in this
> thread from your conversation withgeppoor not?
>
> --
>
>         Ken Snell
> <MS ACCESS MVP>
>
> <ricc...@tin.it> wrote in message
>
> news:180f0b28-fa86-4e50-a646-b7e5929a5407@b15g2000hsa.googlegroups.com...
>
> > On 1 Dic, 17:48, "Ken Snell \(MVP\)"
> > <kthsneisll...@ncoomcastt.renaetl> wrote:
> >> The code I posted uses generic names. Post the code that you're trying to
> >> use so that we can see the actual names and how you've constructed the
> >> code.
>
> >> --
>
> >>         Ken Snell
> >> <MS ACCESS MVP>
>
> >> <ricc...@tin.it> wrote in message
>
> >>news:7f3c7414-f33f-4636-9131-3f80aa6bc395@v4g2000hsf.googlegroups.com...
>
> >> > On 30 Nov, 22:12, "Ken Snell \(MVP\)"
> >> > <kthsneisll...@ncoomcastt.renaetl> wrote:
> >> >> Use the control's BeforeUpdate event to check your table's data (code
> >> >> sample
> >> >> below assumes the field is a numeric data type):
>
> >> >> Private Sub NameOfTextbox_BeforeUpdate(Cancel As Integer)
> >> >> If DCount("*", "NameOfYourTable", "NameOfFieldBeingChecked=" & _
> >> >>     Me.NameOfTextbox.Value) > 0 Then
> >> >>     Cancel = True
> >> >>     MsgBox "Duplicate data!"
> >> >>     Me.NameOfTextbox.Undo
> >> >> End If
> >> >> End Sub
>
> >> >> --
>
> >> >>         Ken Snell
> >> >> <MS ACCESS MVP>
>
> >> >> "KimTong via AccessMonster.com" <u35279@uwe> wrote in
> >> >> messagenews:7c00cb7a836f1@uwe...
>
> >> >> > Dear all,
>
> >> >> > Can I do check duplicate using VBA when user enter data in a certain
> >> >> > field?
> >> >> > If the data that tuser entered was already in the system, give a
> >> >> > message
> >> >> > 'Duplicate data' and the user have to enter a new data.
>
> >> >> > Thank you in advance.
>
> >> >> > KT
>
> >> >> > --
> >> >> > Message posted via AccessMonster.com
> >> >> >http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200711/1
>
> >> > Hi Ken,
> >> > I was trying to use your code to check for duplicates entry. But it's
> >> > giving to me an error like this "missing query operator..."
> >> > What I'm doing wrong?
>
> >> > Many Thanks.
> >> > Stefano.
>
> > Hi Ken,
> > I copied and pasted your code as you posted and I changed it with my
> > name.
>
> > Private Sub Ragione_sociale_BeforeUpdate(Cancel As Integer)
> > If DCount("*", "tblEsercenti", "Ragione_sociale=" & _
> >    Me.Ragione_sociale.Value) > 0 Then
> >    Cancel = True
> >    MsgBox "Duplicate data!"
> >    Me.Ragione_sociale.Undo
> > End If
> > End Sub
>
> > I hope you will understand it, they are in Italian.
>
> > Many Thanks for your help!,
> > Stefano.

Hi Ken,
with the help of Geppo I finally got a solution for my problem.
Many thanks the same for your interest about it, I really appreciate
that!

Bye,
Stefano.
0
riccifs
12/2/2007 9:58:15 AM
Hi Ken,

I just to run your script, but the message said "Data Type mismatch" on this
part:

If DCount("*", "Roger", "AWB=" & _
   Me.AWB.Value) > 0 Then

I am sure I have the same data type between those 2 fields. Do you know why
it happened like that?

KF

Ken Snell (MVP) wrote:
>Use the control's BeforeUpdate event to check your table's data (code sample 
>below assumes the field is a numeric data type):
>
>Private Sub NameOfTextbox_BeforeUpdate(Cancel As Integer)
>If DCount("*", "NameOfYourTable", "NameOfFieldBeingChecked=" & _
>    Me.NameOfTextbox.Value) > 0 Then
>    Cancel = True
>    MsgBox "Duplicate data!"
>    Me.NameOfTextbox.Undo
>End If
>End Sub
>
>> Dear all,
>>
>[quoted text clipped - 6 lines]
>>
>> KT

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200712/1

0
KimTong
12/3/2007 3:18:46 PM
Ciao KimTong via AccessMonster.com wrote:
> Hi Ken,
>
> I just to run your script, but the message said "Data Type mismatch"
> on this part:
>
> If DCount("*", "Roger", "AWB=" & _
>   Me.AWB.Value) > 0 Then
>
> I am sure I have the same data type between those 2 fields. Do you
> know why it happened like that?
>
> KF
>

but AWB that type of data contains?

If it is then type text you have to write her/it this way:

If DCount("*", "Roger", "AWB='" & Me.AWB.Value &"'") > 0 Then

-- 
Ciao
Geppo 


0
geppo
12/3/2007 4:28:56 PM
It works. Thank you very much Geppo

geppo wrote:
>> Hi Ken,
>>
>[quoted text clipped - 8 lines]
>>
>> KF
>
>but AWB that type of data contains?
>
>If it is then type text you have to write her/it this way:
>
>If DCount("*", "Roger", "AWB='" & Me.AWB.Value &"'") > 0 Then
>

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200712/1

0
KimTong
12/3/2007 6:32:59 PM
Reply:

Similar Artilces:

Excel2007 VBA
If I create a PivotTable with a field called DATE containing dates formatting as dd/mm/yy, I then use this macro to remove Saturdays and Sundays from the PivotTable. Except it reads the dates a mm/dd/yy until they are 'out of scope' and then they are read as dd/mm/yy. Why? Sub HideWeekendsFromPivotTable() Dim pivotName As String Dim pivotDate As Date Dim z, pivotCount As Long pivotName = ActiveSheet.PivotTables(1).Name With ActiveSheet.PivotTables(pivotName).PivotFields("Date") pivotCount = .PivotItems.Count ...

merge MSN Bill Pay & Checking Acct
I want to merge my checking account with MSN Bill Pay but only see that I can merge MSN Bill Pay with my checking account (i.e., you lose all of your checking account information). Does any one have any suggestions? -- cheers, Tom G ...

VBA solution to paste text into megred cells
I do realize that merged cells are bad news. I'm stuck with an Excel 2003 worksheet, which contains this dreaded feature. Users are complaining that on trying to paste text into merged cells gives Excel Warnings: "Data on the Clipboard is not the same size and shape as the selected area. Do you want to paste anyway?" And: "Cannot change part of a merged cell." Interestingly, though if the user hits F2, which places the cursor in the formula bar, then the paste operation works! I'm usually reasonably of Ok with VBA; but this ...

Best way to get started with VBA?
Hi. A lot of replies by the MVPs include the statement that the OP will need to use VBA. What's the best way to get started? Are there any "VBA for Dummies" type books or development tools? Are there repositories of source code that the beginning VBA programmer can look at? Question: Does Outlook (2003) out of the box contain VBA code that is executed when a user clicks on a button or fills out a form? While Outlook is running, is there a way to watch this code execute and see how it changes values or fills in fields? This question came up in my mind because of the dedica...

Check names not working for multiple address books / contacts
Hello! I have upgraded from outlook 2003 to outlook 2007, my contacts are stored in pst. I used to be able to search for a name via check names and it displayed me all the results from global address list and from my contacts, but now it only does one. It does not matter which one I set in the checking order, whichever is top will be the one that provides the result. I would like it to show me both results, how would I go about to set that up? Thank you Deana More accurate and complete information would be helpful. The Global Address List is only available to those who c...

Authorizing checks with RMS
I recently removed PCCharge and setup my syetm to work with a preferred merchant provider to allow native processing of credit & debit cards. Since check verification/authorization was handled by PCCharge to Telecheck, check authorization is now not being done. How is check authorization accomplished using a preferred provider? Thanks, Richard Unfortunately, it's not. PC Charge is the only option for check processing in RMS as far as I know. TPI or some other vendor may support it through an add-in, but RMS as shipped has no way of doing both checks and debit... -- -- Gle...

How do I create Check list
1.I am new to excel and want to create a simple check list. I would like to have the first column check boxes and then the following columns discription and notes. Can someone give me a step by step on how to do this. I have created spread sheets but am not familiar with check boxes. 2.Also, Can someone tell me the difference between Access and Excel? Geek 1) Not sure what you're aiming to do with the checkboxes, but if you right click on the toolbar area, you will get a list of available toolbars. You want either the 'forms' toolbox or the 'controls' toolbox. Genera...

Preventing multiple check boxes from being checked
I have a worksheet with over 20 rows of check boxes with 5 check boxes in each row. (from the forms toolbox) I would like to prevent the user from checking more than one box in each row. Is this possible? BTW, this forum has been a great help. I'm so glad that expert Excel users are so willing to share their wealth of knowledge. Thanks again. Jae Put them in a group box, again from the forms toolbar. Drag the group box onto the sheet, then drag the checkboxes in. -- HTH RP (remove nothere from the email address if mailing direct) "Jae" <Jae@discussions.micro...

Check text field value against underlying table or query of combo box
Hello, I have a form on which the user can enter new records or update existing ones. If the user enters a new record on the form, I would like to have an 'After Update' event on one of the text fields, which is the item# to check if the value entered already exists in the underlying table, tbl_item. How can I call the table from the 'After Update' event of the text field and check for that value in the table? Thanks in advance. Regards, A. Crawford antcraw@swbell.net wrote: >I have a form on which the user can enter new records or update >existing ones. >If the us...

Duplicated emails
Some of the users in our our exchange server receiving duplicated emails. Can somebody tells me how to troubleshoot this issue?. We have ms outlook 2003 in the work stations. Is this an outlook problem or exchange problem? Need more information. Did someone internally send out to a list that contained external addresses or this is just internal? There are serveral reasons. Example 1 If a user sends mail to a recipient that is a member of two distribution groups (formerly known as distribution lists, or DLs), and the distribution groups are expanded on different servers, recipients can expe...

Printing Checks
I'm having a problem printing checks from Money 2003. I have three accounts that come up in one .mny file. Two of the accounts use 3-on-a-page checks and I want the name and address printed on them. One account uses voucher checks and needs to be nudged a little south. When I go to print checks I have to be aware of what account I'm in and change the settings by hand to make sure the checks print correctly. How can I make the check-print settings stick with the account I'm using. It's a pain to have to remember every time. I'm frequently forgetting and screwing up a pag...

Employee Check Format-Graphical
Does anyone know of a TK that shows how to convert one of the employee check from a text report to a graphical report? We do not have Direct deposit, I looked in the Report Downloads from Great Plains and did not see one there either. thanks -- Doug There's no secret here. Just un-check the "Text Report" option in the Report Definition window. And then modify the report so it looks like you want it. This will take some time. The reports don't transfer from text to graphical nicely. "Doug" wrote: > Does anyone know of a TK that shows how to convert...

Check Names = "Exchange Server computer is busy..."
Here's what I got: Active Directory on Server 2003 1 Domain Controller also running DNS and WINS No other DNS or WINS servers (I know, I know) Static IP Addresses...NO DHCP Exchange Server 2003 on a member server Client running W2K SP4 Outlook 2000 SP3 Exchange has 4 mailboxes set, but nobody is using it. I'm still testing things out. When I try to connect to Exchange via my Outlook 2000, I get the following message: "The function cannot be performed because the Microsoft Exchange Server computer is busy. Try again later" I've tried it from 3 different client comp...

Form controls, vba and arabic script
I'm struggling with this one. We have developed an application in Access 2003 using standard forms and controls (text boxes, etc). If I enter (by pasting because I can't type it in directly) so arabic text into a text box it displays OK. However, if I want to do anything with that text in vba I run into problems because the value of the text box only displays as "???????" ... e.g Dim strText as string strText = me.txtText Debug.Print strText Output is something like "???????" What am I doing wrong, or will this never work? Any help would be greatly appreciated...

Printing MICR checks
Hello, We are having an issue printing checks on the Select Payables Checks window. When we try to print the checks we receive the error message below: "Warning: You have MICR but are not in a MICR screen. To use MICR, abort this screen and set up security access." The user has access to this screen but I'm not sure what this error message means. There was nothing related to this in the Knowledge Base either. Has anyone else received this error message before and if so how did/do you resolve it? Thanks! The user has the third party mekorma check writing module ...

Check off boxes
Publisher 2003 in use. I am creating a questionnaire that will be included in a newsletter. I am using the publisher software to make this form. Somewhere, in the past, I saw a keyboard shortcut to inset checkoff boxes, however, I cannot remember the key strokes. Any help along this line will be greatly appreciated. Thank you and enjoy your day. Mike Mike Reshetar wrote: > Somewhere, in the past, I saw a keyboard shortcut to inset checkoff boxes, > however, I cannot remember the key strokes. Use Wingdings and the � or q characters. -- Ed Bennett - MVP Microsoft Publisher h...

check check check
afpoiadshfpaosid It is a feature, not a bug -- Message posted from http://www.ExcelForum.com ...

VBA code
Hi, I have to put VBA code in the worksheet_calculate event. How can I do this? Step by step please. Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. enter the macro and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn ...

spam properties check
When I receive a suspecious email (spam), I right click, without opening the message, to check the properties to make sure it is spam before I forward to the "missed spam" software of my ISP. Some spam emails then ask me if "I want to save the changes" before I close the properties box. I am then not able to forward this email without replying yes to "save the changes?" I did not make any changes, I just checked the properties. Why is this happening? ...

tristate check box #2
I have a tristate check box.The default behavour of tristate check box in windows is that, the grayed check box will become unchecked after click on grayed check box. But i want , the grayed check box will become checked after click on grayed check box in VC++ .Pls help me its urgent. Well, for Win32, you can always SendMessage the button a BM_GETSTATE. I'm sure with MFC there's a function that will permit this; it could be as simple as button_name.GetState(). Well, for the event OnClick for that button, you can get the state of the button and then either run it through some if-...

Check scanning and Check readers
Has any group successfully integrated a check reader or scanner into a MSFT RMS-POS solution? Our small business processes alot of checks from contracts and cash from customers. So, we need to integrate credit card, debit card, cash, and checks processing. I've been wanting to implement RMS but have been unsuccessful in locating info on any check readers and compatibility with RMS. Anyone with a solution to reading, scanning, and electronically verifying checks at the POS? Rms only supports check verification using PC Charge. Also, RMS only supports MICR (Magnetic Ink Character...

Check Void in Error
Our beloved cash admin voided SEVERAL checks in error. Is there a way to un-void them? They were historical checks, ie. not in the current period. Our bank isn't effected, but our ledger is. I'm pretty adept at Sql, what tables would I need to hit if I have to manually do this?? Thanks! ...

ntdll.dll failure after vba Int (random num)
I am getting a system freeze on a specific terminal - the freeze is the result of an ntdll.dll failure but I don't have a clue what's doing this and why this dll would fail. I have tried to come up with the simplest test I could so I have send key Setocus to a button (with no OnClick event) then send key {ENTER} after a random amount of time (well sort of random) between 25 min and (approx 41 mins. I don't think the send keys will cause anything to fail - ?? Me.testbutton.SetFocus Dim RandomNumber As Integer 'txtRandomNumber is an unbound box' me.txtRa...

How to check settings for Outlook 97
My Outlook is not working. The error message states, "Mapisp32 has caused an error in MINET32.DLL. Mapisp32 will now close." Secondly, whenever I attempt to check for "new mail" I get another error message, "messaging interface has returned to an error." Any help would be greatly appreciated. First thing to try would be to create a fresh profile here: Control Panel > Mail applet. Also, have you ever installed the Internet Mail Enhancement Patch for OL97? "Alex O. Stone" <aistone@comcast.net> wrote in message news:005501c368bf$eaa638...

Check box change colour once checked
Hi can anyone please help me? I've created a check box in my form which shows what information is valid and what's not, if it's checked then it's not valid and if it's not checked then it's valid....I would like the one's that are checked to show all the information in a different colour not the check box label but rest of the information For example: In a form which shows the category and skills and then the sub form shows list of volunteers (Name and contact details) who may still do voluntary work or not. there is a check box on the sub form next to each volun...