Prevent duplicate data entry as soon as entered

[Zorkmid]

Hi there,

I have a form for data entry, there is a field called "ENCON" that is basically the number of an incident, and is also my primary key. Is there a way to prevent duplicate entries? If I fill out the whole form with a duplicate ENCON # and try to add it, I get a run time error that explains that doing this would result in a duplicate primary key entry. This error would be a bit scary to my users.

Is there a way that I can prevent a duplicate ENCON number as soon as that field is filled? (It's the first field on the form). This way the user would know right away that the incident has already been entered without going thru the tedium of filling out the entire form.

I have tried this:

Private Sub cboENCON_BeforeUpdate(Cancel As Integer)
    If DCount("[ENCON]", "Risk_Data") > 0 Then
        Me.Undo
        MsgBox "ENCON Already exists in database"
    End If
End Sub

But it doesn't seem to do anything.

Am I on the right track? Or way off?

-Thanks in advance, Z

 

[KenHigg]

Hum... Several things going on. To keep it simple, to start with I'd use docmd.CancelEvent instead of me.undo.

 

[gemma-the-husky]

you can do it directly - i tend to store the count in a variable
the dcount needs ot be ofrmatted slightly differently
the encon is text you need to surround the red field with quote or dble quote chars

Private Sub cboENCON_BeforeUpdate(Cancel As Integer)
dim items as long
    items = DCount("*", "Risk_Data","[encon] = " & [COLOR="Red"]whateverfieldyouhave[/COLOR])
    if items>0 then
        Me.Undo
        MsgBox "ENCON Already exists in database"
    End If
End Sub

 

[Zorkmid]

you can do it directly - i tend to store the count in a variable
the dcount needs ot be ofrmatted slightly differently
the encon is text you need to surround the red field with quote or dble quote chars

Private Sub cboENCON_BeforeUpdate(Cancel As Integer)
dim items as long
    items = DCount("*", "Risk_Data","[encon] = " & [COLOR=red]whateverfieldyouhave[/COLOR])
    if items>0 then
        Me.Undo
        MsgBox "ENCON Already exists in database"
    End If
End Sub

Hmmmm, I still dont get any message when I try and go to another field. The name of the field in my table is [ENCON], I tried using that where you added the red text and I tried "" as well. Not really sure tht I fully understand what is going on.

-Z


Is it perhaps that beforeupdate is the wrong event to use here ?

 

[KenHigg]

Can you post your new code the way you have it?

 

[Zorkmid]

Sure

Private Sub cboENCON_BeforeUpdate(Cancel As Integer)
Dim items As Long
    items = DCount("*", "Risk_Data", "[Encon] = " & [COLOR=red]""[/COLOR])
    If items > 0 Then
        Me.Undo
        MsgBox "ENCON Already exists in database"
    End If
End Sub

I have also tried [ENCON] in place of ""


-Z

 

[KenHigg]

items = DCount("*", "Risk_Data", "[Encon] ='" & me.encon & "'")

???

 

[Zorkmid]

Nope, still isnt working

I hope I was clear enough in my goal, I want to prevent the user from entering an ENCON number that is already in the database (they are unique numbers from sheets). And I want a message to pop up as soon as there is a duplicate number in the combobox, before the rest of the field is filled out.

The code as it is doesnt seem to check (sorry, not well versed in the VBA lingo yet) when I tab or click on another control on the form. Doesnt run the code when I click my button to add the completed incident either, as I receive only a run time error when I try and add a record with a duplicate ENCON #.

 

[KenHigg]

is encon a numeric data type?

 

[Zorkmid]

Yes, number format.

 

[Zorkmid]

My form is unbound. Will that have an effect on what Im trying to do?

 

[KenHigg]

I was just going to point out that if it is a numeric data type then you leave the quotes out and would have somthing like this instead:

items = DCount("*", "Risk_Data", "[Encon] =" & me.encon)

Does this work?

(I don't think the unbound form thing matters)

 

[Zorkmid]

No that doesnt work either.

Kinda seems to me like the code isn't even checking for something.

Should this code go to work as soon as I dirty up cboENCON and click on another control?

-Z

 

[KenHigg]

Hum... Come to think of it unbound thing is the entire problem... Let me think about this for a second...

 

[KenHigg]

So when you fill in the field(s) how are you adding the data to a new record, by running and append query?

 

[Zorkmid]

So when you fill in the field(s) how are you adding the data to a new record, by running and append query?

I use a command button:

Private Sub rmAddIncident_Click()
   Dim err As Integer
   Dim cnn1 As ADODB.Connection
   Dim Risk_Data As ADODB.Recordset
   Dim strCnn As String
'Check that all fields are filled in
rmENCON.SetFocus
If rmENCON.Text = "" Then
err = err + 1
MsgBox "Please fill in the ENCON number." & err
End If
rmName.SetFocus
If rmName.Text = "" Then
err = err + 1
MsgBox "Please fill in the name of the person affected by the incident."
End If
rmDate.SetFocus
If rmDate.Text = "" Then
err = err + 1
MsgBox "Please fill in the date the incident occured."
End If
rmInjury.SetFocus
If rmInjury.Text = "" Then
err = err + 1
    MsgBox "Please specify the degree of injury."
End If
rmComments.SetFocus
If rmComments.Text = "" Then
err = err + 1
MsgBox "Please briefly describe the incident in the comments box."
End If
rmResolved.SetFocus
If rmResolved.Text = "" Then
err = err + 1
MsgBox "Please briefly summarize the follow-up."
End If
rmVictimstatus.SetFocus
If rmVictimstatus.Text = "" Then
err = err + 1
MsgBox "Please fill in the Victim status."
End If
rmLocation.SetFocus
If rmLocation.Text = "" Then
err = err + 1
MsgBox "Please fill in the location the incident took place."
End If
rmType.SetFocus
If rmType.Text = "" Then
err = err + 1
MsgBox "Please incicate the type of incident that occured."
End If
rmSpecificType.SetFocus
If rmSpecificType.Text = "" Then
err = err + 1
MsgBox "Please incicate the specific type of incident that occured."
End If
'if no errors insert data
If err < 1 Then
  ' Open a connection.
    Set cnn1 = New ADODB.Connection
   mydb = "G:\Admin\Ceo\Human Resources\RISKMGMT\Inhouse data\Risk Database\Inhouse Data.mdb"
   strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
    cnn1.Open strCnn
 
' Open Risk_Data table.
    Set Risk_Data = New ADODB.Recordset
    Risk_Data.CursorType = adOpenKeyset
    Risk_Data.LockType = adLockOptimistic
    Risk_Data.Open "Risk_Data", cnn1, , , adCmdTable

'get the new record data
      Risk_Data.AddNew
        Risk_Data!ENCON = rmENCON
        Risk_Data!Name = rmName
        Risk_Data!Date = rmDate
        Risk_Data!Injury = rmInjury
        Risk_Data![Incident Comments] = rmComments
        Risk_Data![Medication Risk] = Med_Risk
        Risk_Data![Medication/Equipment] = rmMedication
        Risk_Data![Victim Status] = rmVictimstatus
        Risk_Data!Location = rmLocation
        Risk_Data!Type = rmType
        Risk_Data![Specific Type] = rmSpecificType
        Risk_Data![Phys/Empl/Pt Involved] = rmPersonInvolved
        Risk_Data![Follow-up Summary] = rmResolved
        Risk_Data![Resolved or Pending] = ResolvedOrPending
        Risk_Data![Date Resolved] = rmDateResolved
      Risk_Data.Update
'Show the newly added data.
   MsgBox "Incident #: " & Risk_Data!ENCON & " has been successfully added"
'close connections to DB.
  Risk_Data.Close
    cnn1.Close
'clear all objects
Me.rmENCON.Value = ""
Me.rmDate.Value = ""
Me.rmName.Value = ""
Me.rmInjury.Value = ""
Me.rmComments.Value = ""
Me.Med_Risk.Visible = False
Me.rmMedication.Value = ""
Me.rmLocation.Value = ""
Me.rmVictimstatus.Value = ""
Me.rmType.Value = ""
Me.rmSpecificType.Value = ""
Me.rmPersonInvolved.Value = ""
Me.rmResolved.Value = ""
Me.ResolvedOrPending.Value = ""
Me.rmDateResolved.Value = ""
'send back to main page.
 DoCmd.OpenForm "Main_Page"

Else
MsgBox "An Error has occurred, please check and try again"
End If
 DoCmd.Close acForm, "Enter_New_Incident"
End Sub

 

[Zorkmid]

Ok, I am a total idiot.

I was mis-naming my combobox......fixed

I still get errors when I try and enter duplicate data however.

Private Sub [COLOR=red]rm[/COLOR]ENCON_BeforeUpdate(Cancel As Integer)
Dim items As Long
    items = DCount("*", "Risk_Data", "[Encon] =" & Me.ENCON)
    If items > 0 Then
        Me.Undo
        MsgBox "ENCON Already exists in database"
    End If
End Sub

The error I get is:
Run-time error '3075':

Syntax error (missing operator) in query expression '[Encon] = '.

I get this error whether the encon # is a duplicate or not.

 

[KenHigg]

Did you write all of that? Your duplicate record check should go in this pc of code.

 

[KenHigg]

I'm thinking that all of this before update stuff is useless as the event never fires on an unbound form...

 

[Zorkmid]

The event does fire now I think, the error messages I am getting come as soon as I leave the rmENCON combobox control

-Z