Controlling which table to use via combo-box

[danikuper]

I have a database with two tables, one that stores invoices for US customers and one that stores invoices for UK customers.

Both tables have the same fields (invoice #, customer name, item, value, etc...) however the US table keeps values in dollars and the UK table keeps values in pounds.

I want to create a Form that will display the fields required for data entry - no problem here - and I want to have a combo-box at the end where the user will select which country (US or UK) that record belongs to.

Question: How to I tell Access that if a user selects US from the combo-box, the data should be stored in one table and if the user selects UK from the same combo-box, the values should go to another table?

Any help is appreciated!!!

thanks.

 

[FoFa]

I guess the real question is why 2 tables? A number field does not care if it is pounds, dollars, yen, rubels etc. But I am going to assume you are stuck with what you have. You would be better served using 2 forms, and selecting the proper form before putting data in/editing. Or you can use an UNBOUND form, and control the information by VBA or Queries. I would consider a design change to use one table and simplify things.

 

[danikuper]

Thanks for your advice.

I do agree that having everything in one table would make things easier... however there's another problem I just found out. Some invoices from UK may have values in dollars and some may have values in pounds!

If I cannot differentiate one currency from another through the currency format (because it takes whatever your regional windows settings says), how can I keep track of what's in dollars and what's in pounds?

I see only two ways for that:

a. Have two tables, one for pounds records and one for dollar amounts.
b. Have a field in one table that will say if the amount is dollars or pounds.

The idea of having the country field in the form would be that it would tell me which table (pounds or dollars) should receive the data. I agree that that field would have to be not US/UK but rather Dollars/Pounds.

Now that I think of it, having only one table and a field controlling the type of currency might be a better solution...

What do you think?

 

[FoFa]

Yes, that is the proper solution. Best idea is to use the common (I forget the name) or standard name for currency like USD (U.S. Dollars) as the flag. I forget what pounds are (BPS for British Pounds Sterling?) anyway in the future you could use other currency in the same way if needed.

 

[danikuper]

Great! I'll try that way then.

Thanks a lot