A simple input form for a one to many, many to one, 3 table relationship

[Bob Fitz]

Thanks again Bob - yes i would be interested in that alternative approach - the more options and learning the better!

In the attached db you can add to the list of studies by typing the new study name into the combo box(cmbFind). See the code i the "Not On List" event of the combo box. Post back if you have any questions

[AndyRob1973]

Alternatively, you could use the following line the "After Update" event of the form called "frmStudies".

Forms!frmMF_StudyRecruits.cmbFind.Requery


FWIW my personal preference would be to NOT have a button to open "frmStudies" but use the "Not On List" event of the "cmbFind" combo box to open "frmStudies". Let me know if you'd like to try that approach instead.

I think I will use the "Not in List" method but I'm curious as to why the code below doesn't seem to work in "After Update" event of the form called "frmStudies".

Forms!frmMF_StudyRecruits.cmbFind.Requery

The new study appears and i can select it but it doesn't update the boxes below (ie IRAS, StudyName and LeadNurse, or the frmEnrolments) until i close the forms and reopen. Could you help?

Much appreciated Bob

[Bob Fitz]

I think I will use the "Not in List" method but I'm curious as to why the code below doesn't seem to work in "After Update" event of the form called "frmStudies".

Forms!frmMF_StudyRecruits.cmbFind.Requery

The new study appears and i can select it but it doesn't update the boxes below (ie IRAS, StudyName and LeadNurse, or the frmEnrolments) until i close the forms and reopen. Could you help?

Much appreciated Bob

When I said earlier that I would prefer to use the Not On List event of the combo box I hadn't understood anything of what your db was about or how it was working. I hadn't realized for example that the combo was being used to find a record rather than for data entry. Nor had I realized that the form with the combo is bound the same data table as the form you were opening to create a new entry. I don't understand why you would use a second form when you could just go to a new record in the first form.

I would usually use the Not On List event to create a new record when that combo is used for data entry. For example, when you want to add a new product to the list while creating a detail line on an invoice.

However, after saying all that, the attached db does allow you to create a new "Study" in the combo box after which the code moves the focus to that record.
Post back if you have any further questions.

[AndyRob1973]

When I said earlier that I would prefer to use the Not On List event of the combo box I hadn't understood anything of what your db was about or how it was working. I hadn't realized for example that the combo was being used to find a record rather than for data entry. Nor had I realized that the form with the combo is bound the same data table as the form you were opening to create a new entry. I don't understand why you would use a second form when you could just go to a new record in the first form.

I would usually use the Not On List event to create a new record when that combo is used for data entry. For example, when you want to add a new product to the list while creating a detail line on an invoice.

However, after saying all that, the attached db does allow you to create a new "Study" in the combo box after which the code moves the focus to that record.
Post back if you have any further questions.

Hi Bob

Thankyou for your three replies.

I'll have a good look at them.

Andy