Prevent duplicate data entry as soon as entered

[KenHigg]

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

-Z

You must have code in one of the combo box events

 

[Zorkmid]

Yes, my code is in the before_update of the rmENCON box. Doesnt this mean that that event is firing for it to show me an error?

 

[missinglinq]

DCount() is real picky about spacing, which may be the problem here, if this

items = DCount("*", "Risk_Data", "[Encon] =" & Me.ENCON)

was copied and pasted from your code. When you write an expression in VBA involving an equal mark, such as

A + B =C

Access will automatically correct it to read

A + B = C

with the space between the equal mark and the C. But if you write

"A + B =" & C

Access will take everything within the quotes literally, leaving it reading

A + B =C

which is incorrect syntax.

Try changing

items = DCount("*", "Risk_Data", "[Encon] =" & Me.ENCON)

to

items = DCount("*", "Risk_Data", "[Encon] = " & Me.ENCON)

 

[KenHigg]

Yes, my code is in the before_update of the rmENCON box. Doesnt this mean that that event is firing for it to show me an error?

Oh, in the before update of the control. Hum, yes it should fire whe the form is unbound (I think).

Try Linq's suggestion and see what happens...

 

[Zorkmid]

Private Sub rmENCON_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

With this code I still get the same error.

Linq, thanks for that post, was very informative to me.

-Z

 

[KenHigg]

I still say you should put this check in the rmAddIncident_Click code. What do you think?

 

[Zorkmid]

That works in theory, but if I can make an error message pop-up before the user goes through all the trouble of filling out each field (there are alot) then it would save mucho timo.

-Z

 

[KenHigg]

Hum... Good point.

Then lets start from scratch. In the control before update event just do something like:

msgbox "Hello"

and see if that fires as expected.

 

[Zorkmid]

Hum... Good point.

Then lets start from scratch. In the control before update event just do something like:

msgbox "Hello"

and see if that fires as expected.

Works like a charm.

 

[KenHigg]

Ok. Forget that code for a second and put a cmd button (that we'll delete in a minute) on the form with this in the click event:

msgbox DCount("*", "Risk_Data", "[Encon] = " & Me.ENCON)

 

[Zorkmid]

Ok. Forget that code for a second and put a cmd button (that we'll delete in a minute) on the form with this in the click event:

msgbox DCount("*", "Risk_Data", "[Encon] = " & Me.ENCON)

Gives me

run-time error '3075':
Syntax error (missing operator) in query expression '[Encon] = '.

 

[KenHigg]

Gives me

run-time error '3075':
Syntax error (missing operator) in query expression '[Encon] = '.

So this means you're not referencing the combo control correctly. You may have to do something like:

msgbox DCount("*", "Risk_Data", "[Encon] = " & forms!MyFormName!ENCON)

Otherwise you can test with something like:

msgbox me.ENCON

And see what happens (We're still working with the cmd button here)

 

[Zorkmid]

So this means you're not referencing the combo control correctly. You may have to do something like:

msgbox DCount("*", "Risk_Data", "[Encon] = " & forms!Enter_New_Incident!ENCON)

Otherwise you can test with something like:

msgbox me.ENCON

And see what happens (We're still working with the cmd button here)

Trying:

msgbox DCount("*", "Risk_Data", "[Encon] = " & forms!Enter_new_Incident!ENCON)

Gives me:

run-time error '3075':
Syntax error (missing operator) in query expression '[Encon] = '.


When I try:

msgbox me.ENCON

I get...

Run-time error '94'
Invalid use of null

-Z

 

[KenHigg]

So both of these could be because you have no value entered / selected in the text box. Try entering something and see what happens?

 

[Zorkmid]

It is a combobox, and I have deliberately entered a duplicate value each time I have tested it.

-Z

 

[KenHigg]

And you are sure the combo box control name is ENCON?

 

[Zorkmid]

No, it is rmENCON

I see my mistake now. This code works perfectly:

Private Sub rmENCON_BeforeUpdate(Cancel As Integer)
Dim items As Long
    items = DCount("*", "Risk_Data", "[Encon] = " & Forms!Enter_New_Incident!rmENCON)
    If items > 0 Then
        Me.Undo
        MsgBox "ENCON Already exists in database"
    End If
End Sub

Thanks so much for bearing with me Ken!

-Z

 

[KenHigg]

Wew - I just glad you have it working!