An easy way to find a number in a table.

[Ankarn]

I want a code that just test whether a given number allready excist in a field or not.

Lets say i'm adding one element, i want to know if it's there allready.
I can use DCount to count all the elements in the table with my number in the criteria and test if DCount is > 0, but for the future, there must be an easier way? some "IsInTable-Field" kind of-function.

 

[namliam]

If you have a Combobox or listbox you can use the "On not in list" event, otherwize your DCount or maybe a recordset select is the most logical way.

 

[CyberLynx]

Place the following Function procedure into a Database Code Module:

Public Function [B]IsInTable[/B](ByVal TableName As String, _
                          ByVal FieldName As String, _
                          ByVal SrchItem As Variant) _
                          As Boolean
  Dim SrchCriteria As String
  Select Case VarType(SrchItem)
     Case 0, 1  'Null
        SrchCriteria = FieldName & " IS NULL"
     Case 7     'Date
        SrchCriteria = FieldName & "=#" & SrchItem & "#"
     Case 8     'String
        SrchCriteria = FieldName & "='" & SrchItem & "'"
     Case Else
        SrchCriteria = FieldName & "=" & SrchItem
  End Select
  
  If DCount(FieldName, TableName, SrchCriteria) > 0 Then IsInTable = True
End Function

To Use, you might try:

If IsInTable("[TableName]", "[TableFieldName]", Me.FormFieldName) = True Then
   MsgBox "Hey...that data is already in Table!"
End If

 

[Ankarn]

i'm not so familiar with making functions to use.

What is the deal with the "case"?

 

[namliam]

The case is there to check for the value entered, much like an if...

If SrchItem is 0 then do this
If SrchItem is 9 then do that
Etc