dcount function - new to Access VBA

[jillp]

Hello, I am very new to Access and the googling I have done suggests that I use Dcount, but I can't get it to work.


I have a really simple table with just 2 field.

Type (unique - key - no duplicates - number)
Notes

Now as the user enters a new type I want to check the table to check they have not entered before.

But, I have the following and get 'type mismatch'
What should I be doing ?
Thanks

Dim TypeID As String
      
    TypeID = Me.Type.Value
    MsgBox TypeID
    
    
    If DCount("[Typeid]", "tablecontacttype", "Type" > 0) Then
        MsgBox TypeID
    End If

[pbaldy]

This should help:

DLookup Usage Samples

[jillp]

Thank you very much.
So simple when you know how.

[pbaldy]

No problem, and welcome to the site by the way!

[John_G]

Hi -

You want to check if the TypeID number exists in the table using DCount, so what you need is to check if the DCount function is > 0, so it needs to look like this:

If DCount("[Typeid]", "tablecontacttype", "TypeID = " & typeID) > 0

This assumes that TypeID is the name of the field in the tablecontacttype table.

Notice that I put the ">0" outside the DCount; this is because you are comparing the result of the DCount function to 0.

HTH

John

[Missinglinq]

Your DCount() was malformed, as has been said, and needs to be corrected, but the problem is more complex than just the part that John has pointed out, with the > 0 having to be outside of the DCount()!

You state that the Field name is Type, and that is what needs to be used in the DCount, not TypeID, which is a Variable! It should be

If DCount("[Type]", "tablecontacttype", "Type = " & Me.Type) > 0

Also, your malformed DCount actually wasn't what caused the 'type mismatch' error! That was caused by these two lines:

Dim TypeID As String

TypeID = Me.Type.Value

TypeID is declared to be a String Variable, which is to say it is Text, but you then assign the Value of Me.Type to it, and Type is defined as a Number; hence the 'type mismatch!'

BTW, Type is a Reserved Word, in Access, and should not be used as the name of a Control or Field! Doing so is likely to not only create problems, but create problems that will be very difficult to debug!

Linq ;0)>

[jillp]

Thanks for all the help above.
I have now on your advice renamed both the fields that I called 'type' and 'note' to more specific names.

This is now working after your help, how do I get the thread to show 'solved'

[Missinglinq]

Glad we could help!

At the top of the thread, in the green bar (on my PC, anyway) you'll see 'Thread Tools;' drop it down and selected 'mark as solved.'

Good luck with your project, and Welcome to AFN!

Linq ;0)>