Combo box returning wrong value

[Goldar]

I am having trouble with a combo box, which is filled by a query to that
displays two fields (Fund # and Fund description). The fields are unbound
and I am using VBA to store the unbound fields in a table. I am using an
option box to determine which sequence to use to present the data (I use two
different queries and store one of them in the row source of the combo box,
depending on the option box value). This all works fine. The problem comes
when an item is selected. I would like to store the values both the Fund #
and the Fund value in my table with VBA. My VBA code in the combo box
AfterUpdate routine displays (and stores) the Fund # value correctly, and the
Fund description is also displayed correctly . However, the Fund description
gets stored in the table with a value of the Fund #. I store the values of
the combo box using cbo.column( "field 0 or 1", listIndex) statement, where 0
indicates that the row source query lists the Fund Description first, and 1
indicates that the row source query lists the Fund Description second.

I have tried a number of combinations of bound column=1 or =2, but although
I get different results, none seem to be correct.

I just want to display each of the two fields on my form and then write the
values to a table. Somehow, the Fund description gets written as the fund #.

Any ideas would be helpful.

Thanks

 

[Dorian]

To access columns, use syntax Fieldname.Column(0) or Fieldname.Column(1) etc.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Goldar]

I'm really confused now. When I've specified the sequence of the combo box to
be Fund description, everything works fine. When I specify the sequence to be
Fund #, the Fund # field is correct but the Fund Description field either is
blank, or it's the same value as the Fund # field. Initially, I set the
sequence to be by Fund description, combo column 1 is also Fund Description
and column #2 is Fund #. When the requested sequence changes to Fund #, I
replace the row source with a query that simply reverses the fields to Fund #
and Fund Description. the iother combo properties are changed so that the
Bound column is 2 and the column widths are reversed.

Note: There are five identical fields (fldFundCode 1-5) and combo boxes
(fldFudnDesc 1-5)

Option box AfterUpdate code:
Private Sub optFundSeq_AfterUpdate()
' Change sequence in Fund lookup
Dim objName As String
For i = 1 To 5
objName = "fldFundDesc" & CStr(i)
Set pge = TabCtl0.Pages("pgeFunding")
Set cbo = pge.Controls(objName)
Select Case optFundSeq.value
' setup combo boxes for correct sequence
Case conDesc
Set qdf = dbs.QueryDefs("qrySplitFundsDesc")
cbo.ColumnWidths = "1.75 in;1 in"
cbo.BoundColumn = 1
Case conFund
Set qdf = dbs.QueryDefs("qrySplitFundsFund")
cbo.ColumnWidths = "1 in;1.75 in"
cbo.BoundColumn = 2
End Select
cbo.RowSource = qdf.SQL
Set cbo = Nothing
qdf.Close
Next i
End Sub

Combo box AfterUpdate code:

Sub fldFundDesc1_AfterUpdate()
updateFund "fldFundDesc1", "fldFundCode1"
End Sub

Sub fldFundDesc2_AfterUpdate()
updateFund "fldFundDesc2", "fldFundCode2"
End Sub

Sub fldFundDesc3_AfterUpdate()
updateFund "fldFundDesc3", "fldFundCode3"
End Sub

Sub fldFundDesc4_AfterUpdate()
updateFund "fldFundDesc4", "fldFundCode4"
End Sub

Sub fldFundDesc5_AfterUpdate()
updateFund "fldFundDesc5", "fldFundCode5"
End Sub
Sub updateFund(objName As String, objFund As String)
Dim pge As Page
Set pge = TabCtl0.Pages("pgeFunding")
Set cbo = pge.Controls(objName)
Select Case optFundSeq.value
Case conDesc
' by description
Me(objName) = cbo.Column(0) 'Description
Me(objFund) = cbo.Column(1) 'Fund #
Case conFund
' by fund number
Me(objFund) = cbo.Column(0) 'Fund #
Me(objName) = cbo.Column(1) 'Description
End Select
Set cbo = Nothing
End Sub

When I select a row in the combo box, the Fund # field on my form changes
correctly but the Fund description is sometime blank and sometimes the same
as Fund #. When I trace the code in debug mode, all of the values are
correct, but when I exit debug mode, the Fund Description is incorrect.

I am including the code for 1) handling the change in the option box that
reflects combo sequence, and the code for handling the after update process
for the combo box.

If you need any more information, please let me know.

Thanks ...