Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66

    Require data in only one of two combo boxes

    I've played with this one a good bit, and I have to ask someone smarter...



    I have a continuous form for entering products associated with a contract, including the accounting data. The accounting data can either be a capital project or an operational expense account (subaccount). It cannot be both.

    On the continuous form, I two combo boxes, one for each type of expense with rowsource of all the approved projects or subaccounts, respectively. Because it is a continuous form, I can't use an unbound box to collect the data then record it to the table AfterUpdate, because it would change all the other forms in the display. Likewise, I can't turn the boxes Visible property on or off either. I need to require the user to select EITHER a capital project or operational expense account. Since I can't really prevent it on the input side (continuous form can't prevent input on one row while allowing it on another), I instead need to institute some level of error checking that prevents the user from moving forward (BeforeUpdate?) if they have selected BOTH a capital project AND an operational expense subaccount.

    Notionally, I would check both controls. If both are not null, then I would stop the update and force the user to null one of them. No idea how to do that at a technical level.

    Appreciate whatever help you can offer!
    Thanks,
    Phil

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    'when user clicks SAVE or tries to exit the form/record

    Code:
    if IsNull(cbo1) and isnull(cbo2) then
      msgbox "You must fill in 1 of the combos"
      exit sub
    endif

  3. #3
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    Thanks, ranman256! So that takes care of ensuring at least one of the boxes is completed but what about the inverse (both boxes have data?) Would I add another If statement with IsNot Null for both?

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by pncbiz View Post
    Thanks, ranman256! So that takes care of ensuring at least one of the boxes is completed but what about the inverse (both boxes have data?) Would I add another If statement with IsNot Null for both?
    You could use something like the following code in the form's BeforeUpdate event.
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)    If Nz(Me.cmb1, 0) = 0 And Nz(Me.cmb2, 0) = 0 Then
            MsgBox "MUST have data in one combo"
            Cancel = True
        ElseIf Not Nz(Me.cmb1, 0) = 0 And Not Nz(Me.cmb2, 0) = 0 Then
            MsgBox "Data in ONE combo only"
            Cancel = True
        End If
    End Sub
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Would be interesting to know what is in each list because the choice seems to relate to one thing (Expense Type perhaps) thus one combo is all that's needed?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    Actually that's exactly where I went originally; just have one combo box, and switch out the RowSource based on an unbound opex/capex option group. One issue with that, however: it's a continuous form, so every time you change the option in the option group, you'd change the RowSource and the data displayed in all the records above. Also, each type is a five digit field, and while subaccounts don't change (generally speaking) from year to year, the capital projects do. It's entirely possible to have a capital project and a subaccount with the same 5 digit number. So that would require making that option group bound to an expense type field in the table...so I wind up with just as many fields in the table regardless. With two fields (and combos), I can do the backend math (totals, etc.) a little easier by just choosing (for Opex) all records where project is null, and for Capex, all records where SubAccount is null. If I'm missing something, please let me know!

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Then I think your form design is wrong, but perhaps I don't entirely understand the process. If you're entering products for a contract, I see the need for a continuous or datasheet subform. However, if the accounting data relates to the contract and the products to the contract, then you have a 1 to many relationship: one contract, many products listed. I don't see how the accounting type relates to each individual product, which is what you seem to be doing. So I have to wonder why the accounting type isn't part of the main form header since you said the accounting type relates to the contract, and the contract is the main part of the form, no?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    Yep, there's a one-to-many (contract to products) relationship, but a contract can (and sometimes does) have different funding sources for various products. A portion of the contract may be capital, because we're buying items with a life of greater than one year, but some contracts also combine operational expenses (annual licensing) that has to be paid from operational expenses. There's not a lot of those, but they exist. If I set an entire contract to one expense type (and then only one project or subaccount), then I'd basically have to enter the same contract multiple times to account for each type of funding. I think it's probably easier to keep the contract together in one form, and deal with the various funding sources. Great questions!

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I tend to agree with Micron "I don't entirely understand the process."

    Normally you describe the business process to understand the underlying tables and relationships, then, once vetted, you move to forms....

    Seems you have Projects, Contracts, Products and related accounting info.

    Does the Project determine the Capital vs Operational issue? Or is specific to each Product? Or Contract? Or does it vary by Product within a Contract or Project?

    Might help readers if you could give an example showing how the pieces fit in your environment.
    Good luck.
    Last edited by orange; 08-11-2020 at 11:02 AM. Reason: spelling

  10. #10
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    It varies by product within a contract (although not often).

    Let's say I sign a contract with Company A. They are a reseller of technology products. The contract has a contract#, start/end dates, etc. The contract lists all the products associated with the contract, along with the quantity, unit cost and extended cost. For this example, it's a contract supporting a technology implementation under which we are going to buy a physical server and server software licenses. Under our accounting rules, the physical server is a capital expenditure because it 1) exceeds $1000 and 2) has a service life beyond twelve months. The server software, however, is licensed on an annual basis. Under our accounting rules, the software is an operational expense and has to be charged to the annual budget as opposed to the capital budget.

    We have a (largely) static list of operational subaccounts against which to charge. In this case, the software would hit my opex budget under 60720 - Software Licenses and Fees. The hardware, however, would hit the capital project#, let's say Project 38001. In the database, I have a table for all the subAccounts to be the rowsource for a combo box of subaccounts. I also have the capital budget for the FY, with project numbers, as a rowsource for the capital project numbers.

    In this form, I want to record the contract (main form) then the products underneath (subform as continuous forms). Contract is a one-to-many relationship to products. But because each product can be funded from EITHER opex or capex, I have to be able to accommodate both on the form.

  11. #11
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    Bob's code did the trick for me - wish I had a more elegant solution but continuous forms are really constrictive. Thanks everyone for the help!

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    But because each product can be funded from EITHER opex or capex, I have to be able to accommodate both on the form.
    I can see now why the option can't be on the main form, but still don't see why your combo just doesn't provide either of the 2 values in your quoted statement. If it's because each of those has a sub list, then I suppose the all the options combined (opex list + capex list) are too many, or you would have just made a 2 column list -
    opex | list item 1
    opex | list item 2 etc.
    capex | list item 1 and so on.

    If that describes the situation, then you've got me wondering if cascading combos work in cf forms, but I'm not sure I want to build one to test that. If it did, the the choice of opex would generate only an opex list in the 2nd combo. That is how your data should be in the tables though. Another thought that comes to mind is 2 subforms; one for each product type, thus each with a combo specific to one or the other type, otherwise looking pretty much the same. At least you have something that works for now. If you do try the cascading combo thing in a cf form I'd sure like to know if it works.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    Micron,
    The opex subaccount list is over 100 items, and capex depends on how many projects are associated with that FY. So too much to combine into one combo. The other complication is that in my tables, since the SubAccounts are unique, they are the ID field. So to make a single combo to record the data in a single field I'd have to go back and restructure the SubAccounts table to have a separate AutoNumber ID. At this point, that's too much work (too many other forms/relationships to modify).

    Cascading combos do not work on continuous forms (at least out of the box). Because it is the same form repeated over and over, when you change the rowsource for the control on one "line" of the form, you change it for all of them. That makes all the combo boxes depending on a different rowsource to go blank. The data is still there, but the display just goes blank.

    I have built cascading combos in this same database. If you google around, there's a few techniques (like dynamically changing the RowSource On Enter/Exit) but the one that worked best for me is this:

    1) Create unbound 1st tier combo box with RowSource of whatever 1st tier ought to be, and have it set to Requery the second combobox in the AfterUpdate property.
    2) Create unbound 2nd tier combo box with RowSource filtered on 1st tier combobox (as you would any cascading combobox)
    3) Create a text box bound to the field the 2nd tier combo would normally be bound to. Put it on top of the #2 combobox and make it the exact same size without covering the drop down arrow.
    4) In the AfterUpdate of #2, copy the value of combobox 2 to the bound text box.

    That's it! The first combo will filter the second combo. The user clicks the arrow to make it drop down and makes a selection. The selection is then copied to the bound text box on top of the combo, and since it depends on the underlying field instead of a rowsource, it will display the data correctly.

    If your control is bound to an index field and you need text to accompany it, you have to do a little more work.
    1) Ensure the query for the form includes the index field and the descripion field
    2) Create unbound 1st tier combo box with RowSource of whatever 1st tier ought to be, and have it set to Requery the second combobox in the AfterUpdate property.
    3) Create unbound 2nd tier combo box with RowSource filtered on 1st tier combobox (as you would any cascading combobox)
    4) Create a text box bound to the ID field the 2nd tier combo would normally be bound to. Set its visible property to false (no).
    5) Create a second text box bound to the query's description field for the ID. Put it on top of the #2 combobox and make it the exact same size without covering the drop down arrow.
    6) In the AfterUpdate of #2, copy the value of combobox 2 to the first bound text box (bound to ID).

    That's it! The first combo will filter the second combo. The user clicks the arrow to make it drop down and makes a selection. The selection is then copied to the hidden bound text box, which the underlying query then puts the appropriate display data in the bound text box on top of the combo.

    I wrote it out in detail since this took WEEKS to figure out because everyone's instructions seem to leave out a step or two. I find that often the VERY helpful experts often leave out the "duh" details that novices like me have no clue about. That's not a dig (couldn't have gotten this far without them); it's just reflective of the enormous gap between their intuitive understanding of Access and my amateur efforts.
    Thanks,
    Phil

  14. #14
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    That's all very interesting and if you worked out most of that yourself, you should be proud. I do know about continuous forms and the effect that altering unbound controls has on each instance of it. One thing though - you don't typically change the rowsource on the fly when doing cascading combos. The secondary one has a rowsource that basically says "my list is filtered by what's chosen in the first one" and that's it. You don't alter the rowsource in any subordinate combo as you're saying. I'm not saying that would work in a cf form if the rowsources were constant and the combos were bound because I've never tried it in my 20+ years of dabbling in Access.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    I got some help setting up a dynamic rowsource set up...and it worked as long as you stayed in new records. That's why I changed to this method (text box overlay). RowSources don't change (as you suggest) but the displayed data stays constant. I have the first combo as unbound because I'm trying to avoid storing data I can just look up later. Db normalization is great in theory, but applying the theory can be tough for someone like me. I'm sure this database I've futzed around on for months is one you could whip out in weeks if not days... I sincerely appreciate the dialog. It expands my own intuitive understanding, reducing the number of really stupid questions I have to ask (and yes, I firmly believe there are stupid questions...).

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

Similar Threads

  1. Replies: 13
    Last Post: 12-27-2018, 05:56 PM
  2. Require data to be entered
    By angie in forum Forms
    Replies: 3
    Last Post: 04-30-2018, 10:47 AM
  3. Count of Combo Boxes with data
    By Mask in forum Forms
    Replies: 1
    Last Post: 06-28-2017, 03:37 AM
  4. Replies: 11
    Last Post: 08-06-2014, 09:47 AM
  5. how to filter data using two combo boxes?
    By jasonix in forum Reports
    Replies: 3
    Last Post: 11-21-2011, 03:04 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