?
Solved

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

Posted on 2013-06-06
23
Medium Priority
?
514 Views
Last Modified: 2013-06-21
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
Comment
Question by:kearley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 8
23 Comments
 
LVL 3

Assisted Solution

by:0Doc
0Doc earned 1500 total points
ID: 39227412
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
 
LVL 77

Expert Comment

by:peter57r
ID: 39227413
In the (sub) Form_Current event procedure...

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

Author Comment

by:kearley
ID: 39228743
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:kearley
ID: 39229225
Your suggestion only sets default to very first record in datasheet.
0
 

Author Comment

by:kearley
ID: 39229320
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
 
LVL 3

Accepted Solution

by:
0Doc earned 1500 total points
ID: 39229352
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
 

Author Comment

by:kearley
ID: 39229494
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
 

Author Comment

by:kearley
ID: 39229500
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
 
LVL 3

Assisted Solution

by:0Doc
0Doc earned 1500 total points
ID: 39229551
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
 

Author Comment

by:kearley
ID: 39229617
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
 
LVL 3

Assisted Solution

by:0Doc
0Doc earned 1500 total points
ID: 39229687
If the query works, I'd suggest running it with a docmd in place of the sqlcode.  

Sample:
DoCmd.OpenQuery "UpdateEligiblePts", , acReadOnly
0
 

Author Comment

by:kearley
ID: 39229738
Primary Key violations when I open the update query.
0
 
LVL 3

Expert Comment

by:0Doc
ID: 39229748
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
 

Author Comment

by:kearley
ID: 39229914
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
 
LVL 3

Expert Comment

by:0Doc
ID: 39230530
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
 

Author Comment

by:kearley
ID: 39237991
Thanks! I will give this a try and let you know how it goes.
0
 

Author Comment

by:kearley
ID: 39238526
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
 

Author Comment

by:kearley
ID: 39238842
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
 
LVL 3

Expert Comment

by:0Doc
ID: 39239021
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
 

Author Comment

by:kearley
ID: 39253572
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
 

Author Comment

by:kearley
ID: 39259832
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
 
LVL 3

Assisted Solution

by:0Doc
0Doc earned 1500 total points
ID: 39259952
Try a different event - like On Lost Focus or On Exit.
0
 

Author Comment

by:kearley
ID: 39266965
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question