one to many relatiionship on data entry form

[Jen0dorf]

Hi

suspect I've gone wrong somewhere



I have two tables linked by a one to many relationship

tbl_place has a list of villages

tbl_baptism has a list of baptisms fo various villages

so one village can have multiple baptisms.

So had a bit of trouble creating a form to add anew record so what I did was:

Using the query wizard ceated a simple query adding all the fields from tbl_villages and tbl_villages.

I viewed the query in design mode and it is shown as a 1 to 1 relationship

I then highlighted the saved query and selected the "Create Form" option to give me astarting point.

I can now add records perfectly and save them but when I view the table tbl_place it is writing a new place for every new record.

ie I have a village called Chapel Allerton which I expected to see once in the table but be linked to many in the baptism table. Instead I have seeing multiple fields with the value Chapel Allerton.

I have tried right clicking of the query relationship join and trying the three options but that did not help

IS this Ok or have I made a mistake?

thanks

Ian

[pbaldy]

Normally a one-to-many relationship is presented with a form/subform. In your case, the form would be bound to tbl_place, the subform to tbl_baptism. Master/child links keep the subform in sync with the main form.

[ssanfu]

Well, not the way I would do it.

so one village can have multiple baptisms.

So you have a FK field in tbl_baptism for tbl_villages.

Create a new query. Add the table tbl_baptism to the query. Drag the fields to the grid.
Save the query as "qrybaptism".
Create a new form. Set "qrybaptism" as the record source.
Add the fields to the detail section.

For the tbl_baptism PK field, set the visible property to NO.

Find the FK field for tbl_villages.
Change the control from a text box to a combo box. Set the row source property to a query. I would have the PK field for tbl_villages and the village name. Something like "SELECT villageID_PK, VillageName FROM tbl_villages ORDER BY VillageName".

Save the form.

When you add a new baptism, enter the info and select the village name from the combo box.

[Jen0dorf]

Hi Steve

thanks for the method-ology, went through it today, got lost, then confused and finaly saw where you were coming from and
realised I was approaching it all wrong my logic was just flawed. followed your logic and it's all working great.

thanks a lot Ian

[ssanfu]

Happy to help...