Results 1 to 5 of 5
  1. #1
    Bruce54 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2018
    Location
    Penrith, NSW, Australia
    Posts
    3

    General Handler for a group of similarly named controls (text boxes) and their Click events.

    Disclaimer: I am Not any sort of a coding expert or professional, just a 64 yo volunteer at a Museum,
    helping them keep a Truck Show Charity Event in order. Meagre knowledge of Excel and Access.

    I have a Form containing 57 text boxes (and others), laid out as a full page menu system. (image below)
    Clicking on any of these 57 sends a report on the Category Clicked, to either the Printer or Screen.
    Coding 57 separate [Private Sub Text1_Click()] macros seems extremely inefficient and time consuming.

    [image]


    Click image for larger version. 

Name:	TruckShowMenu.jpg 
Views:	12 
Size:	236.7 KB 
ID:	33348
    [/image]

    I had an idea that something like the snippet below might work, but lack the knowledge to proceed:
    All the controls involved have the same names (e.g. "Title1", Title2" ... as do their labels and counters.
    [Make] is a field inherited from the Source Query

    I've purposely Commented-Out the codelines, knowing they aren't operational

    Code:
    'MakeVar = Array(0, 26, 21, 8, 11, 14, 13, 15, 9, 22, 24, 25, 29, 30, 5, 3, 19)
    'For ctlID = 1 to 57
    '   Select Case Controls
    '       Case ("Title" & ctlID)_Click
    '           DoCmd.OpenReport "GEN-ALL", acViewPreview, , "[Make] = " & [MakeVar], , Controls("Title" & ctlID).Text
    '   End Case Select
    'Next CurrentControl
    All the research I've done seems to indicate the Brute Force method (57 separate macros). Whilst that Does work ... it's a real Cow to type out.

    Any help much appreciated, in fact, Many Thanks for even reading my post

    Bruce54

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Instead of hard coding text boxes, Instead use data lists.
    all menu options are records in a table,
    user picks the caption, it opens the form,or report.
    [caption], [option],[menu]
    1, clients, frmClients, mainmenu
    2, reports, frmReports, mainmenu
    etc

    User picks caption,
    Docmd.openForm lstBox

    theres also the switchboard method. Find examples.

  3. #3
    Bruce54 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2018
    Location
    Penrith, NSW, Australia
    Posts
    3
    That's an option I hadn't explored. The CEO of the Museum, rough-sketched what he wanted, I was doing what I could to meet the needs using his guidelines ... That said, I love your idea.
    I'll start building it and parallel the older work.
    My biggest problem was the use of the MultiValuedField for the Entries ... took me near a week to get it operational. Your option seems ... elegant, to use a weird word.
    Many Thanks RanMan. I really only started Access a few weeks ago.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Either use listboxes as suggested by ranman or combo boxes. Both will work
    Strongly recommend you do not use multivalue fields.
    As well as taking a long time to setup, these will cause lots of issues when filtering or searching your database
    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

  5. #5
    Bruce54 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2018
    Location
    Penrith, NSW, Australia
    Posts
    3
    I hear that about the boxes, I've only had a few hours to play with it, and I can already see the advantages. I love players that put good graphic examples on uTube.
    As the the MultiValued field (fortunately, there's only one), I have it working, well. Just had to realize What it was doing, rather than accept all the nay-sayers. It fits the situation atm.
    That being said, I am also working on it's replacement. I also need to get all the "Run-Once" setup files out of the OnLoad area (old habits die hard). Many thanks for your input

    Bruce54

Please reply to this thread with any new information or opinions.

Similar Threads

  1. On Click event - apply to all text box controls
    By Middlemarch in forum Forms
    Replies: 5
    Last Post: 09-12-2017, 02:15 AM
  2. Replies: 10
    Last Post: 08-21-2015, 12:12 PM
  3. Triggering events for controls in a class
    By fritesmodern in forum Programming
    Replies: 2
    Last Post: 07-14-2015, 01:53 PM
  4. External script to call Command Button Click event handler
    By Sean Nakasone in forum Programming
    Replies: 5
    Last Post: 10-29-2014, 12:35 PM
  5. Form events and showing/hiding controls
    By Monterey_Manzer in forum Forms
    Replies: 2
    Last Post: 03-26-2013, 03:01 PM

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