Pulling records specific to a user

[cwhite23]

Good morning.

I need some advice on a problem. I have created what amounts to a time and tracking application, but I've run into a problem. In the application, I have a field where the employee enters their employee number and the date in the header, and then enters their daily activities in the Detail section of the form as a subform. The data is stored in a backend database, but I need to have the front end show only the records for that particular user, not everyone. What is the best way to accomplish this? Any help would be appreciated.

 

[chunter33]

All you need is to query your back end database with a front end query. This front-end query will be a simple select statement and it'll receive form input as an argument. Your form input is going to be your employee ID number, so in SQL your query would be something like so:

SELECT tblTimes.StartTime, tblTimes.EndTime, tblTimes.EmpID
FROM tblTimes
WHERE (((tblTimes.EmpID)=[forms]![form1]![empid]));

In this case, the input is coming from a simple text box. So I'll have my employee id entered into there, and then hit a button or something which will open my query in code. Of course, this query will only work if the form is open as it won't be able to find your form in the forms collection object otherwise.

 

[dhookom]

Since the data entry is into a subform, I would use the Link Master/Child properties of the subform. The Link Master would be the "field where the employee enters their employee number".

Duane
Hook'D on Access
MS Access MVP

 

[chunter33]

Oh didn't notice it was a subform, well that would make a small change to the query example I provided above. It would now be something a little closer to:

SELECT tblTimes.StartTime, tblTimes.EndTime, tblTimes.EmpIDFROM tblTimesWHERE (((tblTimes.EmpID)=[forms]![form1]![form2]![empid]));

 

[dhookom]

I believe the EmpID is being entered/captured on the main form rather than the subform.

Duane
Hook'D on Access
MS Access MVP

 

[chunter33]

Nope, I actually copied and pasted that SQL query directly from my little application I threw together. "EmpID" was a textbox control originally on the main form, but once I realized we were actually working with subforms I simply recreated it on the subform, removed it from my main form and modified my query to reflect that. Worked like a charm

 

[TheAceMan1]

How are ya cwhite23 . . .

Use a [blue]Single Form[/blue] which only shows [blue]one employee[/blue] at a time. The form should include a combobox which goes to the employee selected.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[cwhite23]

Thanks to everyone for the responses and ideas, but I think I need to clarify. The header form has three fields in the header: EmpID, Date, And EmpName. The first two are entered by the employess, the third fills in automatically courtesy of a dlookup based on the EmpID.

The subform has fields for start time, end time, what the job was and notes relating to that job.

All of this happens on the front end. On the back end, the data from all of the employees gets stored in two tables; the first is for the header, and the second is for all of the lines from the subform.

What I want to have happen is for an employee to open up the form at the start of the day, and be able to not only see their previous days' work, but also to be able to enter the current day's work as well. At no point should they have access to anyone else's data.

Will any of the ideas suggested accomplish this?

 

[chunter33]

I wasn't aware that the time and the header info was stored in seperate tables in your back-end.

It sounds what you're going to need is to add a new column for employee ID's to your second backend table that holds the times. That way, we can easily match each employee to his or her respective times and with the query I mentioned previously, populate your subform with the times for only the one employee in question.

You'll also have to modify the functionality that is updating your backend table to accommodate this new column. Considering introducing some sort of sign-in/login system to your application so that the employee ID for the user is always at hand with a single login while your application runs.

 

[chunter33]

Oh, by the way dhookom I misinterpreted the context of your last post. Ignore my earlier reply lol..

 

[cwhite23]

Got it! The extra column was already there, and it works perfectly now. Thanks to everyone for their help.

 

[TheAceMan1]

cwhite23 . . .

All that you want to do depends on your table structure ... of which our Idea is at the least .... [blue]a guess![/blue] If we are to nail this for you .... you need to apprise us of just what that is. Take a screen shot of your relationships and upload it to [link 4shared.com]4Shared[/url] ... (its free!)

I'm tired of reading the thread and [blue]guessing at this![/blue] It would help us all. All your problems & resolution lie here. [blue]Just do it man![/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]