Issue adding data to a subform

[JRT2006]

Its me again, if anybody has the time, could you look over the following paragraphs and attached database and help me find a solution.

I suppose a field could be added to tblProspectSkill, and the command button code could include that field in the SQL, but I keep thinking there should be a better way than copying the values. However, my attempts to link have led either to multiplying the record count by 9 or a recordset that cannot be edited. I'm probably missing something obvious, but I can't see it from here. Perhaps somebody else will take a look.

To anybody taking a look, the idea is that a position in football will be assigned nine key skills that are important to the team that is thinking about acquiring that player. For a position (quarterback, for instance) there will be a master list of nine skills, along with an importance rating (1, 3, or 5, which is essentially high, medium, or low) for each skill.
Then the prospect (the player being considered) is assigned a position such as quarterback. A command button creates nine records in a ProspectSkills table that is related to the Prospect table. The nine records that are added are the nine skills described in the previous paragraph. Each skill in this listing is assigned a grade, which has a numerical value as well as a letter grade. The numerical value is then multiplied by the skills importance rating (1, 3, 5) and the results are then averaged for a final value.
The difficulty I described in the first paragraph is that when I try to link to the importance rating I end up with 81 records (9 skills * 9, apparently), but I can't sort out how to change that in a writable query.

I've got a few trying to assist me with this and here is where everyone appears to get stuck.

 

[plog]

You didn't really explicitly state the issue you are having. You posted in the forms section, but mention a writable query. So, I'm guessing this boils down to you not being able to edit date through a form. Am I close?

Even if I am not, I see that you have forms whose record sources are queries. You should really avoid that. If you want to manipulate data directly through a form, you should base that form on a table. This avoids any issues you have about writable queries and makes sure there's no unintended data going into a table you don't expect it to.

Without a good grasp of your specific issue, that's my advice--make the forms with which you want to directly interact with data on just the table you wish to interact with.

 

[JRT2006]

The paragraphs were what I was told by somebody trying to help me.
The issue is trying to get the skill value, as selected in frmposition when you select the skills, to appear on fsubProspectSkills, because it is part of a formula to determine final weighted average, without having to create a field in tblProspectSkills.

For instance, assigning a prospect the QB position in frmProspectDetails and clicking the 'add skills listing' button fills the subform, fsubprospectskills, with the skills assigned to the QB position which youve determined in frmPosition. Without adding a SkillValueID field into tblProspectSkills, we are trying to get fsubProspectSkills to also show the SkillValue you selected for the skills in frmPosition.

example picture:

We are trying to get the Skill values to appear on the form with the skills when the 'add skills listing' button is clicked

 

[plog]

We are trying to get the Skill values to appear on the form with the skills when the 'add skills listing' button is clicked

What you would do is set the source of the Skill Value control to a Dlookup (http://www.techonthenet.com/access/functions/domain/dlookup.php) that looks up the value based on what the user input into the SKill drop down.

 

[JRT2006]

Okay, I'll read into that link and report back.

Thanks for the info

 

[JRT2006]

Okay, understand the concept, not quite sure how to apply it considering it would need to lookup two fields; one being the position ID on the Parent Form and the second being the SkillID on the sub form.

Considering that a skills importance is not absolute, for instance blocking may be of higher importance for the tackle position than the tight end position, the lookup would need to find the skill matching the position and return its value.

Hopefully I explained that well enough to understand

 

[plog]

I gotcha, let me change my answer a little bit. I would base that subform off of the appropriate table so the data goes in correctly. I would also still use a Dlookup for the SKill Values, however, I would make a query to point the Dlookup to. That way, you just need the primarky key ID of the row you are on, to use in the Dlookup. The upside of that query is you can also do the Value*Skill calculation in there as well and use a dlookup for that value in the next control.

 

[JRT2006]

thanks, but i give. i assume to accomplish this i'll have to make some major changes and im not willing to go that far for a database i just consider a "project" or "hobby"

 

[JRT2006]

using the code in the button and adding a few fields to a table, I was able to do this correctly.

the next questions is something Ive read a lot about and it frowned upon. How do I get the average of these numbers on a form to save in a query or table so a report can be generated with the prospects general information and final average?

 

[plog]

You really need to create that query I mentioned. The one that basically mimics your subform but is unwriteable. It would be a good basis for any reports you want to generate and could be used to retrieve the calculated values on that subform.

You would be able to use Dlookups to get the data for the individual rows on the subform as well as a DAvg (http://www.techonthenet.com/access/functions/domain/davg.php) for the bottom total.

 

[JRT2006]

Alright, I will start working on it and see how it treats me

Thanks