Help needed - find method or similar

[Ronny Sigo]

Hello all,
I am a newbie regarding ms-access. I made a form, containing a combobox wich
gets is display values from a query. What I want to achieve is that when the
user types in a value in the combobox, the program looks in the query (or
table) to see if the value already exists. If not, it must append this value
to the table. The appending is not the problem, but the finding is ....
Could somebody give me an example of how to do this ?
Thanks
Ronny Sigo

 

[Glen Appleton]

Here's a bit of example code that might help:
----------
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~
' Procedure : ExistInTable
' Date : 7/23/2003
' Author : Glen Appleton
' Purpose : Verify value exists in table
' Returns : Boolean
' Usage : MyBoolean = ExistInTable("MyTable", "MyField1", {AnyValue})
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~
Function ExistInTable(TableName As String, FieldName As String, _
FindValue As Variant) As Boolean

Dim rsTable As Recordset
Dim strSQL As String

' This example only works with tables in the current database
strSQL = "SELECT * FROM [" & TableName & "] " & _
"WHERE ([" & FieldName & "] = " & FindValue & ");"
If VarType(FindValue) = vbString Then
strSQL = Replace(strSQL, FindValue, "'" & FindValue & "'")
End If

Set rsTable = CurrentDb.OpenRecordset(strSQL)
ExistInTable = Eval(rsTable.RecordCount <> 0)
Set rsTable = Nothing

End Function
----------

An alternative to this method would be to set the field in the table to and
indexed field with no dupes and then just trap the error when you try to
insert a duplicate value.

Hope this helps,
- Glen