using Dlookup

[red6000]

Hi I have a tablewith 2 columns:

Table = Tasks
Column 1 = Taskname
Column2 = Category

I then have my main datatable with a form for entering the data.

Is it possible to put a dlookup formula directly in the main data table to
lookup the Category and populate the category based on the Taskname entered
in the form or do I have to have a 'non visible' textbox on the form to
capture the info and then pass it to the data table?

Thanks.

 

[Allen Browne]

You cannot do this in a table, but it is dead easy in a query and you can
use the query wherever you would have used your table.

1. Create a query that uses both tables.

2. Double-click the line joining the 2 tables.
Access pops up a dialog offering 3 options.
Choose the one that says:
All records from main datatable, and any matches from Tasks.

3. Drag all fields from the main datatable into the grid so it shows all
fields from that table.

4. Drag the Category field from the Tasks table into the grid as well.

That's it.

At step 2, you used an outer join. This guarantees you get all records, even
if the task name is left blank. If that's a new concept, see:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html