Filling a field with multiple criteria

[buzzmcduffie]

I need a field on my form that will autofill after a combo box (cbxCompany)
updates. It must look at other fields on the record and return the value,
[Goal], from a table (tblMailCodeTasks).

Fields on Table (tblMailCodeTasks):
TaskID
TaskCategory
MailCode
DisabilityIndicator
State
Goal
Active
VolumeCode

Fields on form: (frmManualTasksDataEntry)
cbxMailCodeTask
cbxState
cbxDisabilityIndicator
cbxVolumeCode
Goal

My code isn't working. What am I doing wrong?

Private Sub cbxCompany_AfterUpdate()
Me![Goal] = DLookup("[Goal]", "tblMailCodeTasks",
"[MailCode]=Forms![frmManualTasksDataEntry]![cbxMailCodeTask]" &
"[State]=Forms![frmManualTasksDataEntry]![cbxState]" &
"[DisabilityIndicator]=Forms![frmManualTasksDataEntry]![cbxDisabilityIndicator]" & "[VolumeCode] = Forms![frmManualTasksDataEntry]![cbxVolumeCode]")
End Sub

many thanks for any help!

 

[PieterLinden via AccessMonster.com]

I need a field on my form that will autofill after a combo box (cbxCompany)
updates. It must look at other fields on the record and return the value,
[Goal], from a table (tblMailCodeTasks).

Fields on Table (tblMailCodeTasks):
TaskID
TaskCategory
MailCode
DisabilityIndicator
State
Goal
Active
VolumeCode

Fields on form: (frmManualTasksDataEntry)
cbxMailCodeTask
cbxState
cbxDisabilityIndicator
cbxVolumeCode
Goal

My code isn't working. What am I doing wrong?

Private Sub cbxCompany_AfterUpdate()
Me![Goal] = DLookup("[Goal]", "tblMailCodeTasks",
"[MailCode]=Forms![frmManualTasksDataEntry]![cbxMailCodeTask]" &
"[State]=Forms![frmManualTasksDataEntry]![cbxState]" &
"[DisabilityIndicator]=Forms![frmManualTasksDataEntry]![cbxDisabilityIndicator]" & "[VolumeCode] = Forms![frmManualTasksDataEntry]![cbxVolumeCode]")
End Sub

many thanks for any help!

If you can set the RowSource of the combobox to a query that contains all
these bits of information, then you can hide all the columns you want to
reference but not show by setting their widths to 0. You have to be sure to
set the column count to the number of columns in your select statement.
Then you can have several unbound controls on your form that just show the
data from the hidden columns in your combobox.

=cboRecordSource.Column(1)

Just remember that the leftmost column is Column(0).

So you'd have several unbound textboxes with the rowsource set to
=me.[Name of your combobox].Column(ColumnIndex)