Dcount Type Mismatch error

Hi,

I am trying to build a macro which will run when a form opens. I am trying to create a macro which first ask for a number to be entered. Then take that number a count however records in a table with that member number are listed with a yes/no field called Disc check as yes.

However my code keeps getting a Type Mismatch error on the line highlighted below:

Private Sub Form_Load()
Dim Claims As String
Dim Num As String

Num = InputBox("Enter a Number", "Number")

If Num = "" Then
    MsgBox "There was no number entered please try again"
    DoCmd.OpenForm "frmMenu", acNormal
    Exit Sub
End If

Me.Text9.Value = Num

Claims = Application.DCount("Number] & [Disc]", "Files", "[Number] = " & Num And "[Disc] = -1")

If Claims = "0" Then
    Me.Text6.Value = "This number does not have a disc"
End If

End Sub

Can any help with this error? As I can not get it to work.

Thank you,

Jeskit

Hi Jeskit,

I think.. any D-Formula doesn't work with ARRAY.. it needs a range.. where we need to specify Conditions..

If it possible to store the DATA & Condition is a separate area in the gridsheet.. and in macro provide the complete range like "M1:O3".. hope it will work..

Please upload sample file with expected output..

Regards,
Deb

Originally Posted by jeskit

Hi,

I am trying to build a macro which will run when a form opens. I am trying to create a macro which first ask for a number to be entered. Then take that number a count however records in a table with that member number are listed with a yes/no field called Disc check as yes.

However my code keeps getting a Type Mismatch error on the line highlighted below:

Private Sub Form_Load()
Dim Claims As String
Dim Num As String

Num = InputBox("Enter a Number", "Number")

If Num = "" Then
    MsgBox "There was no number entered please try again"
    DoCmd.OpenForm "frmMenu", acNormal
    Exit Sub
End If

Me.Text9.Value = Num

Claims = Application.DCount("Number] & [Disc]", "Files", "[Number] = " & Num And "[Disc] = -1")

If Claims = "0" Then
    Me.Text6.Value = "This number does not have a disc"
End If

End Sub

Can any help with this error? As I can not get it to work.

Thank you,

Jeskit

Access Question...


I would guess the problem is with

"Number] & [Disc]"

You are missing the bracket before number but I also don't know that you need or want two fields there. Pick a field that you know will have a value in it. Any value should do.

Maybe

Claims = Application.DCount("[Number]", "Files", "[Number] = " & Num And "[Disc] = -1")

Also see:

http://office.microsoft.com/en-us/ac...001228817.aspx