Question How should I approach for this Access DB (1 Viewer)

[zubersurati]

Hi,
I am using Access 2007 . Created two tables Namely.
tblCountries (Pk CountryID, CountryNames,etc)
tblTypes (Pk TypesID,TypeDescrip)
then I created Junction table tblCountryNTypes
Pk CountryNTypeID, CountryID numeric,TypesID numeric) also in Index
created TypesID as Unique reason that only one record of CountryID and
TypesID I wanted.

Then created thinking of creating form with bound Combobox CmbCountry for countries.
Second either Combobox or Listbox. Now what I want to do is
when picking up Country in CmbCountry second Combobox or
Listbox should populate from ID of Country and Types from tblCountryNTypes.
If Types for that country is not available than I should be able to add
Types Opening a Popup Form to Add Types for that Country.

Could some kind soul will direct me in right direction...?
also please let me know am right or wrong int his approach. In tblTypes I have
about 49 Types records. :banghead:
If this is not right forum than please advice me for right Forum.
Thanks in advance.

 

[Isskint]

The approach with 3 tables is good.

It is a fairly simple thing to acheive with the comboxes.
Add criteria to the RowSource SQL for the second combobox linking it with the country ID in the first combobox. EG

SELECT tblCountriesNTypes.CounrtyNTypesID, tblCountriesNTypes.CountryID, tblCountriesNTypes.TypesID FROM tblCountriesNTypes WHERE (((tblCountriesNTypes.CountryID)=[Forms]![Form2]![Combo2])) ORDER BY tblCountriesNTypes.[CountryID], tblCountriesNTypes.[TypesID];

In the CHANGE event for your Countries combobox requery the second combobox (or listbox). Then check if the second combobox has data and if not open your popup form. In this example combo2 is your Countries and combo4 is the list of CountriesNTypes

Private Sub Combo2_Change()
Me.Combo4.Requery
If Me.Combo4.ListCount = 0 Then
DoCmd.OpenForm "PopUp Form Name", acNormal
End If
End Sub

 

[zubersurati]

Thank you very much Isskint . Appreciate very much will try and come back further
when addition of tables. will try both Combo box as well as List bx and see
which suits .. forms layouts.
again Thanks

surati

 

[zubersurati]

Hi I am trying but cmbTypes or lstTypes does not populate . will try and comeback.