controlsource = sum([comboBox].column(1)) !!!!!!!

[JennyPeters]

Does anyone know why the above code does not work? I'm trying to reference the column 1 value of a combobox, not the column(0) value, which is takes by default. I can't seem to get the right syntax to do this....

Any help would be great!

Thanks,

Jenny

 

[cmmrfrds]

Under the format tab in the combobox is your column count 2?

 

[JennyPeters]

Yes it is thanks. And the combo box works perfectly. It's the syntax that has problems in referencing that combo box.

Any thoughts about the syntax?

Thanks,

jenny

 

[cmmrfrds]

For example in the onclick event of the combobox named cbRequestStatus.

dim var1 as string
var1 = Me.cbRequestStatus.Column(1)

 

[JennyPeters]

Thanks again for the help.

The above syntax is actually in the control source of another text box that is trying to sum all the values in the continuous forms combo box. Because column 0 holds the PK, column 1 needs to be referenced, which is where the number to be summed lies.

Again, the problem lies in the syntax of text box referencing column 1 in the combobox, and no event in the combo box is going to do this.

Any other thoughts?

Jenny

 

[Norris68]

You cannot bind a control to the sum of another control. As far as I know, you can't sum another control. If you know the data source for your combo box, could you not define a similar SUM query to calculate the total? e.g. bind your field to =DSum("MyField","MyTable","MyCriteria&quot

You can reference any column of a combobox if you want using the Column(Index) property. You could also iterate through all rows in the combobox if you want e.g.

Dim Total As Long
Dim A As Long
For A = 0 To cboMyCombo.ListCount - 1
Total = Total + cboMyCombo.Column(1,A)
Next A

... so what you could maybe do is to make the above code a public function, and then bind your textbox control to it.

e.g.
Public Function SumComboColumn(TheCombo As ComboBox, TheColumn As Integer) As Long
Dim Total As Long
Dim A As Long
For A = 0 To TheCombo.ListCount - 1
Total = Total + TheCombo.Column(TheColumn, A)
Next A
SumComboColumn = Total
End Function

Then make your textbox ControlSource =SumComboColumn(MyComboBox,1)

Will that do?