How to Compare New Value Against Existing Table

[caper64]

I'm using Access 2000 and want to validate the entry of a new value against those values already in an existing table. One approach I was considering was to query the table (using DoCmd.RunSQL) and load the results into an array, then compare the new value by looping through the array looking for a match. The new value is entered from a sub-form that pops up after a button click. Am I on the right track using this approach? And how do I load the results of an SQL query into an array?

Thanks for any help!

 

[djeeten]

This is a piece of the code I used to see if a product within an order has already been choosen(as a product should only be choosen once within the same order). I think you're looking for similar code.


Dim found As Integer

Set con = Application.CurrentProject.Connection

found = 0

stSql = "SELECT tblOrderlines.ProductId, tblOrderlines.OrderId FROM tblOrderlines "
stSql = stSql & "WHERE tblOrderlines.OrderId =" & Me!OrderId

Set rs = CreateObject("ADODB.Recordset&quot
rs.Open stSql, con, 1 ' 1 = adOpenKeyset


'Search if the product has not already been choosen
'Me!cboId is the new value

While (Not (rs.EOF)) And found = 0
If Me!cboID = rs![ProductId] Then
Me.Undo
found = 1
End If
rs.MoveNext
Wend

If found = 1 Then
MsgBox "This product has already been choosen!"
Else
.....
End If