Combo box based on a Query

[dwood@atx]

I have a combo box based on a query. The query selects the current months currency exchange rates. the combo box allows the user to select the type of currency and then one of the columns in the combo box is used to multiply a foreign amount into US dollars. The problem I am having is that no mater what I select from the list the box always displays the first item in the list.
Any help would be greatly appreciated as I have been trying to figure this out for a while now without success. Please let me know if I need to provide any further information.

Thank!

 

[CJ_London]

what do you mean the first item? the first row or the first column?

 

[dwood@atx]

what do you mean the first item? the first row or the first column?

Yes the first row of Data. Sorry I was not clear.

 

[CJ_London]

OK so what is the sql to the rowsource to your combo - if it is the name of a query, that means the sql to the query.

Also the following information

The bound column
The column widths
The control source (if any)

 

[dwood@atx]

OK so what is the sql to the rowsource to your combo - if it is the name of a query, that means the sql to the query.

Also the following information

The bound column
The column widths
The control source (if any)

here is the SQL
SELECT CurrentExchangeQry.ID, CurrentExchangeQry.CurrencyType, CurrentExchangeQry.CurrencyDate, CurrentExchangeQry.CurrencyRate
FROM CurrentExchangeQry
ORDER BY CurrentExchangeQry.CurrencyType;

Bound column 3
column widths 0";1";1";1"
control source Currency (One of my tables)

I also have an On Change Event for this combo box (Not sure if it could be the issue) but it is as follows, just in case

Private Sub Currency_Change()
Dim CTotal As Double
Dim CRate As Double

CRate = Me.Currency.Column(3)
CTotal = Me.ForeignValue * CRate

Me.USValue = CTotal

End Sub

 

[CJ_London]

the change event should not make a difference providing the user always uses a mouse to select from the dropdown. I don't know what currency type is but if the user was to type the name of the type the change event is triggered for each press of a key - would be better if this code was in the after update event - although if it is updating a control on your form, you could just put

= [ForeignValue] * [Currency].[Column](3)

in the cTotal controlsource


However the main issue is your bound column - your controlsource is currency (which is a reserved word by the way - it is a data type - so using it can cause unexpected and misleading errors. I strongly recommend you change it to currencyID or similar), but you have bound to the currencydate column which is a datetype. So if you have 2 or more currencies with the same currencydate, the combo will display the first row it comes to with the selected date. This is the reason your combo is not changing. I would expect the bound column to be 1 (the ID field)

It is slightly confusing but the bound column starts at 1, whilst the column numbers start from 0 - so bound column 1 is actually column 0.

It will help you to give all fields meaningful names - I've mentioned currency, but also ID - it has no context - ID of what? call it currencyID or whatever table it is ID of.

Personally I prefer to use CurrencyPK (for Primary Key) and CurrencyFK (for Foreign or Family Key) so it is also clear 'which end' of a relationship the field belongs.

I don't know what you are trying to achieve, but I suspect you need to look at (I assume) your two tables, one for currency names and one for rates and review how they work together in a query - it may be that you have the relationship as one currency, many rates but in your query you are really looking at the rates and then looking up the currency name it applies to - in which case the ID you need to be storing in your currency field is the ID of the rate, and not (as I suspect) the ID of the currency.

 

[dwood@atx]

the change event should not make a difference providing the user always uses a mouse to select from the dropdown. I don't know what currency type is but if the user was to type the name of the type the change event is triggered for each press of a key - would be better if this code was in the after update event - although if it is updating a control on your form, you could just put

= [ForeignValue] * [Currency].[Column](3)

in the cTotal controlsource


However the main issue is your bound column - your controlsource is currency (which is a reserved word by the way - it is a data type - so using it can cause unexpected and misleading errors. I strongly recommend you change it to currencyID or similar), but you have bound to the currencydate column which is a datetype. So if you have 2 or more currencies with the same currencydate, the combo will display the first row it comes to with the selected date. This is the reason your combo is not changing. I would expect the bound column to be 1 (the ID field)

It is slightly confusing but the bound column starts at 1, whilst the column numbers start from 0 - so bound column 1 is actually column 0.

It will help you to give all fields meaningful names - I've mentioned currency, but also ID - it has no context - ID of what? call it currencyID or whatever table it is ID of.

Personally I prefer to use CurrencyPK (for Primary Key) and CurrencyFK (for Foreign or Family Key) so it is also clear 'which end' of a relationship the field belongs.

I don't know what you are trying to achieve, but I suspect you need to look at (I assume) your two tables, one for currency names and one for rates and review how they work together in a query - it may be that you have the relationship as one currency, many rates but in your query you are really looking at the rates and then looking up the currency name it applies to - in which case the ID you need to be storing in your currency field is the ID of the rate, and not (as I suspect) the ID of the currency.

THANK YOU!!!!

I have been looking for this for so long!!!!! So dumb :banghead:that I didn't look at the Bound Column as the issue! because Yes the date for each months currency is the same...the first one on the list is always selected!
YAY! thank you!!!!!