Add records to linked tables

[kysterathome]

Hi all,

I am lost - it seems so simple and yet I don't get it:

Two tables:

tblContacts
ContactID - autonumber (primary key)
ContactName
City - number [linked to tblCity.CityID - enforcing Ref. Int.]

tblCity
CityID - autonumber (primary key)
CityName

The relationship is clear - but here is my problem (as easy as it may sound to you):

When I create a form and add all of the above components to it, I can still not add a new record to the tables... It drives me nuts - what do I have to do? Write a code to update both tables?? But that sounds too complex

Any help is welcome

Kysterathome

 

[BNPMike]

This looks like a form-subform situation. Use the Form Subform Wizard and it will work.

 

[willir]

I agree with Mike -- one way is to create a subform -- or use a combo box.

You have a "City". Do you want to see the "contracts"? Or do you want to assign a city to a contact?

If your main form is based on City, you have a city, and want to add contacts, then Mike's solution is perfect - use the wizard. You can also leave space on your main form, say, frmCity, create a smallish form, say sbfrmContact, open frmCity in design mode and then click on sbfrmContact from the database window and drag it to the frmCity. If you have created your relationships correct (with Relationships tool, from menu, "Tools" -> "Relationship"), then Access will automatically join the Master and Child links based on the CityID. (Hint: It is common to use the exact same name of the field primary key -> foreign key in both tables.)

If you want to add a city for a contact, open the main form based on tblContacts, say frmContact, in design mode. Make sure the "Properties" window is open (from menu, "View" -> "Properties") Change the City control on the form from a text box to combo box by right clicking on the text box and select "Change to" -> "Combo box".

As it is, you will just a list of numbers in your combo box, so you should tweak it. Select the "Data" tab from the "Properties" window and then select "RowSourceType" -- change it to "Table/Query" using the pick list. Next, select the "RowSource" and select the tblCity from the pick list - getting warmer... When you select the RowSource, you will see a "..." command button appear to the right -- click on the "..." button to open the query builder. You should see your tblCity - add CityID to the first column and CityName to the second column - sort CityName Ascending (makes the suers happy). Close and save the query. Now you see "CityID, CityName" when selecting but only see CityID on the form once selected, Select the "Format" tab in the "Properties" window. Change ColumnCount to 2. For ColumnWidth, add 0";1" to hide the first column. If your cities are larger than 1", feel free to change to 1.5" or as required.

Done. Now from the Contact form, the user can select the city.

Richard

 

[kysterathome]

BNPMike and Willir,

I have experimented a bit with the subforms and it turns out to be a good solution. Willir, the dropdown option I will use on another occasion.

THANK YOU BOTH!

Kysterathome