Solved

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

Posted on 2013-06-06
23
499 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
  • 14
  • 8
23 Comments
 
LVL 3

Assisted Solution

by:0Doc
0Doc earned 500 total points
Comment Utility
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
Comment Utility
In the (sub) Form_Current event procedure...

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

Author Comment

by:kearley
Comment Utility
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
 

Author Comment

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

Author Comment

by:kearley
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
If the query works, I'd suggest running it with a docmd in place of the sqlcode.  

Sample:
DoCmd.OpenQuery "UpdateEligiblePts", , acReadOnly
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

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

Expert Comment

by:0Doc
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks! I will give this a try and let you know how it goes.
0
 

Author Comment

by:kearley
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
Try a different event - like On Lost Focus or On Exit.
0
 

Author Comment

by:kearley
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now