wrong info showing up after choosing from combo box

[gmazza via AccessMonster.com]

I have a combo box that shows 2 key fields from my table.

On my form, if I navigate through the records, the 2 fields on my form show
different values, depending on whats in the table. However, if I choose from
the combo box, only the 1st field shows on the form and the 2nd field doesn't
change. How can I get around this?
Here is a sample:

Table Fields(both keys):
Criteria Country
1 USA
1 Canada

On the form when I choose 1 USA, it shows on my form. When I choose 1 Canada
from the combo, USA still remains in the Country field on the form.

I tried a DLookup to return the Country but the dlookup only looks at the
first record, which is the USA, can I get it to look at both records?

Thanks!

 

[June7 via AccessMonster.com]

Please show the code for the combobox RowSource and the DLookup. Where are
you executing the DLookup? Is Criteria and Country a compound key? By Country
field you mean one of the columns of the combobox?

 

[gmazza via AccessMonster.com]

Combo box(cboTrademark) Rowsource:
SELECT Competitor.Trademark, Competitor.CountryCode, zcCountry.CountryDesc
FROM zcCountry INNER JOIN Competitor ON zcCountry.CountryCode = Competitor.
CountryCode
ORDER BY Competitor.Trademark;

DLookup in OnChange of the above combo box:

= Nz(DLookup("CountryCode", "Competitor", "Trademark = " & Chr(34) &
cboTrademark.Column(0) & Chr(34) & _
" And CountryCode = " & Chr(34) & cboTrademark.Column(1) & Chr(34)))

Not sure what you mean by compound key?
Now that I am showing code, the actual key is Trademark and CountryCode.

By Country Field I just mean I have a field on my subform called Country and
I want it updated with the CountryCode.

Hope that helps you out, thanks for your response!

Please show the code for the combobox RowSource and the DLookup. Where are
you executing the DLookup? Is Criteria and Country a compound key? By Country
field you mean one of the columns of the combobox?

I have a combo box that shows 2 key fields from my table.

[quoted text clipped - 16 lines]

Thanks!

 

[Beetle]

It's still not completely clear what your are trying to do.

If you are trying to display the CountryCode from the row
source of your combo box, you can use;

=[cboTrademark].[Column](1)

If your are trying to display the CountryDesc from the row
source of your combo box, you can use;

=[cboTrademark].[Column](2)

But you would not put this expression in the On Change
event property of your combo box. You would have an
*unbound* text box on your form and you would put the
expression in the Control Source of that unbound text box.

--
_________

Sean Bailey

Combo box(cboTrademark) Rowsource:
SELECT Competitor.Trademark, Competitor.CountryCode, zcCountry.CountryDesc
FROM zcCountry INNER JOIN Competitor ON zcCountry.CountryCode = Competitor.
CountryCode
ORDER BY Competitor.Trademark;

DLookup in OnChange of the above combo box:

= Nz(DLookup("CountryCode", "Competitor", "Trademark = " & Chr(34) &
cboTrademark.Column(0) & Chr(34) & _
" And CountryCode = " & Chr(34) & cboTrademark.Column(1) & Chr(34)))

Not sure what you mean by compound key?
Now that I am showing code, the actual key is Trademark and CountryCode.

By Country Field I just mean I have a field on my subform called Country and
I want it updated with the CountryCode.

Hope that helps you out, thanks for your response!

Please show the code for the combobox RowSource and the DLookup. Where are
you executing the DLookup? Is Criteria and Country a compound key? By Country
field you mean one of the columns of the combobox?

I have a combo box that shows 2 key fields from my table.

[quoted text clipped - 16 lines]

Thanks!

 

[gmazza via AccessMonster.com]

I did as you said, which I did before as well, I put
=[cboTrademark].[Column](1) in an unbound text box on my form.
When I run the form the textbox shows UNITED STATES.
When I change the combo to the same criteria but a different country, the
textbox still says UNITED STATES.

It's still not completely clear what your are trying to do.

If you are trying to display the CountryCode from the row
source of your combo box, you can use;

=[cboTrademark].[Column](1)

If your are trying to display the CountryDesc from the row
source of your combo box, you can use;

=[cboTrademark].[Column](2)

But you would not put this expression in the On Change
event property of your combo box. You would have an
*unbound* text box on your form and you would put the
expression in the Control Source of that unbound text box.

Combo box(cboTrademark) Rowsource:
SELECT Competitor.Trademark, Competitor.CountryCode, zcCountry.CountryDesc

[quoted text clipped - 25 lines]