Why does my DLookup only return the first record?

Question

I'm trying to use a DLookup in Access VBA, but even with a criteria (hard-coded or from a text box), the lookup only returns the value from the first record in the table, not a record that matches the criteria.

Here is my code;

JOBID = DLookup("[ID]", "[MASTER PLANNER]", "[JOB NUMBER] = '" & Me.JOB_NUMBER & "'")

ID is a numerical ID field, [JOB NUMBER] is a string (like this; S019191-1) and JOB_NUMBER is the field on the form.

Anyone have any ideas on this at all?

Thanks!

Are you saying your DLookup returns the JOBID from the first record in the [MASTER PLANNER] table and that record DOES NOT have a [JOB NUMBER] matching Me.JOB_NUMBER ? — Don Jewett

user1598814 user1598814 · Accepted Answer · 2015-08-03T16:11:15

Dlookup function return value. Not record. If you want to return all the record/s you can do it with : 1. Query - do display the results in form/report. 2. Recordset - to use it in vba code. Here is an explain how to use recordsets: How to use recordsets for beginners

Why does my DLookup only return the first record?

1 Answers