A2K - help with array search

[Deano]

God knows I've had my run-ins with arrays but this problem is really killing
me!

I'm looping through a recordset. For each record I need to look at a field
value and then see if it exists in a 2D array. It could be any one of the
values in the array and they are not in a particular order.

I don't think there's a built-in way of doing this IIRC but I need a
solution.

Many thanks
Martin

[Rich P]

How is the 2D array being populated? What is the data source of the 2D
array? Instead of populating a 2D array you should populate another
temp table. Then you can use DLookUp to see if your field exists in the
one table in the other table like this:

tbl1 is your main table, tbl2 is what used to be the 2D array

Dim DB As DAO.Database, RS As DAO.Recordset
Dim str1 As Variant
Set DB = CurrentDB
Set RS = DB.OpenRecordset("tbl1")
Do While Not RS.EOF
str1 = _
& DLookUp("fldz", "tbl2", "fldz = '" & RS!fldx & "'")
If IsNull(str1) Then MsgBox RS!flx & " is not in tbl2"
RS.MoveNext
Loop

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Deano]

Rich P wrote:

How is the 2D array being populated? What is the data source of the
2D array? Instead of populating a 2D array you should populate
another temp table. Then you can use DLookUp to see if your field
exists in the one table in the other table like this:

tbl1 is your main table, tbl2 is what used to be the 2D array

Dim DB As DAO.Database, RS As DAO.Recordset
Dim str1 As Variant
Set DB = CurrentDB
Set RS = DB.OpenRecordset("tbl1")
Do While Not RS.EOF
str1 = _
& DLookUp("fldz", "tbl2", "fldz = '" & RS!fldx & "'")
If IsNull(str1) Then MsgBox RS!flx & " is not in tbl2"
RS.MoveNext
Loop

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Cheers Rich, I think you're right about that or I could tag the records in
some way. Trying to look up values in an array seems impossible.