[Solved] Creating a form to insert data into multiple tables

[madwolf]

Good day,

I would like to apologise if my question had been surfaced before, however, I was unable to find anything relevant to solve my issue here.

This is my problem:

I would like to create a form listing my students' particulars (tblStudents), their medical status (tblMedical) and the lessons they'd missed (tblLesson_Missed) due to their medical leave.

Each table (tblStudents, tblMedical, tblLesson_Missed) have their own unique key, txtStudent_ID, linked.

That means that if I were to enter to this form, frmMain, it will display the students' information, medical leave that he/she had taken and the lessons he/she had missed, so that we are able to make up for their lesson missed and not shortchanged them in any way. In that particular form, I am able to enter new Student Particulars and subsequently, re-visit, search for the particular student by using a drop-down list (combo box), displaying their names and entering their medical status and lessons missed.

I tried using sub-forms but failed. I hope I am clear in stating out this problem.

Thank you.

[kabing]

What failed with the subforms?

The search combo box is possible, but only with a macro. Is that how you did it?

kabing

[madwolf]

I'd managed to solve the issue by creating multiple sub-forms. My main problem here is that when I am to choose the Student, the other particulars field do not refresh themselves.

For instance, I want to be able to look at the data inside tblStudents and with the students' Key ID, I am able to input new data into tblMedical and tblLesson_Missed as the previous 2 tables share the same Key ID as directed in the relationship.

I do not know how to write a macro, however.

I used to do programming but haven't done it for the last 8 years, so I need some help.

Thanks

[madwolf]

After some reading, I realised there's a huge mistake in my database design.

In tblMedical and tblLesson_Missed, I shouldn't make the Student_ID as the primary key, instead, having a seperate rolling primary key created automatically...

Beacuse 1 student can have many medical status and have many lessons missed...

Still working on my problem still. Appreciate all kinds of help.

[madwolf]

It was actually simple, I realised...

I just need to create sub-forms into my Main Forms and have the related fields linked...

I guess, i was stuck because there were many technicalities that I was unsure of.

[kabing]

I'm glad you got it figured out! In terms of a search combo box, check out:

this post which contains a macro and links to a couple of other resources in that regard.

You should also probably read this thread, beginning with my post this link leads you to, through the rest of the thread. Villeroy suggests a variation to the macro I provided that may work better for you, pluss there's info about what needs to be changed in the macro to match it to your form.

kabing