Results 1 to 5 of 5
  1. #1
    MGF23 is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Jan 2018
    Posts
    5

    Code for cascading combo boxes


    Hi Guys,

    I have a combo box called ‘authorise reprint’ – from which you can select ‘yes’ or no’. ('NO' is the default)

    If ‘yes’ is selected - the box is enabled so the user can enter data – otherwise the box is disabled.
    Below is the code I am using (and seems to works fine):

    Private Sub Authorise_reprint_AfterUpdate()
    If Me.Authorise_reprint.Column(0) = "yes" Then
    Me.total_reprint_.Enabled = True
    Else
    Me.total_reprint_.Enabled = False
    End If

    My question:
    I want the box ‘authorise reprint’ box to control several other combo boxes on the form. How do I expand this code to achieve this?
    I get the desired effect by repeatedly copying and pasting this code (changing only the dependant box names as I go). This looks a bit unwieldy (and probably not the most efficient way of doing it).

    Any suggestions (or is this in fact the right way to go)?

    thanks in advance

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    your code above could have been written as:
    Me.total_reprint_.Enabled = Me.Authorise_reprint.Column(0) = "yes"

    but to do many boxes use:
    EnableBoxes Me.Authorise_reprint.Column(0) = "yes"

    Code:
    sub EnableBoxes(byval pbOn as boolean)
     Me.total_reprint_.Enabled = pbOn
     txtBox2.enabled = pbOn
    end sub

  3. #3
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001

    Arrow

    A couple of things. If Column(0) is the bound column of the combo you can refer to it simply as Me.Authorise_reprint
    If you have a number of controls then you could set a Tag value to loop around all the controls with that Tag value. So set the Tag property on the controls you want to lock to "Lock" (no quotes), then put the following code;

    Code:
    Dim bEnable as Boolean
    Dim ctl As Control
    Code:
    
    bEnable = False
    If Me.Authorise_reprint = "yes" Then bEnable  = True
    
    For Each ctl In Controls 
       If ctl.Tag = "Lock" Then
           ctl.Enabled= bEnable
        End If
    Next ctl
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    MGF23 is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Jan 2018
    Posts
    5
    Hi Minty,

    So assume the dependant box is called "job".

    I would add the word lock (no brackets) to the Tag propery of the box called "job"?

    then i put this code in the VBA editor?

    im bEnable as Boolean
    Dim ctl As Control
    Code:

    bEnable = False
    If Me.Authorise_reprint = "yes" Then
    bEnable = True

    For Each ctl In Controls
    If ctl.Tag = "Lock" Then
    ctl.Enabled= bEnable
    End If
    Next ctl

  5. #5
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Yup spot on.
    It's a good idea to rename the default names for things on forms so you know what you are referring to.
    I call text boxes txtFieldName, and combos cmbFieldName as it makes the code much more readable in a few weeks time when I can't remember what is what.

    It messed up the formatting for some reason
    Code:
    Dim bEnable as Boolean
    Dim ctl As Control
    
    bEnable = False
    If Me.Authorise_reprint = "yes" Then bEnable = True
    
    For Each ctl In Controls 
       If ctl.Tag = "Lock" Then
          ctl.Enabled= bEnable
       End If
    Next ctl
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Cascading combo boxes
    By SunTop in forum Forms
    Replies: 2
    Last Post: 12-10-2016, 11:29 AM
  2. Cascading combo boxes... help me again please!
    By aqueousdan in forum Access
    Replies: 6
    Last Post: 09-20-2016, 06:54 AM
  3. cascading Combo boxes
    By Chatholo in forum Forms
    Replies: 2
    Last Post: 08-09-2013, 01:39 AM
  4. Cascading Combo boxes
    By finsmith in forum Forms
    Replies: 10
    Last Post: 02-12-2013, 09:37 AM
  5. Cascading Combo Boxes
    By desireemm1 in forum Programming
    Replies: 1
    Last Post: 10-05-2009, 06:00 AM

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