DCount problems

[JiTS]

Hi,

At this moment I've got a DCount problem.

I used DCount for several forms and it works fine, but not with a new form.

Problemsituation:
I have a form that uses a query as a recordsource. You are able to fill in a unique number in a text box (txtAfleveringsbonnummer).

I want to check for duplicate numbers so I put the following code behind the textbox:

Private Sub txtAfleveringsbonnummer_BeforeUpdate(Cancel As Integer)
If DCount("[txtAfleveringsbonnummer]", "tblAanvoergegevens", "[Afleveringsbonnummer]='" & Me.txtAfleveringsbonnummer & "'") Then
MsgBox "Number already exists...", vbExclamation
Cancel = True
Me.Undo
Exit Sub
Else
End If

End Sub

I get every time the same error #3464:
"Data type mismatch in criteria expression."

I guess that the way I use DCount for this form is not correct.

Can somebody help me please?!?

Regards,

JiTS

 

[jfgambit]

You are using a DCount function when you should be using a DLookUp function.

BTW...DLookUps are not the most efficient way to check for duplicates and my slow down your database as it grows in size.

If this is a primary key field then you should allow access to work for you and prompt you for duplicates.

Just a suggestion.

 

[JiTS]

Thanks for your advice.

I am still waiting for an answer on my question, but in the meantime I will read more about DLookUp.

BTW Sunnyvale is a really nice city... I lived there in 2000 at Lakeside Drive... very cool!


Regards,
JiTS

 

[Mile-O]

Give an example of the type of "number" you type into the textbox and check.

Also:

If DCount("[txtAfleveringsbonnummer]", "tblAanvoergegevens", "[Afleveringsbonnummer]='" & Me.txtAfleveringsbonnummer & "'") Then

IF DCOUNT = WHAT?

And is your field in your table called txtAfleveringsbonnummer

 

[JiTS]

The textbox in the form = txtAfleveringsbonnummer
The table = tblAanvoergegevens
The fieldname in the table = Afleveringsbonnummer

The datatype of the fieldname is numerical.

Example:
I type into the textbox '4545'.
I click on the next textbox and DCount will check if '4545' already exists in the table tblAanvoergegevens in the column Afleveringsbonnummer. Yes?!? Then you will get a message otherwise you go further with the next textbox or whatever.

I copied the code from this forum and it works perfectly in my other forms, but not in this form. In the other forms the datatype of the fieldname was text and not numerical. I think that is a possible reason that the DCount function does not work properly in this form.

What do you think?!?

 

[JiTS]

[update]

I changed the datatype from numerical to text and it works perfectly.

But I still want to know if its possible to keep the datatype numerical. I guess that the DCount line will be different.

 

[Jon K]

For a number, remove the single quotes:-

If DCount("[txtAfleveringsbonnummer]", "tblAanvoergegevens", "[Afleveringsbonnummer]=" & Me.txtAfleveringsbonnummer)

 

[JiTS]

*bows in honour*

Thanks for your help... it works... it really does!

*LOL*