Dlookup Question

  • Thread starter Thread starter buzzmcduffie
  • Start date Start date
B

buzzmcduffie

I have a form that needs to look up a "goal" by matching several fields in a
table. I can't figure out how to do dlookup with multiple criteria

Form = frmManualTaskDataEntry
[employee]
[cbxMailCodeTask]
[cbxState]
[cbxDisabilityIndicator]
[cbxVolumeCode]

Table = tblMailCodeTasks
MailCodeTask
State
DisabilityIndicator
VolumeCode
Goal
Active
Click to expand...

Here's what I have that doesn't work:

Private Sub cbxCompany_AfterUpdate()
Me![Goal] = DLookup("[Goal]", "tblMailCodeTasks", tblMailCodeTasks.MailCode
= [Forms]![frmManualTasksDataEntry]![cbxMailCodeTask]) And
((tblMailCodeTasks.DisabilityIndicator) =
[Forms]![frmManualTasksDataEntry]![cbxDisabilityIndicator]) And
((tblMailCodeTasks.State) = [Forms]![frmManualTasksDataEntry]![cbxState]) And
((tblMailCodeTasks.Active) = "yes")
End Sub

Any help is appreciated!
 
The following assumes that all your fields in the Lookup are Text values and
so are surrounded by single quotes.

Me![Goal] = DLookup("[Goal]", "tblMailCodeTasks", "MailCode = '" &
[Forms]![frmManualTasksDataEntry]![cbxMailCodeTask] & "' And
DisabilityIndicator = '" &
[Forms]![frmManualTasksDataEntry]![cbxDisabilityIndicator] & "' And State =
'" & [Forms]![frmManualTasksDataEntry]![cbxState] & "' And Active = 'yes')

buzzmcduffie said:
I have a form that needs to look up a "goal" by matching several fields in a
table. I can't figure out how to do dlookup with multiple criteria

Form = frmManualTaskDataEntry
[employee]
[cbxMailCodeTask]
[cbxState]
[cbxDisabilityIndicator]
[cbxVolumeCode]

Table = tblMailCodeTasks
MailCodeTask
State
DisabilityIndicator
VolumeCode
Goal
Active
Click to expand...

Here's what I have that doesn't work:

Private Sub cbxCompany_AfterUpdate()
Me![Goal] = DLookup("[Goal]", "tblMailCodeTasks", tblMailCodeTasks.MailCode
= [Forms]![frmManualTasksDataEntry]![cbxMailCodeTask]) And
((tblMailCodeTasks.DisabilityIndicator) =
[Forms]![frmManualTasksDataEntry]![cbxDisabilityIndicator]) And
((tblMailCodeTasks.State) = [Forms]![frmManualTasksDataEntry]![cbxState]) And
((tblMailCodeTasks.Active) = "yes")
End Sub

Any help is appreciated!
Click to expand...
 
I'm still getting a syntax error when I copy and paste your code. I must not
understand.

RonaldoOneNil said:
The following assumes that all your fields in the Lookup are Text values and
so are surrounded by single quotes.

Me![Goal] = DLookup("[Goal]", "tblMailCodeTasks", "MailCode = '" &
[Forms]![frmManualTasksDataEntry]![cbxMailCodeTask] & "' And
DisabilityIndicator = '" &
[Forms]![frmManualTasksDataEntry]![cbxDisabilityIndicator] & "' And State =
'" & [Forms]![frmManualTasksDataEntry]![cbxState] & "' And Active = 'yes')

buzzmcduffie said:
I have a form that needs to look up a "goal" by matching several fields in a
table. I can't figure out how to do dlookup with multiple criteria

Form = frmManualTaskDataEntry
[employee]
[cbxMailCodeTask]
[cbxState]
[cbxDisabilityIndicator]
[cbxVolumeCode]

Table = tblMailCodeTasks
MailCodeTask
State
DisabilityIndicator
VolumeCode
Goal
Active
Click to expand...

Here's what I have that doesn't work:

Private Sub cbxCompany_AfterUpdate()
Me![Goal] = DLookup("[Goal]", "tblMailCodeTasks", tblMailCodeTasks.MailCode
= [Forms]![frmManualTasksDataEntry]![cbxMailCodeTask]) And
((tblMailCodeTasks.DisabilityIndicator) =
[Forms]![frmManualTasksDataEntry]![cbxDisabilityIndicator]) And
((tblMailCodeTasks.State) = [Forms]![frmManualTasksDataEntry]![cbxState]) And
((tblMailCodeTasks.Active) = "yes")
End Sub

Any help is appreciated!
Click to expand...
Click to expand...
 
Sorry missed the closing double quotes off the end

...... And Active = 'yes'")

buzzmcduffie said:
I'm still getting a syntax error when I copy and paste your code. I must not
understand.

RonaldoOneNil said:
The following assumes that all your fields in the Lookup are Text values and
so are surrounded by single quotes.

Me![Goal] = DLookup("[Goal]", "tblMailCodeTasks", "MailCode = '" &
[Forms]![frmManualTasksDataEntry]![cbxMailCodeTask] & "' And
DisabilityIndicator = '" &
[Forms]![frmManualTasksDataEntry]![cbxDisabilityIndicator] & "' And State =
'" & [Forms]![frmManualTasksDataEntry]![cbxState] & "' And Active = 'yes')

buzzmcduffie said:
I have a form that needs to look up a "goal" by matching several fields in a
table. I can't figure out how to do dlookup with multiple criteria

Form = frmManualTaskDataEntry
[employee]
[cbxMailCodeTask]
[cbxState]
[cbxDisabilityIndicator]
[cbxVolumeCode]

Table = tblMailCodeTasks
MailCodeTask
State
DisabilityIndicator
VolumeCode
Goal
Active

Here's what I have that doesn't work:

Private Sub cbxCompany_AfterUpdate()
Me![Goal] = DLookup("[Goal]", "tblMailCodeTasks", tblMailCodeTasks.MailCode
= [Forms]![frmManualTasksDataEntry]![cbxMailCodeTask]) And
((tblMailCodeTasks.DisabilityIndicator) =
[Forms]![frmManualTasksDataEntry]![cbxDisabilityIndicator]) And
((tblMailCodeTasks.State) = [Forms]![frmManualTasksDataEntry]![cbxState]) And
((tblMailCodeTasks.Active) = "yes")
End Sub

Any help is appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Also it needs to be all on one line unless you use the underscore
continuation character.

buzzmcduffie said:
I'm still getting a syntax error when I copy and paste your code. I must not
understand.

RonaldoOneNil said:
The following assumes that all your fields in the Lookup are Text values and
so are surrounded by single quotes.

Me![Goal] = DLookup("[Goal]", "tblMailCodeTasks", "MailCode = '" &
[Forms]![frmManualTasksDataEntry]![cbxMailCodeTask] & "' And
DisabilityIndicator = '" &
[Forms]![frmManualTasksDataEntry]![cbxDisabilityIndicator] & "' And State =
'" & [Forms]![frmManualTasksDataEntry]![cbxState] & "' And Active = 'yes')

buzzmcduffie said:
I have a form that needs to look up a "goal" by matching several fields in a
table. I can't figure out how to do dlookup with multiple criteria

Form = frmManualTaskDataEntry
[employee]
[cbxMailCodeTask]
[cbxState]
[cbxDisabilityIndicator]
[cbxVolumeCode]

Table = tblMailCodeTasks
MailCodeTask
State
DisabilityIndicator
VolumeCode
Goal
Active

Here's what I have that doesn't work:

Private Sub cbxCompany_AfterUpdate()
Me![Goal] = DLookup("[Goal]", "tblMailCodeTasks", tblMailCodeTasks.MailCode
= [Forms]![frmManualTasksDataEntry]![cbxMailCodeTask]) And
((tblMailCodeTasks.DisabilityIndicator) =
[Forms]![frmManualTasksDataEntry]![cbxDisabilityIndicator]) And
((tblMailCodeTasks.State) = [Forms]![frmManualTasksDataEntry]![cbxState]) And
((tblMailCodeTasks.Active) = "yes")
End Sub

Any help is appreciated!
Click to expand...
Click to expand...
Click to expand...
 
worked like a charm! thank you!

RonaldoOneNil said:
Also it needs to be all on one line unless you use the underscore
continuation character.

buzzmcduffie said:
I'm still getting a syntax error when I copy and paste your code. I must not
understand.

RonaldoOneNil said:
The following assumes that all your fields in the Lookup are Text values and
so are surrounded by single quotes.

Me![Goal] = DLookup("[Goal]", "tblMailCodeTasks", "MailCode = '" &
[Forms]![frmManualTasksDataEntry]![cbxMailCodeTask] & "' And
DisabilityIndicator = '" &
[Forms]![frmManualTasksDataEntry]![cbxDisabilityIndicator] & "' And State =
'" & [Forms]![frmManualTasksDataEntry]![cbxState] & "' And Active = 'yes')

:

I have a form that needs to look up a "goal" by matching several fields in a
table. I can't figure out how to do dlookup with multiple criteria

Form = frmManualTaskDataEntry
[employee]
[cbxMailCodeTask]
[cbxState]
[cbxDisabilityIndicator]
[cbxVolumeCode]

Table = tblMailCodeTasks
MailCodeTask
State
DisabilityIndicator
VolumeCode
Goal
Active

Here's what I have that doesn't work:

Private Sub cbxCompany_AfterUpdate()
Me![Goal] = DLookup("[Goal]", "tblMailCodeTasks", tblMailCodeTasks.MailCode
= [Forms]![frmManualTasksDataEntry]![cbxMailCodeTask]) And
((tblMailCodeTasks.DisabilityIndicator) =
[Forms]![frmManualTasksDataEntry]![cbxDisabilityIndicator]) And
((tblMailCodeTasks.State) = [Forms]![frmManualTasksDataEntry]![cbxState]) And
((tblMailCodeTasks.Active) = "yes")
End Sub

Any help is appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top