Prev: Credit Card Integration
Next: Form_current() firing multiple times
From: buzzmcduffie on 5 May 2010 12:30
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!
From: PieterLinden via AccessMonster.com on 5 May 2010 13:39
buzzmcduffie wrote:
>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)

--
Message posted via http://www.accessmonster.com

 | 
Pages: 1
Prev: Credit Card Integration
Next: Form_current() firing multiple times