Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 523
  • Last Modified:

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

I have a main form that displays subform records in a datasheet based on selection in main form.   A "DefaultPoints" field in the datasheet subform has a value.   I need to set the "ActualPoints" field to the default value for that record in the datasheet subform.  DefaultsPoints is not edible, but ActualPoints is.   After I set the ActualPoints field to the DefaulPoints value, it can be changed by the user.  
I'm struggling with how to do this using a datasheet subform and VBA.  
Thanks for any help you can provide.
0
kearley
Asked:
kearley
  • 14
  • 8
5 Solutions
 
0DocCommented:
At what point do you want to assign Actual Points?  Is it when you go to a new record?  Where ever that is, all you should need is an small on event VB script.  It will be something like this:

ActualPoints.Value=Forms.Mainform.DefaultPoints.Value
0
 
peter57rCommented:
In the (sub) Form_Current event procedure...

If me.newrecord then Me.ActualPoints = Me.DefaultPoints
0
 
kearleyAuthor Commented:
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
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
kearleyAuthor Commented:
Your suggestion only sets default to very first record in datasheet.
0
 
kearleyAuthor Commented:
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.
0
 
0DocCommented:
If I'm understanding the issue, you need an sql Update command to set the ActualEligiblePts to be the Eligible amount for each of the selected the records.  The command would be something like below.  Note: You should definitely try this on a test database.

sqlcode="Update [YourTableName] Set ActualEligiblePts = "& EligiblePts&
" WHERE WorkType= " & Forms!Work!WorkType.Value &" and LineofBusiness= " & Forms!Work!LineofBusiness.Value & " and Product= " & Forms!Work!Product.value & ";"

Docmd.runsql sqlcode
0
 
kearleyAuthor Commented:
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]))"
0
 
kearleyAuthor Commented:
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..
0
 
0DocCommented:
Based on the information I can see, I cannot identify the cause of the key violation error.  I suspect it has something to do with the join between tblQualityReview_Checkpoints and tblCheckpointsToReview.  Here is what I suggest.  Create a select query with the correct joins that shows the records you want and the correct tblCheckpointsToReview.Def_EligiblePts.  

Once you have the select query working, change the Query Type to Update and make the Update To [tblCheckpointsToReview].[Def_EligiblePts].  Get that working and then change the view to SQL view and get the code - or - change the Docmd to run the query directly.
0
 
kearleyAuthor Commented:
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.
0
 
0DocCommented:
If the query works, I'd suggest running it with a docmd in place of the sqlcode.  

Sample:
DoCmd.OpenQuery "UpdateEligiblePts", , acReadOnly
0
 
kearleyAuthor Commented:
Primary Key violations when I open the update query.
0
 
0DocCommented:
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.
0
 
kearleyAuthor Commented:
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
0
 
0DocCommented:
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.
0
 
kearleyAuthor Commented:
Thanks! I will give this a try and let you know how it goes.
0
 
kearleyAuthor Commented:
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
0
 
kearleyAuthor Commented:
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!
0
 
0DocCommented:
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.
0
 
kearleyAuthor Commented:
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
0
 
kearleyAuthor Commented:
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?
0
 
0DocCommented:
Try a different event - like On Lost Focus or On Exit.
0
 
kearleyAuthor Commented:
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.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 14
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now