Trying to use combobox column value on a report

SteveL13
SteveL13 used Ask the Experts™
on
I have a form that has a combobox.  Then on a report I am trying to reference in the control source of a field on the report, that comboboxes 2nd column.  (1st column after the key column)

Here is what I'm using but I get a #Name? error in the field:

=[Forms]![frmTimeReportingHeader]![cboProject].[Column(1)]
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
is the form frmTimeReportingHeader open ?
and is there an item selected in  combo [cboProject]?
Distinguished Expert 2017
Commented:
There are situations where you can't reference the properties of controls except from within the form/report itself.  To get around the problem, you can do one of two things.
1. modify the form to join to the lookup table (usually with a left join to avoid loosing records).  Then you can refer directly to the bound field  rather then refering to it in the combo.
2. add a hidden field to the form.  In the AfterUpdate event of the combo, copy the .Column(1) property to the hidden field and reference the hidden field.

Author

Commented:
Answers/comments:

is the form frmTimeReportingHeader open ?   -  YES.
and is there an item selected in  combo [cboProject]?   -  YES

1. modify the form to join to the lookup table (usually with a left join to avoid loosing records).  Then you can refer directly to the bound field  rather then refering to it in the combo.  -  I UNDERSTAND
 2. add a hidden field to the form.  In the AfterUpdate event of the combo, copy the .Column(1) property to the hidden field and reference the hidden field.  -  SOMETIMES IS DO THIS BUT IT ALWAYS FEELS "SLOPPY" WITH HIDDEN FIELDS ON A FORM.  MAYBE IT'S JUST ME.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Distinguished Expert 2017

Commented:
There are cases where Access thinks it is smarter than you.  You can't win.  This is especially true for reports.  Since Access knows that a report cannot be updated, it rebuilds the query you so carefully constructed as the record source and ELIMINATES any field NOT bound to a control.  Therefore, if you have a piece of code that does a calculation
Me.somefield = Me.A + Me.B
and A and B are not bound to controls, the calculation will fail because A and B are no longer part of the recordset.

Author

Commented:
Pat.  Sometimes I know Access is smarter than I am.  I am going to go ahead with:

"add a hidden field to the form.  In the AfterUpdate event of the combo, copy the .Column(1) property to the hidden field and reference the hidden field."
Distinguished Expert 2017

Commented:
I've been working with Access since the early 90's and no piece of software has EVER impressed me more.  The foresight of the initial team was scary good.  And then there's the little annoying things like this one.  I'm sure this problem was introduced later by some lesser developer.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial