How to allow users to provide weekly status updates using prefilled editable subform

[codor]

Dear all,

I do not seem to be able to crack this one for quite a while. I would really appreciate your help.

Context:
My company has an internal improvement team. Each of the users within this team are responsible for a multitude of improvement projects at any given point in time.

Each week, every team member provides a progression update for each of the individual improvement projects awarded to him/her. he/she reports back attributes such as:

- actual (perceived) percentage of the project finsihed
- actual hours of work spent on the project for that week

(these values are - amongst others - used to determine if projects are on track compared to plan values)

I constructed a (unbound) form(see picture below) in which the user selects himself, a year and a weeknumber by means of drop boxes/textboxes (in the header section). these boxes currently don't pose as a problem.

Problem:
The detail section of the same unbound main form should provide a subform listing the open projects connected to the member (see below), together with relevant attributes spent time and %finished as mentioned above. The user provides progress information, afterwards (once a save button is hit, and validation is passed) the information should saved (as in appended) to a table called 'actprogress'.

How do I do this?

(for the sake of clarity) actprogress would have the following structure:
PK[idproject, yr, wk], hoursspent, percfinish, remark

 

[Steve R.]

I assume that your database is split into a front-end and a back-end.

If I understand your question correctly, you are unable to have a filtered result based on the employee name. To accomplish this you will need one table with the employee that is linked to another table that contains the "progress" information along with the employee ID.

Though you have not asked this question, you may want to have a "default" table that provides the employee information when the form is opened. i use that approach.

 

[codor]

Hi Steve, Thanx for your quick reply

To answer your first question I haven't split yet..I'm mocking up at current to find problem spots in the design.

The problem I face is not related to obtaining the actual results based on employee name. In fact I already have the query in place to obtain this information. The problem I face is different.

Normally when a subform is used you a) edit existing records b) add new records one at a time.

What I want to achieve is to create a subform, containing all the most up to date bookings for projects related to the selected user in the main form ((ie. booking with the largest year week combination) this information acts as a template for new bookings connected to the yr and week selected on the main form.

For instance. I am a teammember called Mary Johnson, I select my name in the drop down box (MAR), the year and week box are already prefilled with current year and week. the subform lists all my open projects, next to that it also lists all the previous values I have entered last week (my most recent set of bookings) for timespent and %finsihed, I review these values and determine if they are still correct and modify them to reflect the current situation.

Note: when modifying nothing is changed to the values stored of last weeks progress bookings.

Then when I hit the Save button all progress bookings/records in the subform are saved (appended) to my actual progress table (actprogress) the PK contains IDproject, year and week selected on the main form.

My questions is how to achieve the above described scenario.

Thanx

p.s. I've added the relationships below for extra info.

 

[Steve R.]

The problem I face is not related to obtaining the actual results based on employee name.

What I want to achieve is to create a subform, containing all the most up to date bookings for projects related to the selected user in the main form ((ie. booking with the largest year week combination) this information acts as a template for new bookings connected to the yr and week selected on the main form.

I am confused. The "selected user" would seem to be equivalent to "employee name".

the subform lists all my open projects.

This requires the creation of a query that lists all the open projects associated with the employee. See if that provides you with an initial dataset that you can use for the next step.

If the above worked - establish a DAO recordset based on the open projects. I am suggesting the use of DAO as an approach since I am more familiar with that approach than SQL.

next to that it also lists all the previous values I have entered last week.

Create a date filter. Filter the recordset based on the dates for last week's data (Between date 1 and date 2). What you will get is a list of open projects between two dates. That should allow you to answer your question below.

I review these values and determine if they are still correct and modify them to reflect the current situation.

Build in small increments. Once you get a working version, you can make an SQL statement that accomplishes the whole task.

 

[codor]

Thanx Steve,

Sorry for me being ambigious. employee name and selected user refer to the same thing namely the person that is responsible for the project.

Your approach sounds intuitive. I assume that the created DAO recordset has to be linked to the subform. I haven't done this before but I'll research it. Typically I don't do full coding in Access...I've got my VBA experience from Excel.

Conceptual question: If I edit the recordset (by means of the subform) will the underlying values in the DB tables be changed (ie. vector) or am I working on a copy of these values residing in RAM?

Next, in order to append I assume I will have to write some insert into code to within a loop to transfer the records in the recordset into actprogress table right?

Greetings,

Coen

Steve thanx again



Still once

 

[Steve R.]

Here is a link for DAO that I find very useful.
http://www.classanytime.com/mis333k/sjdaoadorecordsets.html

Also look here: http://allenbrowne.com/tips.html

In the deceleration section.

Public RSTedit As DAO.Recordset

Linking to the form:

Set RSTedit = CurrentDb.OpenRecordset("SELECT * FROM dbo_InspectionTable ORDER BY InspectionDate DESC, RecordID DESC", dbOpenDynaset, dbSeeChanges)
Set Me.Recordset = RSTedit

If the form uses the DAO recordset and the textboxes are bound, the values will change when changed on the form. However, if the textboxes are unbound you will need VBA code to update the underlying tables.

 

[codor]

Hey Steve! Super! was currently fidling around with adodb (found out that I had to activate it in references section, first I'll digg into dao now.

thanx!