how do I auto fill a text box according to a selection made in combo?

[davidg47]

I am developing an Order System using the Order Entry template supplied with Access 2000. I am adding some functions to the application as well as taking some of the un-needed functionality out of the template.
One thing I am adding to the application is the ability for the client to choose which currency the order was sold in; then using a formula for the current exchange rate for the country in which the order was sold in, show the order in US Dollars. The client wants to keep a record of all the exchange rates in the past as well as the dates on which the exchange rate changed. So, I created a form with a sub-form that would allow them to first find the country for which the exchange rate is being entered for by way of a combo box that would allow them to scroll and find the country. When the country is found, the sub-form displays the exchange rates and the dates on which the exchange rates were entered. At this time the client would be able to enter the new exchange rate and the date on which it changed.
Now, on the orders page, (much like the orders page in the template with a few changes) I have added a combo box that will allow the person entering the order to choose the currency the order was sold in. My problem is, when the person (salesperson) enters the currency by way of the combo box, is there a way that the exchange rate field can be automatically filled in with the “latest, most recent” exchange rate for that currency? Then in the USD field, I will place a formula that will multiply the exchange rate with the price of the order to obtain the USD amount, thus auto filling the USD amount box also.
Please forgive me if my questions seem elementary, but I am just getting started, and I don’t have any books that explain how to do this sort of thing.
Thanks in advance for your help,
David Greene
admin@mindinthesky.com

 

[Prattdakota]

Here is some code I wrote just this morning that takes a value from a combo box (cboReasonCode) and looks up the Reason Category based on the combo box value. Use the AfterUpdate event of the combo box to write the code. The table tblRefusalReason contains the Reason Code and the Category it belongs to.

Private Sub cboReasonCode_AfterUpdate()

Dim rstRefusalReason As ADODB.Recordset

Set rstRefusalReason = New ADODB.Recordset

With rstRefusalReason
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "SELECT tblRefusalReason.Category " & _
"FROM tblRefusalReason " & _
"WHERE tblRefusalReason.ReasonCode = " & _ Me.cboReasonCode
End With

Me.txtReasonCategory = rstRefusalReason!Category
rstRefusalReason.Close

End Sub