check data if exsists the add new record

[joemills62]

I have a button on contact details form to view director details



I want the button to check

1, check to see if the director is in the database
2, if not ask if you want to add to directors table using form directors
3, if yes add record using the form
4, if no, return

i am using the following code

Dim intChk As Integer

intChk = DCount("*", "directors", "[contact] = '" & Forms!contact_details![contact name] & "'") > 0

but im getting an error

runtime error '3464' data type mismatch in expression

can anyone help me this codeing please

[RuralGuy]

Try changing this one line:
Dim intChk As Integer
...to...
Dim intChk As Boolean

[ssanfu]

Dim intChk As Boolean

Not to be a nit picker, you might also think about:

Dim blnChk As Boolean

if this is a local variable.... (just to keep things consistent)

[RuralGuy]

Good point.

[joemills62]

Many thanks,
I have changed to var to blnchk to boolean and still get same error

the code is now
Private Sub Command80_Click()
Dim blnChk As Boolean
' contact is TEXT
intChk = DCount("*", "directors", "[contact] = '" & Forms!contact_details![contact name] & "'") > 0
If blnChk = True Then
DoCmd.OpenForm "directors", acNormal, , , acFormEdit, acWindowNormal 'Edit Mode
Else
If Me.Dirty = True Then Me.Dirty = False 'Save pending Edits
DoCmd.OpenForm "directors", acNormal, , , acFormAdd, acWindowNormal 'Add Mode
End If

End Sub

[joemills62]

sorry the code is now

Private Sub Command80_Click()
Dim blnChk As Boolean
'If contact is TEXT
blnChk = DCount("*", "directors", "[contact] = '" & Forms!contact_details![contact name] & "'") > 0
If blnChk = True Then
DoCmd.OpenForm "directors", acNormal, , , acFormEdit, acWindowNormal 'Edit Mode
Else
If Me.Dirty = True Then Me.Dirty = False 'Save pending Edits
DoCmd.OpenForm "directors", acNormal, , , acFormAdd, acWindowNormal 'Add Mode
End If

End Sub

[RuralGuy]

Alright then is the [contact] field a text field?

[joemills62]

Yes, the contact field is a Text Field

The idea is that it will check the directors table to see if this "director" exsists.

[RuralGuy]

You have both a Form and a Table named "directors"? Is this code on the contact_details form?

[joemills62]

yes, it is a button on the contact_details form, called directors details, when the button is pressed.
1. it should check to see if the conatct name on the conatct details form exsists in the directors table, and if not option to add new record, or edit if exsiss

[RuralGuy]

I repeat:

You have both a Form and a Table named "directors"?

...and you do not need to go through the Forms collection:
blnChk = DCount("*", "directors", "[contact] = '" & Me.[contact name] & "'") > 0
This *IS* the line of code that is throwing the error, right?

[joemills62]

Yes, this is the code that is causing the error.

I have copied what you have posted and seem to get the same error

[RuralGuy]

To what is the [contact name] control bound? You might try a:
MsgBox Me.[contact name] just before the DCount line to see what is in there.

[joemills62]

It is bound from the form contact_details [conatct name] field - > directors [contact]

in debug mode it have appears to have the correct information eg. the directors name

I put msgbox before the dcount,
and it come up as the directs name which is correct

[RuralGuy]

And you do have both a Form *and* a Table name "directors", right?