DLOOKUP question

[GD]

If I'm not mistaken, DLOOKUP retrieves information from the first row it
encounters in a table, based on the criteria. But what if there are possibly
more than one row with the same criteria, and the one I want it to refer to
is 2nd (3rd, etc.) listed in the table? Can I program a command button on a
form to somehow get DLOOKUP to refer to the next record down, based on the
criteria, then the next, and so on? Or is there something other than DLOOKUP
that can make this happen?

Thanks!!

 

[Douglas J. Steele]

DLookup doesn't have that capability.

You'd have to open a recordset and manually loop through it.

 

[John W. Vinson]

If I'm not mistaken, DLOOKUP retrieves information from the first row it
encounters in a table, based on the criteria. But what if there are possibly
more than one row with the same criteria, and the one I want it to refer to
is 2nd (3rd, etc.) listed in the table? Can I program a command button on a
form to somehow get DLOOKUP to refer to the next record down, based on the
criteria, then the next, and so on? Or is there something other than DLOOKUP
that can make this happen?

Thanks!!

There's no such concept as "the next record down" in a Table; tables have no
usable order, they should be viewed as "sacks full of data".

You'll need to either add an additional criterion to select the correct
record, based on the values of fields within the table; or open a recordset,
if you have some other field in the table which can provide an unambiguous
sort order.

 

[JString]

An easy option might be to ammend DLookup's criteria parameter like in this
untested sample code:

Private Sub Command0_Click()
Static criteria As String, last_record_ID As Long
If criteria = "" Then
criteria = "[some field name] = " & some_value
Else
criteria = criteria & " AND [record ID field] <> " & last_record_ID
End If

'remember the ID of the record that was just found
last_record_ID = Me![record ID field]

'lookup a value from the table and assign it to something
myvar = DLookup("some field name", "table name", criteria)
End Sub

This code won't work for you without a lot of tweaking, but it should show
you how the 'criteria' string variable is added onto with each successive
click of the command button.