assigning values from a combo box. 2

[elff]

A reply to a previous post got me so much closer to my goal, but I'm not quite there.

The project is to create an estimate. I have an estimate main table, and a table for line items for the estimate, and another for the Material data.

For various reasons we want to save the current cost, labor rate etc. for each material selected in the estimate line items

table-Estimate Item Detail
Fields (partial list)
EID-Material (number)
EID-Description (text)
EID-MatCost (currency)
EID-MatLabor (number)

table-Materials
Material-ID (number - linked to EID-Material)
Material-Desc (text)
Material-Cost (currency)
Material-Labor (number)

I've created a combo box on my estimate line item subform to select the Material number. I can save the Material-ID into the EID-Material field via the selections made when creating the combo box. I can display the Description, Cost and Labor values on the subform via the "=cmbMaterialID.column(x)" method. I've tried using an After Update event on the combo box properties to store the values for Description, Cost and Labor into the EID fields but so far have had no luck. I use the syntax:
Me![EID-Description] = Me![cmbMaterialID].column(1)
Me![EID-MatCost] = Me![cmbMaterialID].column(2)
Me![EID-MatLabor] = Me![cmbMaterialID].column(3)

the SQL select selected Material-ID, Material-Desc, Material-Cost, Material-Labor.

Any help or ideas would be appreciated.

 

[ZmrAbdulla]

Me![EID-Description].[b][COLOR=blue]Value[/color][/b] = Me![cmbMaterialID].column(1) 
Me![EID-MatCost].[b][COLOR=blue]Value[/color][/b] = Me![cmbMaterialID].column(2)
Me![EID-MatLabor].[b][COLOR=blue]Value[/color][/b] = Me![cmbMaterialID].column(3)

________________________________________
Zameer Abdulla
Visit Me
If you have never been hated by your child, you have never been a parent.

 

[Eupher]

elff,

Where are the EID controls located? On the subform or main form? If they are on the main form, try:

Me.Parent![EID-Description] = Me![cmbMaterialID].Column(1)
Me.Parent![EID-MatCost] = Me![cmbMaterialID].Column(2)
Me.Parent![EID-MatLabor] = Me![cmbMaterialID].Column(3)

Ken S.

 

[elff]

The EID controls are on the subform - or at least they were originally. I temporarily took them off to see if I could get the data to display on the subform using the cmbMaterialID.column(x). I want them to be on the subform and have the values assigned to them somehow. These will be display only fields - no tab stops or user changing. For Zameer's suggestion, do I literally add the ".value" syntax to what I had or does "value" represent something else? Thanks!

 

[Eupher]

Hi, elff,

I temporarily took them off to see if I could get the data to display on the subform using the cmbMaterialID.column(x).

Not sure what you mean by this... If you took the controls off the form, what controls were to display the data from the combo columns?

The .Value syntax in Zameer's suggestion is exactly as posted. However, as far as I know, the .Value property is the default and is assumed unless another property is explicitly coded. But, as Zameer knows, I've been wrong before. ;-) Give it a try, but I think it will not help.

FWIW, here's a link on referencing forms and subforms:

http://www.mvps.org/access/forms/frm0031.htm

Ken S.

 

[elff]

I guess I didn't word that too well. Yes there are controls on the subform, I just changed the data source from the EID table fields to the combo box column data - mostly to make sure I really did have the column numbers correct and to make sure I could display the data. When I had them as the EID table fields, I couldn't see anything because I don't have the assignments correct yet. I'll try adding the .value and also take a look at the link. Thanks.

 

[ZmrAbdulla]

Syntax for both combo and textbox are on main form

Me.TextBoxOnMainForm.Value = Me.ComboOnMainForm.Column(0)

Combo on Main form and textbox on subform

Me.MySubFormName.Form.TextBoxOnSubForm.Value = Me.ComboOnMainForm.Column(0)

As Eupher said .Value is the default but I prefer to write it for more precise reading of code.


________________________________________
Zameer Abdulla
Visit Me
If you have never been hated by your child, you have never been a parent.

 

[elff]

Thank you to all for your help. Adding the .Value to the syntax did make the difference.