Check if exsists

[joemills62]

Hi all

I have a problem were dcount is reporting a false int, were in fact the data is in the table you will see that i have inserted msgbox to let me know ehat is happending.

dcount should check the [directors] table to see if the contact exsists, if this is true then open a form in edit mode, if false then ask to add new record.

however the dcount is coming up false when actually the contact does exsist



Private Sub Command80_Click()
Dim intChk As Boolean
'If contact is TEXT
MsgBox Me.[contact name]
intChk = DCount("*", "directors", "[contact] = '" & Me.[contact name] & "'") > 0
If intChk = True Then
MsgBox intChk
DoCmd.OpenForm "directors", acNormal, , , acFormEdit, acWindowNormal 'Edit Mode
Else
If intChk = False Then
MsgBox intChk

DoCmd.OpenForm "directors", acNormal, , , acFormAdd, acWindowNormal 'Add Mode
End If
End If

End Sub

[ssanfu]

You have verified the contact name is in the table? (..just asking )

You will need to have a reference set for DOA, but try this:

Private Sub Command80_Click()
   Dim rs As DAO.Recordset
   Dim sSQL As String

   Dim blnChk As Boolean

   blnChk = False

   'If contact is TEXT
   '   blnChk = DCount("*", "directors", "[contact] = '" & Me.[contact name] & "'") > 0

   MsgBox Me.[contact name]

   sSQL = "SELECT contact FROM directors WHERE [contact] = '" & Me.[contact name] & "'"
   Set rs = CurrentDb.OpenRecordset(sSQL, , dbReadOnly)
   If Not rs.BOF And Not rs.EOF Then
      rs.MoveLast
      MsgBox rs.RecordCount
      blnChk = True
   End If
   rs.Close
   Set rs = Nothing

   If blnChk = True Then
      MsgBox blnChk
      DoCmd.OpenForm "directors", acNormal, , , acFormEdit, acWindowNormal   'Edit Mode
   Else
      'boolean can only be TRUE or FALSE. no need to check for FALSE since already checked for TRUE
      '      If blnChk = False Then
      MsgBox blnChk
      DoCmd.OpenForm "directors", acNormal, , , acFormAdd, acWindowNormal      'Add Mode
      '      End If
   End If

End Sub

[joemills62]

I am still getting a false return, interesting enough though, when i done a query in the directors database, and i put in = quotes the directors name which is in the table, when ui run it it also comes up false ???

Also the [contact] field is also a lookup field from the contact table, i wonder if this is the problem, and how could i correct it ?

[ssanfu]

Also the [contact] field is also a lookup field from the contact table, i wonder if this is the problem, and how could i correct it ?

Yes, that is a huge problem.
See "The Evils of Lookup Fields in Tables"

at http://access.mvps.org/access/lookupfields.htm


You are seeing a name, but the actual field type is probably a number (long integer).

[joemills62]

Yes this has been the problem all the time, thank you.
would it be best then to use a form to add directors, but have the form look up the company table to select the company ?

and if so, how would i go about it