is it possible to set default value for the field that would adapt to the value of another field?

[Miik]

Hi, I'm new here and, I'm also novice in MS Access.

I'd like to know whether it is possible to set default value for a field that would change its value according to the value of another field.
This is to say I've got two tables:
in the first one there are an id of a deal (IDDeal) and the deal number (DealNumber) - DealNumber is specific to IDDeal
in the second table there are more other information about these deals. Apart from many fields there are also IDDeal and Deal Number.


I'd like to somehow set the value of DealNumber in a way that when I insert the value of IDDeal, the DealNumber will appear automatically.

I suppose this is possible, but I have no idea how to do that.
I tried to do this afterwards using Update Query, but it 'heals' only records already written and after every new record it is necessary to run this query again. I'd like to make it more automatically
I'd be glad if you mind helping me.

[Mickjav]

I do this in one of my account programs I have say a suppler who has a default account set as say Groceries so as soon as I select a supplier the general ledger account default is set

I do it like Me.subform.Controls("AccountID").DefaultValue = Me![SupplierID]

[Miik]

Thanks for your response, maybe stupid question but where I should insert the code like 'Me.subform.Controls("AccountID").DefaultValue = Me![SupplierID]'? In Macro?

[Mickjav]

In the after update of a combobox but it does depend on how your system is setup.

mick

[Gicu]

Milk,

You don't need the Deal Number in the second table, as there is a one-to-one relationship between the IDDeal and Deal Number and you can always get it by joining the two tables by IDDeal and bringing the Deal Number from the first table. Having it in both tables goes against the good db design (have a readon this here:https://www.justsoftwaresolutions.co...cate-data.html) and can cause data integrity problems.

Cheers,

[Mickjav]

Thanks @gicu missed that.