updating fields based on another field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm creating a simple database that will allow me to track invoices issued
out to a customer from different countries.
I have a table (INVOICES_TABLE) that contains:
Country --> Country of issuance
Region --> Dependin on the country of issuance
Currency --> Depending on the country of issuance
Exchange Rate --> Depending on the country of issuance
There are other fields, but those are for manual entry.
I have a form (INVOICES_FORM), that I'll be using to enter the data into the
INVOICES_TABLE. This table contains the fields listed above plus all the
other manual entry fields.
What I'm trying to to is to have the Region, Currency and Exchange Rate
fields update automatically on my form and table by selecting a Country from
the Country Combo Box on the form.
How can accomplish this?
 
I guess I should add that I also have a table (REFERENCE) which lists all of
my Countries with their respective Region, Currency and Rate. My initial
train of thought was to have the Country Combo Box feed off of this table and
the other three fields would then update based on that selection. I was able
to get the Country ComboBox to list the countries based on the REFERENCE
table, but no matter what I do... I can't get the other three fields to
update once the country is selected.

Thanks for you assistance.
 
I'm creating a simple database that will allow me to track invoices issued
out to a customer from different countries.
I have a table (INVOICES_TABLE) that contains:
Country --> Country of issuance
Region --> Dependin on the country of issuance
Currency --> Depending on the country of issuance
Exchange Rate --> Depending on the country of issuance
There are other fields, but those are for manual entry.
I have a form (INVOICES_FORM), that I'll be using to enter the data into the
INVOICES_TABLE. This table contains the fields listed above plus all the
other manual entry fields.
What I'm trying to to is to have the Region, Currency and Exchange Rate
fields update automatically on my form and table by selecting a Country from
the Country Combo Box on the form.
How can accomplish this?
Click to expand...

Well...

Don't.

The Region and Currency fields should exist in the country table, and
only in the country table. An invoice (printed) can and should be
based on a Query joining the invoice table to the Country table.
France is going to still be in Western Europe, and still using Euros,
no matter when the invoice is generated - there is NO need to store
that piece of information redundantly in the Invoice table!

The exchange rate may be another matter, since it is time-sensitive.
YOu can "push" the current exchange rate from the country Combo Box
into the invoice table exchange rate field; in the combo box's
AfterUpdate event put code like

Private Sub cboCountry_AfterUpdate()
Me!txtExchangeRate = Me!cboCountry.Column(2)
End Sub

using of course the names of the actual form controls involved. The
Column() property is zero based, so (2) means take the THIRD field in
the combo box's Row Source query.

John W. Vinson[MVP]
 
John,

Thank you very much for your help. I realize now that my table setup was
not the most efficient way of achieving what I needed. I think I have under
control now. I'll post here again if I get stuck.. I know that you or any of
the other MVP's will have an answer for me when (note that I say when as
opposed to If) that happens.

thanks again
Sebastian
 
Back
Top