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

[AndyRob1973]

Hi all,


I have a database where I have a ‘student’ table, a ‘course table’ and an ‘enrolment’ table.

A student can enrol on one or more courses and each course can have more than one student.

I have set up my relationships as below:

STUDENT (1----------Many) ENROLMENTS (Many-----------1) COURSES

I would like to build an input form where I can enrol a student on a course.

Ideally I could choose from pre existing students and courses but also add new ones on the form (but that can wait)

Could anyone point me to any simple sample forms or tutorial that will help me with this. For starters it only needs to be basic and I will build on it gradually.

Thank you

[ranman256]

I use list boxes. Source list, dbl-click to run append query to add to the Target table. (list)
The Accept button not always needed.

[Bob Fitz]

Hi all,
I have a database where I have a ‘student’ table, a ‘course table’ and an ‘enrolment’ table.

A student can enrol on one or more courses and each course can have more than one student.

I have set up my relationships as below:

STUDENT (1----------Many) ENROLMENTS (Many-----------1) COURSES

I would like to build an input form where I can enrol a student on a course.

Ideally I could choose from pre existing students and courses but also add new ones on the form (but that can wait)

Could anyone point me to any simple sample forms or tutorial that will help me with this. For starters it only needs to be basic and I will build on it gradually.

Thank you

The attached db may give you some ideas

[davegri]

Here's another working example:

https://www.accessforums.net/showthread.php?t=79814

[AndyRob1973]

Thank you all,

I shall have a look and digest these - I think they have put me on the right lines!

[AndyRob1973]

Thank you to all who helped on this. I have something which works.

[AndyRob1973]

Hi Bob,

I’ve been using the Student Database example that you sent me very successfully.
There are a couple of parts that I’m having a little struggle with.

I have added a button which allows the user to add a new course into the tblCourses table. When I click the ‘find’ button I can see the new course but when I select it, the details below do not change.

However, if close the form and reopen, click on this new course, it DOES change the details.

It is obviously requerying when I reopen the form but how do I force that to happen without closing a reopening the form?

Thanks

[AndyRob1973]

Hi Bob,

I’ve been using the Student Database example that you sent me very successfully.

There are a couple of parts that I’m having a little struggle with.

I have added a button which allows the user to add a new course into the tblCourses table.

When I click the ‘find’ button I can see the new course but when I select it, the details below do not change. However, if close the form and reopen, click on this new course, it DOES change the details.

It is obviously requerying when I reopen the form but how do I force that to happen without closing a reopening the form?

Thanks.

[Bob Fitz]

Hi Bob,

I’ve been using the Student Database example that you sent me very successfully.

There are a couple of parts that I’m having a little struggle with.

I have added a button which allows the user to add a new course into the tblCourses table.

When I click the ‘find’ button I can see the new course but when I select it, the details below do not change. However, if close the form and reopen, click on this new course, it DOES change the details.

It is obviously requerying when I reopen the form but how do I force that to happen without closing a reopening the form?

Thanks.

I'll look into it

[Bob Fitz]

AndyRob1973
I've had a look at the example db that I originally posted for you but my memory is not good enough to remember what exactly I was trying to demonstrate.
To add courses I would often use the "Not on List" of a combo box at the point where the user finds that the required course is not on the list.

I have added a button which allows the user to add a new course into the tblCourses table.

If you post a copy of your db I'd be happy to have a look at your current proplem.

[AndyRob1973]

Hi Bob

When you open the database (which holds dummy nonsensicaldata), from the menu choose ‘Manual Payment input’ form.

Then on the form that appears, click ‘add new study todatabase’.

Add a new study (just put anything – IRAS is a number but itdoesn’t matter). Save it.

Go back to the ‘Manual Payment input’ form and choose thenew study from the dropdown.
The information below stays the same as it was for theprevious selection.

Thanks

Andy

[Bob Fitz]

Use the following line of code in the "Got Focus" event of the combo box called "cmbFind" and labelled "Choose study".

[Bob Fitz]

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.

[AndyRob1973]

Use the following line of code in the "Got Focus" event of the combo box called "cmbFind" and labelled "Choose study".

Thank you Bob - did you mean to put some code in here?

[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.

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