Solved

How to refresh a field on a main form from afterupdate event of a subform control

Posted on 2011-09-20
14
579 Views
Last Modified: 2012-05-12
I have a main form (frmEmployee_Audits) and a subform (frmQuality_Review_Subform).  I have some code.  I have tried it two different ways.  One way the code is working (see Code 1) in the Code section, but the screen flickers while it is running the code even though I have Echo set to false, and the other code (see Code 2) in the Code section, doesn't change the values at all.

First of all the subform (frmQuality_Review_Subform) is populated when a user makes a selection from the combo box (cboAuditType) on the main form (frmEmployee_Audits).  One of the fields that is populated is (cboAssoc).  The problem that I am running into is if for some reason the user needs to either come back later or during the initial creation of the record and change the field (cboAssoc) on the subform, I need the other subform controls (Possible_Score) and (Audit_Score) to be refreshed with the appropriate initial values.

On the subform (frmQuality_Review_Subform) if a user changes the initial value that is populated from the main form combo box (cboAuditType) to something else, and when that happens I need the control (cboAuditType) on the main form to requery so that the fields on the subform changes to what they should be, but have the cursor remain on the subform control (cboAssoc).

These are the fields on the subform:

cboAssoc    cboOpers   cboImpact   Possible_Score    Audit_Score

The first three field's choices are Y, N or N/A.  Depending on what is chosen in the first field (cboAssoc) the Possible_Score and Audit_Score are populated.

Depending on what the user chooses for the cboAuditType field on the main form the subform will popluate with different values.  For example, if a user chooses the Audit Type "Reinstatements", then the first line of the subform automatically populates with the following:

  cboAssoc    cboOpers   cboImpact   Possible_Score    Audit_Score
       N               N               N                    3                     3

Then if a user changes the cboAssoc value to Y then the Possible_Score stays as "3" and the Audit_Score changes to "0"; but if the user chooses N/A then both the Possible_Score and Audit_Score change to "0";  but then if a user decides then or later that they need to change the cboAssoc back to either an "N" or a "Y", then I need to have the control on the main form (cboAuditType) refresh so that changes the Possible_Score and Audit_Score from "0" back to what it should be based off of the original amounts from the combo box choice population.

Sorry, I hope this isn't too confusing.  Like I mentioned in the beginning of my post, the one code (Code 1 - see below) is doing what I want it to do, I just don't like the flickering, and the cboAuditType control on the main form is set to drop down each time you enter the field, so that is part what is seen during the running of the code and the flickering.

Thanks,
gdunn59
Code 1 (doing what I want it to do, but flickering):

    DoCmd.Echo False
    If cboAssoc = "N" Then
        Me.Parent!cboAuditType.SetFocus
        Me.Parent!cboAuditType.Requery
        Parent![frmQuality_Review_Subform].SetFocus
        DoCmd.GoToControl "cboAssoc"
        Audit_Score = Possible_Score
        'cboOpers = "Y"
    ElseIf cboAssoc = "Y" Then
        Me.Parent!cboAuditType.SetFocus
        Me.Parent!cboAuditType.Requery
        Parent![frmQuality_Review_Subform].SetFocus
        DoCmd.GoToControl "cboAssoc"
        cboAssoc = "Y"
        cboOpers = "N"
        Audit_Score = "0"
    ElseIf cboAssoc = "N/A" Then
        Audit_Score = "0"
        Possible_Score = "0"
    Else
        MsgBox "Option Not Available.  Please choose another option."
    End If    
    DoCmd.Echo True




Code 2 (not doing what I want it to do:

DoCmd.Echo False
    If cboAssoc = "N" Then
        Forms!frmEmployee_audits!cboAuditType.Requery
        Audit_Score = Possible_Score
        'cboOpers = "Y"
    ElseIf cboAssoc = "Y" Then
        Forms!frmEmployee_audits!cboAuditType.Requery
        cboAssoc = "Y"
        cboOpers = "N"
        Audit_Score = "0"
    ElseIf cboAssoc = "N/A" Then
        Audit_Score = "0"
        Possible_Score = "0"
    Else
        MsgBox "Option Not Available.  Please choose another option."
    End If    
    DoCmd.Echo True

Open in new window

0
Comment
Question by:gdunn59
  • 7
  • 5
  • 2
14 Comments
 
LVL 14

Expert Comment

by:Bill Ross
ID: 36573553
Hi,

I would suggest that you write the data to the table then requery the main (and sub form if needed) rather than changing the screen data and allowing the screen to then write to the table.

To do this -
  save the form data so there are no pending updates/locked records
  open a recordset
  change all the necessary values
  close the recordset
  requery the main form

Regards,

Bill  
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 36573889
If the combobox dropdown occurs when it receives focus, try doing the requery first, then set focus on the combobox.

        Me.Parent!cboAuditType.Requery
        Me.Parent!cboAuditType.SetFocus

0
 

Author Comment

by:gdunn59
ID: 36574513
puppydogbuddy:

I tried what you suggested, but for some reason unless it sets the focus first, the requery doesn't work, nothing happens.

Thanks,

gdunn59
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 36574723
Hmm.I don't recall having to set focus to do a requery.  try temporarly eliminating the auto-dropdown and verify that it is the cause of your flickering.

If the dropdown is the cause of the flickering, Try what I suggested in my previous comment using a fully qualified reference instead of Me!Parent.......
Forms!YourMainForm!!cboAuditType.Requery

 
       
0
 

Author Comment

by:gdunn59
ID: 36576501
puppydogbuddy:

I did what you suggested in your last posting, it still flickers.  

I removed the dropdown code from the after update of the cboAuditType control and moved it to the On Enter, this at least eliminates the dropdown from expanding, so it still flickers, but not quite as mu;ch.  I actually removed the line of code with the Requery and just have it doing a SetFocus on the cboAuditType field on the Main Form.  This works, so I think that eliminated some of the flicker as well.

Not sure if you or anyone else has any further suggestions on this.  I'm all ears!

Thanks,

gdunn59
0
 

Author Comment

by:gdunn59
ID: 36576530
One other note, the Requery just will not work without me doing the SetFocus on that field first.  Don't know why.

Thanks,
gdunn59
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 36578476
Since you are just trying to synchronize the main form combobox to the record displayed in the subform, try doing a refresh instead of setfocus and requery that you originally.  The refresh, unlike the requery, should not reset the pointer to the record that was current in the subform, which should reduce or eliminate the flickering.

Me.Parent!cboAuditType.Refresh

If this doesn't do the trick, let me know and I will research this in more detail.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:gdunn59
ID: 36583568
puppydogbuddy:

I had already tried what you suggested in your last posting and I get the following error:

  Run-time error '438'
  Object doesn't support this property or method

I am going to upload my database for you to look at.  I just noticed that the other issue I am having is if I just start typing N when in the cboAssoc control because I want to choose the option "N/A" it automatically wants to choose "N" and goes on with the remainder of the code.  It shouldn't be doing this either.

I'm really not sure what else to do with this, but I really need to get it working.

Once I upload my database, if you have another way to accomplish what I am trying to do, please feel free to go that direction and let me know what you did.

Thanks,
gdunn59
0
 

Author Comment

by:gdunn59
ID: 36583809
Here is the database.

Actually the part that I posted in my last posting about choosing N/A, it appears to be okay.

The problem that I am having with the cboAssoc (Y,N,N/A) control is that when going to the next record in the subform and changing the cboAssoc field to either a "Y" or a "N" (N/A is fine), it changes it but then the cursor jumps back to the first record's cboAssoc control on the subform, and changes that record that it was on before back to the original value.  It shouldn't be doing that.

Thanks,

gdunn59 Audit-Database--for-EE---working.mdb
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 36585163
I couldn't open your database file due to incompatible format. That means that your version of Access is probably 2007...... which I don't use.  If you could upload a 2003 version of your database with the db format in year 2000), i could work with it.

Irregardless, try the following to synchcronize your main form combobox with the current record of the subform:

I assume the main form combobox is linked to the subform records on a unique key value (e.g. Master and Child  link).  So, in lieu of using the AfterUpdate event and the code contained there-in, it should be much simpler to use the Current event of the subform to change the selection in the combobox to match the current record of the subform as shown below:

Private Sub Form_Current()                   'current event of the subform
Me.Parent!cboAuditType = Me.ID           'set main form combo to key value for
                                                                 ' current record of the subform
End Sub


Let me know how it goes.

0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 36586884
Hi gdunn59,

Looks like you and puppydogdaddy are making good progress but did you try my original suggestion above?  In reviewing the comments I think that might be a good solution for you.

Bill
0
 

Author Comment

by:gdunn59
ID: 36588096
BillDenver,

I didn't quite understand what it is that I needed to do.

Can you enlighten me.

Thanks,

gdunn59
0
 

Author Comment

by:gdunn59
ID: 36588459
puppydogbuddy:

Here is the MS Access 2000 Version of the database that you requested.

Also, I tried doing the code in the Current Event of the subform and I am getting an error saying that "you can't assign a value to this object.

Thanks,

gdunn59 Audit-Database--for-EE---working.mdb
0
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 500 total points
ID: 36592608
gdunn59,
Just took a quick look at your database and see why you are having problems.  You are using a selections from bound (have a control source) comboboxes to find a record on the bound column, and that is why Access is saying it can't assign a value......it thinks you are trying to overwrite the key value.  Unbound combo boxes are generally used for lookups.....and unless you have a specific reason for storing info about each lookup, I suggest that you use unbound comboboxes. See this link for a detailed explanation:   http://www.dbforums.com/microsoft-access/1656486-find-record-combo-box-wizard-quirk.html

If you use an unbound combo on your main form to find and display certain records in your subform, the synchronizing the combo with the current record displayed on your subform is easy:

Private Sub Form_Current()                     'current event of the subform
Me.Parent!cboAuditType = Me.Audit_Type  
                                                                 ' current record of the subform
End Sub

Let me know if you need more help.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

759 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

22 Experts available now in Live!

Get 1:1 Help Now