Page 1 of 3 123 LastLast
Results 1 to 15 of 40
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Requery Sub form

    1. To the left on the image there is a text field that returns "Yes" or "No".


    2. If "Yes" Sub form 2 visibility must be true. If "No" Sub form visibility must be false.
    3. I have done these many times successfully, but not here. What am I missing?
    4. The code I placed under the event "Form_current" is
    If Me.txtSubAccountA = "Yes" Then
    Me.f02JournalsSubAccX.Visible = True
    Else
    If Me.txtSubAccountA = "No" Then
    Me.f02JournalsSubAccX.Visible = False
    End If
    End If
    5. It may be a requery issue because if I exit the form and return, the visibility work as it should.
    6. I tested various requery commands but probably just do it wrong here.
    7. While capturing, if the Yes change to a No, the Sub form2 must disappear.
    Click image for larger version. 

Name:	230309a.png 
Views:	41 
Size:	127.1 KB 
ID:	49852

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If this f02JournalsSubAccX is the name of the subform, change it to the name of the subform control.
    In the current event you could probably get away with one line: Me.subformControlName.Visible = Me.txtSubAccountA = "Yes"

    You would need to code in the textbox AfterUpdate event if you want to capture immediate changes to Yes or No.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Not working yet.
    Forms: Mainform f02Journals, Subform1 f02JournalsSub, Subform2 f02JournalsSubAcc.
    Subform 2 was named with an added X, following your advice the formname and controlname of all three are the same now.
    The field name for the text field is txtSubAccountA, I checked the spelling a couple of times.
    The result is still the same, if I exit and return the result is as wanted. I want the Subform2 to disappear the moment the text field returns a "No".
    Click image for larger version. 

Name:	230309b.png 
Views:	38 
Size:	9.6 KB 
ID:	49853

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    following your advice the formname and controlname of all three are the same now.
    Me? I would never name a control the same as any other object. Not even the field it is bound to. Don't know where you got that idea from.
    You still haven't stated whether you're referring to the subform control or the subform. You hide the subform control, not the form.

    Did you step through the code to validate that values are as expected?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Sorry if my words caused a misunderstanding. f02Journals is named f02Journals, f02JournalsSub is named f02JournalsSub and f02JournalsSubAcc is named f02JournalsSubAcc. There was no idea that you want me to name a control the same as another object. If I still miss it, I apologize.
    Click image for larger version. 

Name:	230309c.png 
Views:	35 
Size:	14.6 KB 
ID:	49854Click image for larger version. 

Name:	230309e.png 
Views:	35 
Size:	35.1 KB 
ID:	49856
    Attached Thumbnails Attached Thumbnails 230309d.png  

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    If you want the subform 2 to "disappear" immediately after you change the SubAccount from "Yes" to "No" you need to add that line of code to the AfterUpdate event of the SubAccount control, the Current event will not trigger until to move to another record.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    you need to add that line of code to the AfterUpdate event of the SubAccount control,
    Already stated but perhaps missed.
    You would need to code in the textbox AfterUpdate event if you want to capture immediate changes to Yes or No.
    So the subform control is named f02JournalsSubAcc ? Then Me.f02JournalsSubAcc.Visible = Me.txtSubAccountA = "Yes" should work. If not, maybe time to post a db copy as you have before.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry, I did miss it !
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Micron, after your first reply here (#2), I tested the code on AfterUpdate on the main form. I did not understand I should do it on the sub form. The text field on the main form (txtSubAccountA) returns the answer to an expression, I assumed that if the return change from "Yes" to "No" it is an AfterUpdate. I will test the latest advice on the test version I am working on. If it still doesnt work I will post it. Thank you.

  10. #10
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I will prepare a partial db and post it. It will take a while to prepare.

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    The text field on the main form (txtSubAccountA) returns the answer to an expression

    If the control is a calculated field the value change triggered somewhere else will not trigger the control's AfterUpdate. Can you show us the control source of that textbox (the expression)? The code to show\hide the subform needs to be in the AfterUpdate of whatever control's value you manipulate to make the SubAccount expression show "Yes" or "No".

    Cheers,

    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Vlad, no need to apologize as I was just trying to drive home the point to Perfac, whom I can't help think doesn't always focus on what's being written. I know that's a problem for me sometimes. Sometimes the details just start to form alphabet soup in my brain.
    I tested the code on AfterUpdate on the main form
    Not the form and not the subform either.
    You would need to code in the textbox AfterUpdate
    I based my answer on someone changing the control value manually. I take it that it is being done via code? Or maybe it's a calculated control and not bound?
    Anyway, will wait to see a db if you remain stuck.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Afrikaans is my main language, but I cannot use that as an excuse. More than 50% words I spoke in my life of 60 years were English. I put in effort to understand correct, but some moments I am possibly a little stupid, or concentration is low. But the show must go on. Attached find the db. With respect to your time, you only need to open form f02Journals. This test version shows an error which my app doesn't, but I suggest click past it, it is not part of the issue here.
    1. Go to the first line on the sub form. Click "Overhead expenses. I set the combo box to return only two options but select Overhead expenses.
    2. It will bring up another little subform with a long list of overhead expense types.
    3. If one of the next three (Bank Charges, Groceries, Interest Paid) is selected the text box will return "Yes". Otherwise "No".
    4. The db I posted is just a fraction of my app. If it needs any object I left out, I will quickly add it and post again.
    5. This issue is only about Subform2 must display when one of the three in 3 above is selected, when "No" Subform2 must be gone.

    Thank you.
    Attached Files Attached Files

  14. #14
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    First, no offense intended re language because I know anyone in your position understands English way better than I understand any other language and I respect that.

    As for errors when opening, those are just about missing references (fixed enough in your sample to allow it to open without issues).
    Unfortunately I don't understand the whole point of what you're asking for. That's because the yes/no seems to be based on the subform records. If the first record is yes and that is the active record, then if you attempt to get that value from the form it will return yes. Consider also that when you open a form the active record is the first record by default. So in your form, that record is yes for JOU56. The only way to get a return value of No (in the second record) would be to select it. OK, so you code to hide the subform control. Now what? The form that contains the records with yes/no should now disappear? That makes no sense to me.

    If you want to hide the subform if any record in it contains no then that is a different matter.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I did not want to bother with the detail but here it comes.
    1. Table t09SubAccounts store a list of sub accounts if the user wanted more analyzing of accounts such as Groceries, Bank charges, Interest Paid or other.
    2. In query q02JournalsSub one field returns the "Yes" or "No" through an expression that check if the ID you see in field "Sub Ldg Acc No" is found in the table above.
    3. It is action on Subform1 that should cause Subform2 to be hidden.
    4. Text box txtSubAccountA on Main form is caused by Subform1, Subform1 is never hidden.
    5. Some journals will have many records, and yes, every record on its own. Only a few records will have sub accounts for more analyzing.

    My app has more than 250 forms, at least 20 use visibility options, so I have a bit of experience here, would like to learn what is the issue. You may become sure that I have some loose screws if I tell you I got one form where there are more than 100 buttons on top of one another. That form works well.
    I just thought of other ways, will test it. But if there is a "yes" in txtSubAccountA it must be possible to hide Subform2 instantly when it changes to "No".

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-13-2022, 03:44 AM
  2. AfterUpdate requery does not requery list box
    By ittechguy in forum Programming
    Replies: 5
    Last Post: 09-05-2017, 08:51 AM
  3. Replies: 11
    Last Post: 04-09-2016, 08:54 PM
  4. Replies: 7
    Last Post: 07-01-2015, 10:29 AM
  5. Replies: 1
    Last Post: 05-26-2014, 10:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums