Results 1 to 5 of 5
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    "Enable/disable" a control on one form based on listbox items on another form

    Experts:

    First of all, I apologize in advance for this lengthy post. However, IOT get to my question, I do need to provide some decent amount of background. Ultimately, I need some assistance with "enabling/disabling a control" on one form based on a count of list box items on a second form.

    The attached database contains 2 tables, 2 queries, and 2 forms. There are hidden objects but again, for addressing this questions, there's no need to look at those hidden objects. In order to "help you help me", please view the objects in the order summarized below:

    1. View form "F00_Billets":

    - Upon opening the form, you'll see a "Go to" combo (under the dark blue banner) and three listboxes (CFT, OPT, WS).
    - Use the "Go to" combo to navigate back and forth between the records.
    - For illustration purposes, only 3 out of 7 records contain a value in the listboxes. More specifically, the below summary shows which record has one CFT, one OPT, and one WS:
    * BIN 0001111 -> 1 CFT
    * BIN 0002222 -> 1 OPT
    * BIN 0003333 -> 1 WS
    * BIN 0004444 through 0007777 "Not assigned to a WQSB!"
    - Please note that the "message" box is for testing only and it merely summarizes the counts for the CFT + OPT + WS. Later on, I will disable the message box... right now they helped me to validate the correct accounting of list box items.

    2. View form "F01_Staffmember":
    - Upon opening, use the "Go to" combo to navigate from one staff member to the next.
    - In total, there are 7 staff members. These staff members are linked to the following billets (see Q100_N1S_BilletsStaffmembers):
    * 0001111 -> Fonda
    * 0002222 -> Eastwood
    * 0003333 -> Day
    * 0004444 -> Robertson
    * 0005555 -> Washington
    * 0006666 -> Wallach


    * 0007777 -> Van Cleef

    Additional background:
    - I track CFT, OPT, and WS assignments based on the "BIN" number (e.g., 0001111, 0002222, etc.).
    - Based on the billets to staffmembers relationship, however, I could inherently also say that staff member "Fonda" is assigned/linked to one "CFT".
    - Altenatively, for example, staff member "Eastwood" is assigned/linked to one "OPT".

    So far so good...

    More background:
    - Again, a CFT, OPT, and WS is an "activity" that remains pretty much constant.
    - In other words, these activies (essentially, cross-functional team membership or operational planning teams) will remain linked to a BIN/billet and rarely change.
    - However, staffmembers (employees) will join/leave the organization frequently.
    - Now, if a new staff member joins the organization and he/she is assigned to a BIN/billet which is linked to a CFT or OPT or WS, then this new employee must complete certain training.
    - Alternatively, in case the employee fills a BIN/billet that is NOT linked to the CFT/OPT/WS, then no training is required (unless there's a change where, e.g., 0004444 is linked to a CFT/OPT/WS later on).

    All that said, here's what I need some help with:
    - On form "F01_Staffmember", I have a combo called "Training Status". Values can take on either "Complete" or "Not Complete".
    - Now, in this example, the first staff member is "Doris Day". Again, she's BIN = 0003333... which in turn, is linked to a "WS".
    - So, in this case, I want the combo to be enabled so that I can change the value from, e.g., "Not Complete" to "Complete" (once the training reqs have been satisfied).
    - Now, if I were to navigate from "Doris Day" to "Denzel Washington" (assigned to BIN 0005555), I want the combo to be disabled.
    - In other words, I do NOT want the user to be able to accidentally change the training status value to "Complete" since Denzel Washington does NOT need to fulfill the training reqs.

    My question:
    How can I drive the enabling of the "Training Status" combo box on form F01_Staffmember where -- on form F00_Billets -- the "If Me.lstCFT_Summary.ListCount + Me.lstOPT_Summary.ListCount + Me.lstWS_Summary.ListCount > 0" is TRUE?
    Alternatively, once I navigate to a staff member who is NOT assigned to any CFT/OPT/WS, I want the "Training Status" combo to be disabled.

    Thank you advance,
    EEH
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Since form is in Single view, can use VBA. But can also use Conditional Formatting rule.

    Either way, options:

    1. reference F00_Billets controls, if form remains open

    2. query records using recordset or domain aggregate DCount() - domain aggregate can be used in CF rule
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    1. Billet form may not be open.
    2. Could you please provide background on Dlookup given different PkId numbers?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Where are assignments stored? What query would show these assignments?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    June7... there are hidden objects (incl. tables) where I store the WQSB (CFT/OPT/WS) assignments.

    Either way... you merely asking that question allowed me to solve the problem. That is, prior to my posting the question, I was thinking I somehow had to tie the count of list items on the billet form to my staff member form.

    Again, your asking where I stored the WQSB assignment made me rethink it. So, I will use three DLookups on the tables that store any potential assignments. If all of them return a null, I then know that the current staff member is not assigned to either a CFT or OPT or WS; thus, I disable the combo on my staff member form; ELSE I have the combo enabled.

    I got to this work w/ one DLookup thus far... I'll continue working on the other two (for OPT and WS).

    Thousand thanks for guiding me in this direction.

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

Similar Threads

  1. Replies: 5
    Last Post: 12-04-2015, 10:19 AM
  2. Replies: 1
    Last Post: 09-03-2014, 03:27 AM
  3. Enable "and/or" on custom search form
    By DB88 in forum Access
    Replies: 18
    Last Post: 06-09-2014, 02:32 PM
  4. Replies: 5
    Last Post: 08-03-2012, 04:20 PM
  5. Disable security warning to "enable content"?
    By Heatshiver in forum Security
    Replies: 2
    Last Post: 02-03-2012, 02:26 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