Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314

    Code chokes when run within a Tab Control

    I have an Events main form with a continuous sub-form, and a control table, Role_Act. The code is associated with the Role combo box control in the sub-form [me]. When I run the Events form directly, the code works, but when I run the Events form within a TAB Control, as it would normally run, it chokes on "[Forms]![Events]![Event_Type]".

    Private Sub Role_GotFocus() ' Filter Role Combo Box
    Me.[Role].RowSource = "Select [Role_Act].[Role],[Role_act].[Ev_Type]" & _
    " From [Role_Act]" & _
    " Where InStr([Role_Act].[Ev_Type], [Forms]![Events]![Event_Type])" & _


    " or [Role_Act].[Ev_Type] = 'All';"
    End Sub

    Why does it matter in which context it is run, and how do I fix it?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The InStr part of the WHERE clause is incorrect.
    See https://support.office.com/en-us/art...8-77CD0CB8A55B

    There of course may be other issues as well
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    when you have something like [Forms]![Events]![Event_Type] contained within quotes like you do, it becomes a literal string. In other words, you are not passing the value pertaining to the referenced control, you are passing it's hierarchy (path, beginning with the Forms! collection reference). You have to concatenate, as in

    "...Where InStr([Role_Act].[Ev_Type]," & [Forms]![Events]![Event_Type]) & " OR [Role_Act].[Ev_Type] = 'All';"
    while being careful to maintain spaces where required, so you don't end up with something like ORmyReferencedControlValueHer
    FWIW, I would block capitalize key words like OR, AND, LIKE. It just makes the expression more readable. Just my opinion.

    EDIT.
    Whoops, I totally missed the fact that this was wrapped in that function even though I copied and pasted it. Focused on this strictly as a sql building statement. I'll leave it in in case it could help in the future. It could prove to be somewhat correct I think, depending on what the function is returning (that value might have to be delimited by ' or #).
    EDIT 2. Actually, not seeing what clarification the link provides as they don't cover form control referencing. I think it ought to work as written insofar as returning a value assuming no Nulls, but it won't result in a correctly structured sql statement. If I substitute PARTY for the event type, we ought to get (forgetting about line continuation characters)

    "Select [Role_Act].[Role],[Role_act].[Ev_Type] From [Role_Act] Where
    InStr([Role_Act].[Ev_Type], PARTY) OR [Role_Act].[Ev_Type] = 'All';"

    Not only will there be no string delimiters around PARTY, the result makes no sense as the 1st part isn't being equated to anything, and doesn't have string delimiters either. Not sure of their need - would have to test.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Kudos is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2017
    Location
    Toronto, ON
    Posts
    29
    In place of [Forms]![Events]![Event_Type] try [Forms]![MainForm]![MainFormSubformControl].[Form]![Event_Type].

    When a form is opened as a subform, Access doesn't consider the form itself to be open so the direct path will fail. Your path will need to be the full path to the control you need, through all the layers.

    Once switched, the form will choke when opened directly, and not choke when opened in the tab control. If you need it to work in both scenarios, you should assign that string to a variable and have the logic for the variable checking if a parent form exists or not before deciding which string to assign.

  5. #5
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    Ok. I tried it as shown here, in context of the tab control, but it still chokes the same as before:
    Private Sub Role_GotFocus()
    Me.[Role].RowSource = "Select [Role_Act].[Role], [Role_act].[Ev_Type]" & _
    " FROM [Role_Act]" & _
    " WHERE InStr([Role_Act].[Ev_Type],[Forms]![MainForm]![MainFormSubformControl].[Form]![Event_Type])" & _
    " OR [Role_Act].[Ev_Type] = 'All';"
    End Sub

  6. #6
    Kudos is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2017
    Location
    Toronto, ON
    Posts
    29
    [Forms]![MainForm]![MainFormSubformControl].[Form]![Event_Type]

    MainForm needs to be swapped with your parent form name (whatever form holds the tab control) and MainFormSubformControl swapped with the name of the subform control on your parent form.

  7. #7
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    Form naming not withstanding, this is still going to fail because, as has already been pointed out, you are using the InStr function incorrectly.

    InStr returns a result. This will either be Null, 0 or the numeric position at which a match is found (1,5,17, etc.). So this-

    WHERE InStr([Role_Act].[Ev_Type],[Forms]![MainForm]![MainFormSubformControl].[Form]![Event_Type])"

    -even if you had all the form references and delimiters correct, depending on what InStr returns, would basically be the equivalent of saying;

    WHERE Null

    -or-

    WHERE 0

    -or-

    WHERE 1 (or 5 or 17 or some other number)

  8. #8
    Kudos is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2017
    Location
    Toronto, ON
    Posts
    29
    Agree. It's just interesting that the claim has been made that it works great when the Events form is opened directly. I assume it should be closer to something like this:

    "Where [Role_Act].[Ev_Type] Like '*' & [Forms]![MainForm]![MainFormSubformControl].[Form]![Event_Type] & '*'" or thereabouts.

  9. #9
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    My original code:
    Me.[Role].RowSource = "Select [Role_Act].[Role], [Role_Act].[Ev_Type]" & _
    " From [Role_Act]" & _
    " Where InStr([Role_Act].[Ev_Type], [Forms]![Events]![Event_Type])" & _
    " or [Role_Act].[Ev_Type] = 'All';"

    Perhaps you are right, but this code, as is, filters the Role options as expected, when the main form is run directly, but not when the main form is run in context of the TAB Control form. I just put the original code back in and ran the main form directly and tested it again, and it still works. How does it then work?

  10. #10
    Kudos is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2017
    Location
    Toronto, ON
    Posts
    29
    We're not sure. But did you try it with [Forms]![YOURPARENTFORM_NAME]![YOURPARENTFORM_SUBFORMCONTROL_NAME].[Form]![Event_Type] ?

    Keep in mind the second one is not the name of the tab control. It is the name of the subform control within the tab control.

  11. #11
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    I believe I did earlier, but I am getting confused. I think it is better if I show a screen print of the full context of the forms. The top level form is the Manage SCATeam form. Everything runs within this form. Then the Events TAB Control, then the Events main form with its Assignments sub-form. Does this help to understand the full context?

    I guess why the InStr works as is, is because it always returns a number > 0. It is either going to find the Event_Type string, or it will find "All". Here is an example of several Role entries:
    Role > Ev_Type
    Staff > Callout, Board Meeting, Interview
    IC > Callout
    Member > All
    Traffic > Callout, Civic Event
    Click image for larger version. 

Name:	Events form.JPG 
Views:	20 
Size:	236.5 KB 
ID:	34473

  12. #12
    Kudos is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2017
    Location
    Toronto, ON
    Posts
    29
    This looks like a navigation control rather than a tab control. Click on the subform bucket (the entire box below the tabs) and figure out what the name of the control is. Put that in the below string where SUBFORMCONTROL is currently. Then put this string where you currently have [Forms]![Events]![Event_Type].

    [Forms]![Manage SCATeam]![SUBFORMCONTROL].[Form]![Event_Type]

  13. #13
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    Thank you. THAT WORKS! The name of the box is NavigationSubform. I guess I got the controls reversed in my mind. Its been six months since I learned about the two controls and implemented them. Under the Member Navigation Control button, I have a member form with a set of embedded Pages, which must be the TAB Controls. In the Member > Event Control Tab, I also have a similar Role combo box, which needs the same filtering. I presume, I just need to add another level for the page form into that string?

    I should have sent the screen print earlier, as I wasn't describing the context correctly. Sorry. Here is the the code which works in context, but not by calling the Events form directly. I am not sure how to ask the question, Is the Events form running directly, or in context, so I can run it either way.

    Private Sub Role_GotFocus()
    Me.[Role].RowSource = "SELECT [Role_Act].[Role], [Role_Act].[Ev_Type]" & _
    " FROM [Role_Act]" & _
    " WHERE InStr([Role_Act].[Ev_Type], [Forms]![Manage SCATeam]![NavigationSubform].[Form]![Event_Type] )" & _
    " OR [Role_Act].[Ev_Type] = 'All';"
    End Sub

  14. #14
    Kudos is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2017
    Location
    Toronto, ON
    Posts
    29
    You were describing it pretty well actually, you outlined the number of layers involved, which was the key piece of info. Are you sure having the Events form opening directly is an actual requirement for your users? If so, there's a couple different ways to check for a parent, I'll post those tomorrow.

    It is still inexplicable to me that this SQL string works at all, so incorporating the string variable we'll generate will probably take some trial and error on your part.

  15. #15
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    I had originally left out the Manage SCATeam top form, before I included the screen print. No. Running the Events form directly, is not necessary for the user interface. It was just convenient for testing, during development. I found, one cannot place buttons on a live sub-form environment, using the button wizard. So, to add buttons, one needs to run the form stand-a-lone. I would be interested in learning how to ask that question, just for the sake of learning. More importantly, is adapting this procedure to work in the perspective of the Member and his Activities, which uses the same underlying Activities table. Was I right suggesting, I just need to add another level for the Page Tab form into that string?

    The procedure seems to work flawlessly now. Each Parent form Event Type filters a different set of Roles in the Assignments sub-form.

    I have another function related to these same two forms. The Assignments sub-form contains another combo box called Activity, which is redundant with the Event.Event_Type, for displaying in the above Member perspective, which needs to show type of event he participated in. I need to populate that control for all the rows filled in, in the context of the Event, after the Event form is updated. It appears this procedure also has to deal with this same full form context. I have pieced this together from similar procedures listed on MS Access support pages.

    Update a control in a child Continuous form, from a control in its Parent form
    For code in the form module use "Me"
    For code in another module, use Forms("Form name").Recordset
    Events form AfterUpdate property [me]

    Private Sub Update_Activity_AfterUpdate()
    Set rst = Forms([Activities Ev Subform]).Recordset
    rst.MoveFirst
    Do Until rst.EOF
    [Forms]![Events]![Activities Ev Subform].[Form]![Activity] = Me.[Event_Type]
    rst.MoveNext
    Loop
    End Sub

    When I attempt to run it, it causes an error with the Set Statement, but I adapted it right out of one of MS examples. In light of what I learned today, I'll revisit this tomorrow, to see if I can get it working. This stuff can surely get complicated, especially for one such as I, who is relatively new to VBA.

    Thanks again for your help.

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

Similar Threads

  1. Control to VBA code
    By Thompyt in forum Programming
    Replies: 2
    Last Post: 04-21-2016, 05:10 PM
  2. Replies: 6
    Last Post: 06-11-2014, 01:00 AM
  3. code to apply control formatting
    By tariq1 in forum Programming
    Replies: 3
    Last Post: 07-21-2012, 12:36 PM
  4. Code not working with tab control
    By pg13Reader in forum Programming
    Replies: 4
    Last Post: 12-13-2011, 06:30 PM
  5. Tabular Control Code
    By benthamq in forum Programming
    Replies: 6
    Last Post: 09-12-2011, 04:16 PM

Tags for this Thread

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