retrieving more then one search result

[Sohotra]

Given a table name and the column name I can use the DLookUP function in VBA and get the appropriate value. However thats just one value,
What if my search criteria matches more then one value. Is there a way to basically do a search in VBA on a table which will put all the matching results in some type of an array?

 

[Moniker]

Using a DCount will count the number of matches.

To return the records, use the criteria part of the DLookup in a SELECT query. DLookup will only return the first match it finds, not a collection.

 

[jkl0]

Instead of DLookup, use a filter instead.

Hope this helps?

 

[Sohotra]

I'm not sure what you mean by filters?

 

[pbaldy]

The "how" really depends on the "what". What are you trying to accomplish? If you're trying to get the values into VBA, you could open a recordset using the same criteria to get the records, and use a loop to go through each.

 

[Sohotra]

That is precisely what I want to do.
I'm just not sure of how to do it in VBA?

 

[pbaldy]

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT ..."
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Do While Not rs.EOF
    'your code here
    rs.MoveNext
  Loop

  set rs = nothing
  set db = nothing

 

[Sohotra]

oh so thats how you do it.
Cool thankyou kindly