Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-09-20
14
Medium Priority
?
718 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
[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
  • 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
 
LVL 1

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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
 
LVL 1

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
 
LVL 1

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
 
LVL 1

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
 
LVL 1

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
 
LVL 1

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
 
LVL 1

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 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

722 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