Datasheet Subform needs certain fields to default to another field's value

In the (sub) Form_Current event procedure...

If me.newrecord then Me.ActualPoints = Me.DefaultPoints

Here's how it works - I open my main form and select values in 3 combo boxes.  Based on the values selected, the subform displays a datasheet with a list of items and their associated point values.  The Eligible point values need to be static but then need to set the ActualEligiblePts field to that value as a default so they can be changed if need be.  So all records that display in the subform need to have the ActualEligiblePts field set to the corresponding EligiblePts value for that record.  Does that make sense?  I might be going about this entire wrong. I will try your 2nd suggestion and let you know! Thanks.

See attached.
SampleForm.docx

Your suggestion only sets default to very first record in datasheet.

I decided to try a command button on my main form that would run an Update SQL statement to update the "ActualEligiblePts" value to the "EligiblePts".   The SQL runs and tells me it's about to update the correct amount of records. However I get an error about a key violation and the update fails on all records.   The update query has to do a left outer join to join the two tables together since the ActualEligiblePts and EligiblePts are in two different tables.  I'm stuck.  Thanks for any help you can provide.  I can attach the entire db if that helps.

Yep! I have that type of VBA code but I am getting a key violation so it will not update any of the records.  It's strange because I can type in the ActualEligiblePts manually with no key violations.  
Here is my update SQL:  DoCmd.RunSQL "UPDATE tblCheckpointsToReview LEFT JOIN tblQualityReview_Checkpoints ON tblCheckpointsToReview.CheckpointsReviewID = tblQualityReview_Checkpoints.CheckpointsReviewID SET tblQualityReview_Checkpoints.ActualEligiblePts = [tblCheckpointsToReview].[Def_EligiblePts]" & _
"WHERE (((tblCheckpointsToReview.WorkTypeID)=Me!Parent.[WorkTypeID]) AND ((tblCheckpointsToReview.ProductTypeID)=Me!Parent.[ProductID]) AND ((tblCheckpointsToReview.LineOfBusinessID)=Me!Parent.[LineOfBUsinessID]))"

Now I'm considering creating VBA to loop through a recordset based on a query.  I have no idea how to do this, but I'm going to give it a whirl..

The select query works perfectly - it's the same query as in the subform source.   I copied the SQL to a different query and changed to update.  That's how I got the SQL for the update.  Something's odd.  Trying to figure it out.  Thanks.

Primary Key violations when I open the update query.

Can you attach a sample database that has the error?  If so, I can probably debug it.  The other option is to go the VB code routing using a dataset.

Database is attached.  I believe the PK issue is because of the way my relationships are set up.  
the main table - tblQualityReview_Main has a related table tblQualityReview_Checkpoints which contains the "earned points" for the eliglble checkpoints.  So when the 3 combo box values are selected, I need to display the list of eligible checkpoints - these are the checkpoints in the table tblCheckpointsToReview.    When I had the cascade update set between the main table and the tblQualityReview_Checkpoints table, I was unable to update the table with the actual points.  So I had to remove the cascade update.
Now because of the cascade update removed, my PK of QualityMainID in the subform source (specifically table tblQualityReview_Checkpoints) is being set to 0 instead of to the autonumber value from the main table (by way of the main form).  Here are the issues in a nutshell:
1)  I need a way to auto update the QualityMainID in the subform.
2) I need to display all available checkpoints for the 3 combo box value (the main form/subform linking fields).
3) For the Def_EligiblePts values for each of the checkpoints listed in the subform, I need to be able to default the ActualEligiblePts to the Def_EligiblePts so they can be changed and saved as changed values with the Quality Review being performed.  The changed default values (in the ActualEligiblePts field) will be used to compare against the Earned points for that particular review which will be used in reports.

This is a nightmare.  I am willing to increase the points for your help.  Thank you.
TEST.accdb

I think I have a solution for you, or at least something to try.  First, temporarily remove the relationships from the quality review table and then update the existing records with the correct default points.  When all the records are up to date, then add the relationships back on.  Then, all you have to worry about is new records.  On new record insert, set the Actual Eligible points as the record is created.  In theory I believe that will work.  Be sure to no add any data when the relationships are temporarily removed because data integrity will not be enforced.

Thanks! I will give this a try and let you know how it goes.

Here's the issue.   The table tblCheckpointsToReview contains the Default points for each Checkpoint with CheckpointID as the PK.   It's the field ActualEligiblepts in the tblQualityReview_Checkpoints that needs to be set to the Default value of the Checkpoint from the tblCheckpointsToReview table.  I can't set the ActualEligibePts to the Default value since I need the PK of QualityMainID to contain a Value. It doesn't have a value until an actual Quality Review is done.  This PK comes from the source of the main form (tblQualityREview_Main).  This is very tricky.   Even with the relationships removed, it won't work due to not having a value in one of the PKs.  I will keep thinking on it.  

I'm attaching a screen shot of my relationships.
QualityRelationships.jpg

Yeah! I figured out how to update the subform datasheet records with the default values by using a command button.  I'm using the code below:  

With Me.[frmCheckpointsToReview subform_Main].Form.RecordsetClone
    If .RecordCount Then .MoveFirst
    Do Until .EOF
        .Edit
        'vDefEligiblePts = Me.[frmCheckpointsToReview subform_Main].Form!Def_EligiblePts
        !QualityMainID = Forms!frmQualityReview_Main!QualityId
        !ActualEligiblePts = !Def_EligiblePts
        .Update
        .MoveNext
    Loop
   
End With

So now that I have this working, I want to remove the command button and have this code run when my main form is opened only if the Main form is on a new record.  If the record is existing, I want the existing subform values to display (showing the actualeligiblepts and earnedpts, etc.)

Can you help with where I can put this code?  I tried in the OnLoad of the subform but it doesn't work there.

thanks!

I believe you need to run set the eligible points it at a later time so that the detail lines have been filled in.  I'm thinking the best thing to do run the code in the checkpoints subform after the for each checkpoint is entered.  At that point then set the defeligiblepts - for just that one record.  I could be off base because I haven't been able to actually create a new record in the database you uploaded.  The employee table is missing and there is a missing function.  But, hopefully, I am still steering you in the right direction.

I've been off work for a few days.  I'm still working on how to get the default values to be assigned without clicking a command button.  I've attached a newer version of my test database.
TEST.accdb

I am now able to run the code to set the default values in the subform by using an AfterUpdate event on a combo box in the main form.  the issue is that it doesn't always work. sometimes I need to make my combo box selection twice.  Any ideas?

For some reason it is working again.  I didn't have to use On Lost focus or On Exit. Thanks though.  I have another issue but I will open a new item.  Thanks for getting me to think things through and come up with solutions. YOur ideas really helped.