Link to home
Start Free TrialLog in
Avatar of SteveL13
SteveL13Flag for United States of America

asked on

Trying to use combobox column value on a report

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)]
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

is the form frmTimeReportingHeader open ?
and is there an item selected in  combo [cboProject]?
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SteveL13

ASKER

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.
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.
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."
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.