Incident Tracker (1 Viewer)

Kubalism

Registered User.
Local time
Today, 07:30
Joined
Jul 4, 2012
Messages
36
Incident Tracker - Cannot add record: join key of table "tblIncident Info" not in rec

Hi guys,

I've managed to start a few tables, a log in form and a little flash screen. I then created a form that will serve us a fill-up form.

But when I tried to input data to my "Location of Incident, Time, Date , Narrative and Resolution fields it doesn't allow me to and displays an Error message saying Cannot add record; join key of table "tblIncident Info" not in recordset.

To what I see, I think I mess up with my relationships. Or I don't know.

I would be willing to provide a link to my relationships field but i'm still not allowed to do so. I've already check the post by Nano but to no avail I can't find the solution to my problem there.

Hope someone can help me with this.

EDIT:

What i want to do is that there would only be ONLY one Form with tabs and that a user will just input the data on the fields provided. Is that advisable? Or would it be better if there are multiple forms?

I also already did several revisions and now on this current one I've decided to use a design concept from databaseanswers since it's closely the same design to what i have in mind.

I will post a zip file of my db later.
 
Last edited:

GinaWhipp

AWF VIP
Local time
Today, 10:30
Joined
Jun 21, 2011
Messages
5,901
Can you post the Recordset of the form and how those tables are related to each other?
 

Kubalism

Registered User.
Local time
Today, 07:30
Joined
Jul 4, 2012
Messages
36
Good day!

@Gina

Hello,

Please see attached DB.
Thanks

@boblarson

I've already seen that post sir, but in my office, zip files are not allowed because of security purposes.

Got a question sir, as to what I stated above, is my case similar to Nano? Because I've tried reading the entire thread of Nano but I can't seem to find a solution or maybe I just fail to spot it.

Regards,

EDIT:

I tried separating the fields to another table and then linking to the main table. Good news is that it allows me to enter value, bad news is when I open the form it will show an enter paramater value box.

I also noticed that when I open my DB on another PC, the fields on my form dissapears.

EDIT:

Please see attached db.
 

Attachments

  • Tracker 2.accdb
    1.8 MB · Views: 136
Last edited:

boblarson

Smeghead
Local time
Today, 07:30
Joined
Jan 12, 2001
Messages
32,059
Immediately upon opening the database I can see your problem with this form. You can't have a query like this for it:


It should actually be like this:




You should have the other tables (most of them) as SUBFORMS on each tab.
 

Attachments

  • Kubalism01.png
    Kubalism01.png
    36.9 KB · Views: 471
  • Kubalism02.png
    Kubalism02.png
    20.9 KB · Views: 461

Kubalism

Registered User.
Local time
Today, 07:30
Joined
Jul 4, 2012
Messages
36
Thanks for the swift reply sir.

Got a question.

What are the things that I should change and are my relationships fine? or could it be better ?

I'm really confused since this is my first time making this kind of database.

Please help me improve my database because I really need it for our new site.
 

boblarson

Smeghead
Local time
Today, 07:30
Joined
Jan 12, 2001
Messages
32,059
I did forget to mention that your table structure has a few changes that should be made.

First off, the Employee and Employee Superior should be in the same table like you have. You shouldn't have the Superior Info as a separate table. They are both employees, but except for the very top guy, they all would have a superior. So, we should end up with this:



 

Attachments

  • Kubalism03.png
    Kubalism03.png
    39.5 KB · Views: 510

Kubalism

Registered User.
Local time
Today, 07:30
Joined
Jul 4, 2012
Messages
36
I made another table for superior sir because I want to show that a single superior could have many employee and that a superior could have another ID.

By changing the tables, wouldn't that have an effect to my database?
 

boblarson

Smeghead
Local time
Today, 07:30
Joined
Jan 12, 2001
Messages
32,059
I made another table for superior sir because I want to show that a single superior could have many employee and that a superior could have another ID.

By changing the tables, wouldn't that have an effect to my database?

Is the superior an employee? Yes, they are. So they belong in the employees table. You have superiorID in the employees table to identify who that employee's superior is. So you put an employeeID in that superiorID field by having a combo box which has the list of employees to choose from so you can choose which superior is that employee's superior. Does that make sense? There is no need for a superior's table because you can use a query to get which employees are under that superior.
 
Last edited:

GinaWhipp

AWF VIP
Local time
Today, 10:30
Joined
Jun 21, 2011
Messages
5,901
Well, I see you are in capable hands so I'll just move along...
 

Kubalism

Registered User.
Local time
Today, 07:30
Joined
Jul 4, 2012
Messages
36
Is the superior an employee? Yes, they are. So they belong in the employees table. You have superiorID in the employees table to identify who that employee's superior is. So you put an employeeID in that superiorID field by having a combo box which has the list of employees to choose from so you can choose which superior is that employee's superior. Does that make sense? There is no need for a superior's table because you can use a query to get which employees are under that superior.

Sir,

What if employee A is transferred to another Superior, so I need to requery it?

Is this right?

please see image.

P.S

Sir, would it be possible that you can revise the db and post it here? please.
 

Attachments

  • Revised.jpg
    Revised.jpg
    96.2 KB · Views: 127
Last edited:

boblarson

Smeghead
Local time
Today, 07:30
Joined
Jan 12, 2001
Messages
32,059
You do not use lookups like that at table level (you shouldn't use lookups at table level anyway - see here for why: http://www.mvps.org/access/lookupfields.htm)

See the attached sample I created for you so you could see only the employees/superiors and how that would work. I included a form for entry and a query for viewing how it relates and how you use a second instance of the employees table to get the supervisor value.
 

Attachments

  • EmployeesAndSupervisor.mdb
    340 KB · Views: 138

Kubalism

Registered User.
Local time
Today, 07:30
Joined
Jul 4, 2012
Messages
36
Great sample.

Got a question. What is that on the relationship field there are tables that i dont see on navigation pane ? Does that table name suppose to hide it from the navigation pane?
 

boblarson

Smeghead
Local time
Today, 07:30
Joined
Jan 12, 2001
Messages
32,059
Are you talking about tblEmployees_1? If so, it isn't another table. It is just the same employees table added into the query TWICE. Once to get the employee information and the second instance (tbl_Employees_1) is for getting the supervisor information.
 

Kubalism

Registered User.
Local time
Today, 07:30
Joined
Jul 4, 2012
Messages
36
Are you talking about tblEmployees_1? If so, it isn't another table. It is just the same employees table added into the query TWICE. Once to get the employee information and the second instance (tbl_Employees_1) is for getting the supervisor information.

No.

I am talking about the tables on the relationship field. Please see attached image.
 

Attachments

  • relationship.JPG
    relationship.JPG
    19.4 KB · Views: 71

boblarson

Smeghead
Local time
Today, 07:30
Joined
Jan 12, 2001
Messages
32,059
Those are system generated. I didn't put them there. You can ignore them.
 

Kubalism

Registered User.
Local time
Today, 07:30
Joined
Jul 4, 2012
Messages
36
Ow my bad. :)

So it isn't necessary to have two tables at all. Now i get that part. Really helped me a lot. But here in our company we have like almost 100+ supervisors and approximately 40 Dept heads w/ fast turnover I would then need to update the table every week.

I will try to incorparate this with my original database and see what happens.
 

Kubalism

Registered User.
Local time
Today, 07:30
Joined
Jul 4, 2012
Messages
36
You'd need to do that any way you approach it.

Wouldn't it be better if I'll just disregard the combo box, and input the name everytime there is a new incident?

Edit:

I also tried inputting using the form, and the name that I inputed shows at the Supervisor combo box (which shouldn't)

Edit:

I used a Subform for the problem fields. Works Great. I hope I won't get any problems anymore.
 
Last edited:

Users who are viewing this thread

Top Bottom