Check Duplicate with VBA

  • Follow


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
Reply KimTong 11/30/2007 8:49:33 PM

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
Reply 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
Reply 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
Reply 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
Reply 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
Reply 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
Reply 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
Reply 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
Reply 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
Reply 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
Reply 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
Reply 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
Reply 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
Reply 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
Reply 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
Reply KimTong 12/3/2007 6:32:59 PM

15 Replies
3293 Views

(page loaded in 0.009 seconds)


Reply: