CODEWITHSUNDEEP
ms-access
Jose Torres-Jasso
Jose Torres-Jasso

Reputation: 1

Saving Master Table from Form built off Multiple Tables Microsoft Access

I am new to Microsoft Access and I am trying to build a Case Tracking system for our Employee Relations folks. I have a table for Employees and a table for Cases. They are linked by Employee ID. I also have a form to incorporates fields from both tables so they can fill out when a case comes in. How do I save the data on the form into a master case history table? I want to capture the employee data as it was when the case was added since departments, managers and other fields are dynamic for the Employee. My form includes:

Employee Table Employee ID, Company, Job Family Group, Cases Table Case Number, Case Date, Case Type,

I know I can obviously write a query to join them, but it will show me the employee facts as they are as of the last data refresh. Any help you can provide would be greatly appreciated! Thank you again.

Upvotes: 0

Views: 113

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 49059

Actually, the way you approach this?

Well, you have a main form - employees.

Then, you have (make) a sub form of cases.

Now, that sub form can be a grid like display - perhaps you select (click) on a row to show + edit more details, or perhaps just a sub form as a grid (continues form) will suffice.

In other words, you don't make the form update both tables at once, but in fact use a main form, and a sub form.

Say, I have a table of Hotels. And I need/want to display hotel information, and THEN ALSO display people booked into that hotel?

so, I create a form based on table People. That table People of course has a column that relates back to the hotel they are booked to.

So, you get this:

enter image description here

So, each form STILL ALMOST ALWAYS will be based on ONE table. Don't try and make some multi-table join for the forms data source.

So, in your example?

you build a main form - employees - it can show all the information (and let you edit/add etc.) to that form.

For each case, from the case table?

Well, go off and build a 100% separate form for display of "case(s)".

Then save that form. Now, open up your employee form, and in design mode, simple drag + drop in the cases form into that form.

So, you don't actually base the form and its table on more then one table, but you combine multiple forms EACH based on their one respective table.

But, as above shows, the end result is a main form + sub form, and that gets you the relational database setup, and allows you to do this without having to write code.

Upvotes: 1

Related Questions