Full Version: Can't Figure Out How To Build This Form
UtterAccess Forums > Microsoft® Access > Access Forms
EdNerd
Been goin' 'round with this thing for a while, and I'm stumped. I've asked several questions about different aspects, and y'all give me great solutions - and then there's that one more thing that just doesn't work.

We're doing reviews on people. I have a table of criteria for each employment step level. I can create the queries that give me the review criteria for each step - anywhere from 5 to 12 criteria. I got the subform for the criteria as a datasheet all figured out to show what I need adequately. It looked like it was finally gonna be finished!

The last two columns of the subform were for the reviewer to enter the current score and any comments for this review. So I go to test it -- "This recordset is not editable." Saw Allen Browne's explanation - and sure enough, my query for that subform meets three or four of his "if you have this, it won't work!" pointers.

It looks like I may have to go back to my original plan: a series of unbound controls that I fill in from the recordset. It just looked real clunky and like there should be a better way to do this.

So I guess that's what I'm asking - *is* there a better way to do this?

Ed

PS - in case it would help, here's the SQL for the subform query. The fields for ThisScore and Comments could be left off - those were to enter the data for the current review.
SQL
SELECT qryReviewScoresWithDataSets.Order, qryReviewScoresWithDataSets.Criteria, qryReviewScoresWithDataSets.MaxPoints, qryReviewScoresWithDataSets.Instrc, Max(IIf([DataSet]=2,[CritScore],"")) AS OldestScore, Max(IIf([DataSet]=1,[CritScore],"")) AS LatestScore, IIf(Len([Order])>0," ","") AS ThisScore, IIf(Len([Order])>0," ","") AS Comments
FROM qryReviewScoresWithDataSets GROUP BY qryReviewScoresWithDataSets.Order, qryReviewScoresWithDataSets.Criteria, qryReviewScoresWithDataSets.MaxPoints, qryReviewScoresWithDataSets.Instrc
ORDER BY qryReviewScoresWithDataSets.Order;
doctor9
Ed,

The first step we should take is to make sure you have the correct table structure to support this sort of data.

> We're doing reviews on people. I have a table of criteria for each employment step level.

Okay, so you should have these tables:

People (Employees)
Employment Step Levels
Criteria

These tables store the basic data, but nothing about each person's actual review data. You need separate tables for that. (I say plural tables because one person can likely be reviewed more than once.) You haven't described what sort of data that is, but it should be along these lines:

tblEmployeeReviews
EmployeeReviewID [Autonumber, Primary Key]
dteReviewDate
lngEmployeeID [Foreign Key to the primary key field of your Employees table]

tblReviewResults
ReviewResultID [Autonumber, Primary Key]
lngEmployeeReviewID [Foreign Key to tblEmployeeReviews.EmployeeReviewID]
lngCriteriaID [Foreign Key to the primary key field of your Criteria table]
strResult (The rating the employee received for this particular criteria)

Now, assuming you have this table setup, you will need to populate the tblReviewResults table with some partially-filled-in records when a new review is being entered for an employee. Specifically, the lngEmployeeReviewID field should be the primary key value for the current Employee Review, and the lngCriteriaID values need to be the values of all of the criteria for this employee's Employment Step Level, but the strResult field is left blank. So, if there are seven criteria for the current employee's employment step level, you'd insert seven new records in the tblReviewResults table.

Once these records have been inserted, you can requery your continuous subform which is based on the tblReviewResults table (the main form would be based on the tblEmployeeReviews table). Now the continuous subform lists all seven criteria for the current employee, but the strResult field is blank.

You may have noticed that this table structure allows for the employee to be reviewed at different employment step levels. It also allows for the list of criteria for a step level to change over time.

Hope this helps,

Dennis
EdNerd
Here's my tables:

-- tblEmplData holds current employee data, including employee number and current step.

-- tblPrgSteps holds the progression steps, a Yes/No "IsCurrent" filed, and "StepID" as autonumber PK.

-- tblStepCrit is set up with:
** StepCritID (autonumber PK)
** Current (Yes/No)
** StepID (fk to tblProgSteps)
** Step (identifies which progression step this belongs to)
** Order (sets the order the criteria are shown in)
** RevNum
** dtRevised
** Reason (these three fields let me keep a history of revisions to the criteria)
** Criteria (text of the criteria)
** MaxPoints (set as Text; most often a single number, but in a few cases it's something like "-20 to +10")
** Intrc (any instructions for completing the review on this criterion)

-- tblStepRevs
** RevID (autonumber PK)
** RevDate (date of review)
** EmplNum (employee reviewed)
** SuperNum (supervisor doing review)
** StepNum (fk to Step in tblStepCrit)

-- tblStepRevScores
** ScoreID (autonumber PK)
** RevID (fk to RevID in tblStepRevs)
** StepCritID (fk to StepCritID in tblStepCrit)
** CritScore
** CritComment

If I read your response correctly, the data for each new review gets appended to the table in two steps:
-- first, when the review is initiated, recording everything except the results
-- second, to insert the results
And in between these steps, the subform requeried.

Because the query gets the last two previous scores and displays those as well, there were some calculations and grouping in my SQL. According to Allen Browne's article, these are reasons why the recordsest was unable to be edited. Will your method and using the continuous subform overcome this?

Ed
doctor9
Ed,

This is a data entry form, so you would use VBA to append the incomplete records to the table, and then requery the form. Then, the USER enters the scores/results for the review into the control bound to the score on the continuous subform. There is one record for each criteria that is listed for the employees current employment step level.

I have no idea which Allen Browne article you're referring to, so it's hard to understand the rest of your post.

Hope this helps,

Dennis
EdNerd
QUOTE
This is a data entry form, so you would use VBA to append the incomplete records to the table, and then requery the form. Then, the USER enters the scores/results for the review into the control bound to the score on the continuous subform. There is one record for each criteria that is listed for the employees current employment step level.

Understood the VBA to append. Just trying to make sure I understood the sequence.

QUOTE
I have no idea which Allen Browne article you're referring to, so it's hard to understand the rest of your post.

http://allenbrowne.com/ser-61.html

The question I had was: due to the nature of the query (according to the article), the datasheet subform could not be edited by a user trying to enter the current scores. Is this hindrance overcome by using a continuous subform? I've never used one before, but I did insert one as I was trying to make this work. It appears just like a series of text box controls, one for each field, and one "row" of controls for each record - yes? But as they will all still be bound to this query, will I be able to edit this subform where I could not edit the datasheet? If not, I can just go with a setup of unbound text box controls and write my values into each one from the recordset using VBA, and then collect the entries and write them to the table records in VBA. I was trying to use a subform in hopes that it would be easier to get the values in and out, and also "neater" in appearance (not having 15 rows of text controls when I only have six criteria).

Ed
doctor9
Ed,

The continuous subform should just be based on the table where you store the actual scores; no query is required in the setup I suggested. The continuous subform should automatically filter to the review being viewed in the main form.

I would NOT recommend using datasheet view, as the user should only be able to edit the score data. They should not be able to alter the criteria or the review ID, for example.

Dennis
EdNerd
QUOTE
The continuous subform should just be based on the table where you store the actual scores; no query is required in the setup I suggested.

Okay - that gets around the problem I encountered. Makes sense.

QUOTE
The continuous subform should automatically filter to the review being viewed in the main form.

Not sure how to do this. Especially to get the latest two previous scores - that's where I ran into expressions and grouping in my previous query.

QUOTE
They should not be able to alter the criteria or the review ID

I wondered about that - making the datasheet editable would have exposed everything else, too.

The subform should present my data like so:
Criteria MaxPoints Instructions 2ndPrevScore LastPrevScore ThisScore Comments
(ThisScore and Comments would be blank until the reviewer fills them in.)

It sounds easy. But I'm quite lost.
Ed
doctor9
Ed,

The Parent/Child properties of the subform control determine the fields that link the main form to the subform. If you've created the relationships beforehand, when you add the subform to the main form in Design View, Access will try to use the linked fields between the tables that drive the two forms.

Here's an example:

You want to track your bills. You create a table of Bills and a table of Bill Line Items. The Bill Line Items table includes a "lngBillID" Long Integer field and the Bills table has a "BillID" Autonumber field. You create a link between these two fields in the Relationships view. Then, you create a single form based on the Bills table and a continuous form based on the Bill Line Items table. When you add the Bill Line Items form to the Bills form in Design View, Access will use BillID as the Parent and lngBillID as the Child. At this point, when you navigate to a bill on the main form, the subform will display line items for that bill only.

For your subform, you should use textboxes and comboboxes. You can set the .Enabled property to TRUE for values the user can edit, and FALSE for values the user should only view. You can use different formatting techniques to make it clear to the user what they should and should not try to edit.

You might want to look at the Northwinds demo database for ideas on how to implement these concepts.

Hope this helps,

Dennis
EdNerd
Thank you, Dennis. I'll chunk on this and post back with problems or questions.
(Long weekend coming up, and I'm not sure when I'll get back to this.)

Ed
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.