Question Dlookup default value with multiple criteria (1 Viewer)

[Ash1]

First, I should say that I am new at creating access databases... my apologies in advance.

I have a form with multiple combo boxes. Once the combo boxes are populated, I need the text box to look up the value in the query based on two combo box selections.

I am attempting to do a DLOOKUP, but I am obviously not doing it correctly because the result is #NAME?.

Here is what I have done:

In Default Value section of the txtONE property sheet: =DLookUp([fieldTHREE],[qryMAIN],[cmbONE].[AfterUpdate] And [cmbTWO].[AfterUpdate])

Your help is much appreciated!

 

[bob fitz]

Can you tell us in plain english what your criteria is supposed to mean.

 

[Ash1]

Query: qryWorkOrders (Area - Work Order - Task - Location)
Form: frmMAIN
Form: subDailySchedule - Datasheet
Combo Box: cmbArea
Combo Box: cmbWork Order
Combo Box: cmbWork Task
Text Box: txtLocation

When Area is selected - Only the Work Orders in that area appear in the list
When the Work Order is selected - Only the Work Tasks for that Work Order appear
When the Work Task is selected - The text box should show me the location. The work order + task = unique, so there is only one result.

All of this information goes into a table.

 

[plog]

=DLookUp([fieldTHREE],[qryMAIN],[cmbONE].[AfterUpdate] And [cmbTWO].[AfterUpdate])

You've made a bunch of syntatic errors. Here's a good reference for how Dlookups should be constructed: http://www.techonthenet.com/access/functions/domain/dlookup.php

Here's where you went wrong: Dlookup takes 3 arguments:

Dlookup(FieldString, SourceString, CriteriaString)

1. Your FieldString, isn't a string, you've treated it like a field/variable. You need to put double quotes around it, making your first argument this:

FieldString="[fieldTHREE]"

2. Same issue with your SourceString, also remove the brackets on data sources:

SourceString="qryMAIN"

3a. Same double quote issue with your CriteriaString, but you have bigger issues.

3b. You have no criteria, you need some sort of evaluation to occur. Most likely you need to compare two inputs to something in the data source of the Dlookup.

3c. I don't know what fields on your form you are referencing, but you are doing it wrong. You typically reference form inputs with ME![InputName] (actually, forms aren't my strong suit so that may not be right either. Point is what you have is referencing them correctly.)

 

[Ash1]

Thank you plog! The only problem I have now is that it needs to wait until the criteria fields (combo boxes) update. Should I be using a VBA code rather then entering into the default field?

Also, the detail in your reply is so helpful!!