How do I store a lcombo field value and add it into all subsequent related form fields?

[obembe]

I'm creating a database for my coin collection. I want to enter the data by
country, e.g. all coins for Italy, etc.

My two main tables are:-

tblCountry: pkCountryNo (autonumber); CountryName (text)

tblCoin: pkCoinNo (auto); fkCountryNo (long integer); etc.

My Coin form with lookup to countries works fine.
However, I want to create a (vertical) form with an initial lookup to
country, so that the country I choose, e.g. Italy, is displayed then stored.
If any coins exist for Italy, the first one will be displayed in the form.
Any ones I subsequently add will inherit the the looked-up country and be
updated behind the scenes. I won't make the conutry field visible in the
coin form, as it'll be apparent from the country lookup field what I'm
viewing/entering.

I know all the very simple form and query stuff - now I need to get
"intermediate." Any ideas on how I would achieve this?

Regards,

Adetola Obembe.

 

[Steve Schapel]

Adetola,

You could make an unbound form, and put the Coin form onto this other
form as a subform. Then, on the main form, you could put a combobox.
As far as I can see at the moment, this would be unbound, but the Row
Source would be the tblCountry table. Set the Link Master Fields
property of the subform to the name of the country combobox, and its
Link Child Fields property to CountryNo. That way, whenever you select
a country in the combobox, only those coins from that country should be
displayed, and any new records added will be assigned to that country.

By the way, a comment if I may, on the table design. There seems to be
no benefit in the Autonumber field in the tblCountry table, and just
makes it unnecessarily complicated. There will presumably never be two
countries with the same name, so I would prefer to just have the one
field CountryName, and use this also as the related field in the tblCoin
table.

 

[obembe]

You could make an unbound form, and put the Coin form onto this other

form as a subform...

Thanks, Steve, that worked a treat - eventually, since I'm such a novice!
Incidentally, I couldn't get it to work until I changed the country name to
primary key of its table, as you suggested.
Admittedly I'm from a legacy background, but I was always told that a
numbered lookup
meant no need for cascaded updates, if e.g. a country name changed, and also
takes up less space
in child records. In a live situation, your solution could rack up space
enormously. Oh well, its just a home app,
so space won't be an issue. Thanks again,

Adetola.