Relationships

[superrob5]

I am in the process of making a new helpdesk at work but I got a question.

HelpLog Table
HelpID
Initials of caller
User Name


User Table
User ID
HelpID
User name(first, last)

I am going to have them link by HelpID. Correct?


if so and say a user leaves the company but the record needs to still be there but I want to either hide the name from the drop down on the form, I could add a another field like status and based on y or n show name. But will this affect the record from showing up correctly or when add new records that user will not show up?

 

[dcx693]

Just a few comments since I wrote such an application a while ago. First, you do not need a "HelpID" field in your user table. Each user is going to most likely call in multiple times for either the same or multiple help requests. However, you are right that you do need a HelpID field in your HelpLog table since you need a way to identifying unique help requests. Next, consider that while one person will call in a help request, multiple users can be affected. You might want to include a third table with two fields: one with the HelpID number, the other with the user ID of the affected user. That way, you can have multiple users associated with a HelpID.

I had a status field in my application that was True if the employee was active, and false otherwise.

 

[superrob5]

The way the helpdesk works it we recieve calls first writting them down becasue it often difficult to type them. Also one problem one person. So if we receive multiple calls we know it an entire are and we just work it out and only log it once. So if we dont have a helpid what should I use a primary key.

 

[dcx693]

The primary key for user table is the UserID. I wouldn't even store a foreign key in the user table since each user can call in on multiple problems.

Also, you might prefer to store one problem with just one user, but what if you want to search by user?

Also, store the UserID in the HelpLog table, not the user name.

 

[superrob5]

shouldn't I keep track of what helpdesk calls get assigned to each user or do I not have to relate it, isnt that why I need a foreight key in the HelpLog???? I am confused.

 

[dcx693]

shouldn't I keep track of what helpdesk calls get assigned to each user or do I not have to relate it, isnt that why I need a foreight key in the HelpLog???? I am confused.

Yes, you do need the foreign key in the HelpLog table. That should be the UserID field from the User table.

 

[superrob5]

check out what happens if I select a user. I have it after pressing tab to auto input info. Now I just got to get it to work with saving the user id instead of the actual name

 

[dcx693]

I'd use the After Update event of the combo box instead of the On Lost Focus event. It changes the other fields immediately.

Also, you don't need to set the focus to a field in order to change it's values. Use shorter field notation like this:
Me.Telen = Me.Field66.Column(2)
Me.Department = Me.Field66.Column(1)
Me.Site = Me.Field66.Column(3)

The "Me" keyword is a shorthand for the current form running the code. So in this case, it stands for "Forms![LOGENTRYHD]". Also, you only need the brackets if the form name has a space or special character in it.

 

[superrob5]

yeah I will do that. This db was originally started by someone else so I am using the ideas from it and learning new tricks, also how do I get it to save the user id number instead of the name, but still show all the into like I have.

Rob

 

[dcx693]

how do I get it to save the user id number instead of the name, but still show all the into like I have.

First, the HELPLOG table that feeds the form does not have the UserID field you want. It does have username. So what I'd do is replace that username field with the UserID field, join then two tables in a query, and use that query to feed the form. Then you need to change the Controlsource of the combo box (Combo66) and the rowsource as well to include the UserID as a column in the combo box.

 

[superrob5]

kool I understand it now thanks alot but what really forces it to use the UserID instead of putting the actaully name in there.

 

[dcx693]

If you add the UserId field to that combo box, then you can change the Bound Column property from being 1 to the column where the UserID field is positioned. Simple, right?