Form info in to Subform.... again

[DaleG]

I know this has been asked a million times, I just can't seem to find an answer that works. I know you are going to say -"why would you even do that?", so I will explain my goal first and if there is a better way - tell me.

We have a need for our international team to be able to create maintenance contracts in multiple currencies (USD, British pound sterling, and Euro). The maintenance contracts contain a detailed configuration of the equipment being maintained, and it can be 20 or so items. I have a table of customers (tblCustomer) and a table of configurations (tblConfiguration). In tblCustomer, the quote number for the specific quote is the primary key since it is only used once and for the particular contract in question. It is NOT generated in access - it comes from a sales quoting process in SalesForce, so it has importance for historical purposes.

Now, I have a form for the customer data entry (frmCustomerInput). It has entry for quote number, CURRENCY (USD, GBP, or EUR), name, address, etc. Now the CURRENCY field is a Combo box tied to a third table that has the 3 currency types and their *current exchange rates*. Currenty - this combo box does nothing.

There is a subform named frmConfigurationSBFRM. This subform is in datasheet view for easy data entry and contains fields quote number (which is autofilled from the main form), Currency (combo box), ExchangeRate (autopopulated from a lookup based on the currency field ), partnumber (combo box pulling from a full list of parts), description (auto populating based on part number entry), pricing, etc.

As it is, its working pretty good. It is very important that the currency and exchange rate be written to the configuration subform, because that changes. The exchange rate won't be the same next month - and the NEW values for the exchange rates will be input in the exchange rates table. So I need to write the *current at time of creation* currency and rate to the configuration table for historical auditing.

What I would like to do - is to be able to change the combobox on frmCustomerInput, and then every line added in the subform frmConfigurationSBFRM automatically get that currency value, which then would populate the exchange rate, so the salesperson only has to put in the currency type once.



I get the reasoning behind queries tabulating info, but I hope it makes sense that by doing that, it would recalculate based on the current value in the exchange rate table and not reflect the right values in place at the time it was generated, so I don't think that would work.

I am a beginner and freely admit I am over my head, but I am just trying to create a tool for our guys to use for the next 6 months to a year while we migrate to a new total platform.

Thank you for any help.

[davegri]

In the subform Form_Current event

if me.newrecord then 
     me.currency = me.parent.cboCurrency
endif

Adjust names for your situation.

[ArviLaanemets]

In Quotes subform properties, set it's datasource's Currency field linked to currency field of Contract Generator form datasource (Link Child Field, Link Master Field);
Hide currency control or set it disabled, so users can't mess with it.

Whenever a new record is created in subform, now the currency field in subform is filled with currency value in master form (NB! The control, not the table field!). When the record is saved, the currency value is saved along with other controls.

[Gicu]

And do the same with the exchange rate as it seems like that is (also) a very import part. You can add a hidden text box (txtExchangeRate) to the main form and set its control source to the currency combo's second column (cboCurrency.Column(1) - they are zero based) then in the master\child linking add them (using semi-colons) to the list.

Cheers,
Vlad

[DaleG]

Thank you! That worked!