Solved

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

Posted on 2011-09-20
14
656 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

738 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