Results 1 to 15 of 15
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    Cascading Combo Box on Form to Subform?

    Hi all,
    I have a form (TransactionFrm) and on that form i have a sub form (CatagorySubFrm).
    I have a combo on the main form (CboCatagory) and on the sub form I have a combo (SubCatagoryID)
    I am trying to get only the SubCatagories that relate to the Catagory and cannot figure out how to get the form to sub form?


    Have tried the following:

    Code:
    'I put this on the row source of the SubCatagoryID 
    SELECT [SubCatagoryTbl].[SubCatagoryID], [SubCatagoryTbl].[CatagoryID], [SubCatagoryTbl].[SubCatagoryName] FROM SubCatagoryTbl WHERE "CatagoryID=" & Me.TransactionFrm.Form.CboCatagory ORDER BY [SubCatagoryName];
    Code:
    'I put this in the after update event on the CboCatagory
     Me.SubCatagoryID.RowSource = "Select * From SubCatagoryTbl Where CatagoryID=" & Me.CboCatagory
    How on earth do i get from form to sub form?

    Thanks
    Dave

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Can you post a copy of the db
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    'I put this on the row source of the SubCatagoryID
    Where, property sheet? Then you can't use Me.
    Also, your concatenation and quoting make no sense in either example regardless of where you're doing this.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    AccountTest.zip

    Sure, Open TransactionFrm

    While in there, i am having one heck of a time getting the NotInList to work for the Combo (CboCatagory) It opens form but wont let me save it with the button?
    Something is necessary to click Add when the form CatagoryFrm opens.

    Thanks

  5. #5
    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 d9pierce1 View Post
    AccountTest.zip

    Sure, Open TransactionFrm

    While in there, i am having one heck of a time getting the NotInList to work for the Combo (CboCatagory) It opens form but wont let me save it with the button?
    Something is necessary to click Add when the form CatagoryFrm opens.

    Thanks
    I'm having trouble understanding your requirement.
    OK I've opened TransactionFrm

    What's next?
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You must have uploaded the wrong db. There is no AfterUpdate event on cboCat.
    Don't understand the complex approach to the not in list issue. Did you step through that event and see how many times other functions are being called because of your code? I have no solution for your method because if you don't save the form you get error 2118 (must save field before requerying control to get the new value) and if you do save the record you get trapped in a loop. Why bother with the add "catagory" (SP) form anyway? Suggest one of the methods listed here for bound combos

    https://docs.microsoft.com/en-us/off...obox.notinlist
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Bob, if you're looking into that, there is no category field in the subform so there is no relationship like master/child. You can't even filter based on category.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Sorry about that,
    Actually I have it commented out on the After Update event as it was a problem that i couldnt fix so to stop it from running i just commented it out.

    As far as the Not in list event, it does work to open the form, put the txt in the correct spot, but on that form it wont let me save it. I for it when it opens to that form, to
    call on the CmdAdd_Click so it lets me save! Most likely how i have done my buttons.

    Big question is how to get the data from form to subform in the combos ?

  9. #9
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    thank you,
    I will check out and see if that helps....
    Dave

  10. #10
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Cascading comboboxes in datasheet view forms is a little a bit tricky and risky purpose.

    Because the bound column of the combo box of subcategories has to be the visible column (SubCatagoryName), has to denormalize the table [CatagorySubTbl] by adding a new auxiliary field to keep the name of the selected subcategory, and bound on this field the column of the subcategory name, and in AfterUpdate event, has to set the value of the [SubCatagoryID] explicitly to the value of the first column of combobox.

    I return your database with the appropriate changes, only for the issue of the thread.

    I added the Property Let Category() procedure in CatagorySubFrm's code which manage the rowsource of the subcategory combobox as category changes. So, from TransactionFrm's code, not having to mind about combobox name etc, you can change the category of CatagorySubFrm as follows:
    Code:
    Me.SubCatagoryForm.Form.Category = Me.CboCatagory
    Now, CatagorySubFrm knows what have to do with the new category.

    Check if works as you expect.

    Cheers,
    John
    Attached Files Attached Files
    Last edited by accesstos; 12-06-2020 at 03:24 PM. Reason: Code correction

  11. #11
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi Accesstos,
    Nice work for sure! I do have a couple of questions if you dont mind.
    1. When adding a new Transaction i am getting 2 update messages, can those be shut off?
    2. In the qryCatagorySub you created, there is a field for CatagoryID which doesnt get filled in?
    3. I used this not in list (Below) for the CatagorySubFrm in previous try which worked well but now that i have made changes to where the Catagory is,
    Will this work in that NotInList event? I may have to change some names of the Cbo and such but do i need to put a hidden CatagoryID in this subform?
    The purpose of it if I add a new field to the subcatagory, then i want it to attach the catagoryid along with it if that makes sense?


    Code:
    Private Sub CboCatagorySub_NotInList(NewData As String, Response As Integer)
    Dim strsql As String, x As Integer
    x = MsgBox("Sub Catagory is Not in Current List, Would you Like to Add?", vbYesNo)
    If x = vbYes Then
    strsql = "INSERT INTO CatagorySubTbl (CatagoryID, CatagorySubName) " & _
            "VALUES (" & CboCatagory & ", '" & NewData & "')"
        'MsgBox strsql
       CurrentDb.Execute strsql, dbFailOnError
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
    End Sub
    Again, thank you so much!

  12. #12
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi David!

    1. Even if I don’t understand what the use of this procedure is, you have to move the line DoCmd.SetWarnings False before the statement Select Case to cover all the cases. Take care to reset the SetWarnings to True before the exit of procedure (E.g. in case of Error). I never use the command RunSQL. In code I use the method Execute of a database object. […]Normally, you shouldn’t store all this info and the table AccountLedgerTbl should be a query on table TransactionTbl for report purposes.

    2. This field came from the CatagorySubTbl where shouldn’t be there. I didn’t use that query but if you want to keep it, take the [CatagoryID] from the table SubCatagoryTbl and remove that field from the CatagorySubTbl.

    3. Because in this subform, the CatagoryID, except of the normal values, could be Null or -1 (ALL), you have to give to the user the chance to choose the category for the new subcategory. In case of Null or -1 category, you can pass the control in a simple pop up form as dialog to save the new subcategory, checking the max stored SubCatagoryID before and after this “dialog box”. If those max values differs, then the new subcategory has added. So, you just have to say: Response = acDataErrAdded and the new subcategory will be the current in the list. In case of an actually CatagoryID (>0), you just step to an INSERT INTO statement in “stealth mode” and set again the Response to acDataErrAdded. If the user doesn’t want to add the new subcategory, just set the Response to acDataErrContinue. Because the cboSubCat.Column(1) returns Null in the NotInList event, have to keep this info (Category) in a private variable in the module of the subform or in a hidden textbox. I prefer the private variable way.
    Code:
    Private Sub cboSubCat_NotInList(NewData As String, Response As Integer)
        Dim strMsg As String
        Dim lngID As Long
        Dim lngCat As Long
    
        strMsg = "Sub category '" & NewData & "' is not Not in Current List, Would you Like to Add?"
        If MsgBox(strMsg, vbQuestion + vbYesNo, "New sub category") = vbYes Then
            'Get the last SubCatagoryID
            lngID = Nz(DMax("SubCatagoryID", "SubCatagoryTbl"), 0)
            'Get the CatagoryID
            lngCat = Nz(Me.Category, 0)
            If lngCat > 0 Then
                'We have an existing CatagoryID
                On Error Resume Next
                CurrentDb.Execute "INSERT INTO SubCatagoryTbl (CatagoryID, SubCatagoryName) " _
                                  & "VALUES (" & lngCat & ", '" & NewData & "')", dbFailOnError
                If Err Then
                    MsgBox Err.Description, vbExclamation, "New Sub Category"
                    Err.Clear
                End If
            Else
                'CatagoryID could be -1 (ALL) or Null.
                'User have to add the new subcategory via the specific form.
                DoCmd.OpenForm "frmNewSubCat", , , , , acDialog, NewData
            End If
            'Comparison of "old" and "new" SubCatagoryID.
            If lngID = Nz(DMax("SubCatagoryID", "SubCatagoryTbl"), 0) Then
                'Nothing added
                Response = acDataErrContinue
            Else
                'We have a new sub category
                Response = acDataErrAdded
            End If
        Else
            Response = acDataErrContinue
        End If
    End Sub
    You can pass the new subcategory name to the pop up form through the OpenArgs property:
    Code:
    'Code of the frmNewSubCat
    Private Sub Form_Load()
        Me.txtSubCatName = Me.OpenArgs
    End Sub
    But, after a deeper look in your project, I think that is the right time to rebuild it from scratch. You have written much more code than it needs, and, you have used more controls and risky commands in forms than required. For example, while you already have the name of the TransType in the second column of the CboTransType, you put it in TxtTransType, or, you try to replace the navigation buttons of the forms and the ribbon with your own.

    I recommend routing your energy to the creativity, not to the repetition.

    Good luck with your project!
    John

  13. #13
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    But, after a deeper look in your project, I think that is the right time to rebuild it from scratch.
    By chance did you look at the relationships in that "deeper" look? I got the impression that it's not normalized very well but I didn't make that comment because
    - I didn't think it would be appropriate given the problem at hand wasn't a result of how the tables look
    - you have to understand the process (which I don't) to know if the schema is good.

    Some things I wondered about:
    can't see the reason for a cat type and cat tables
    use of multi value fields
    looks like data in 2 or more tables but not related (e.g. openbalance)
    repeated tables (account, account1, account2)
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    thank you, will see where this goes from here!
    Thanks for the help
    Dave

  15. #15
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Dave, you are very welcome!

    Obviously, your project is under construction, and, obviously, you build it as you learning. I think that until now, you have learned enough to rebuild it from scratch and, by rejecting some developing mistakes, you will achieve the today’s level very soon and on a much better base. Don't afraid of the stetback. The exit of a dead-end street is its entrance.

    I don’t think that you have too much to do with your schema but, one of those is to remove the table AccountLedgerTbl form this database. In place of this table, create a UNION query on table of transactions to track the appropriate info for each account. If you need help on this, just open a new thread. Also, I think that the field CatagoryID shouldn’t exists in tables CatagorySubTbl and TransactionTbl.

    The form for the transactions is the best applicant for a wizard. You could avoid all those risky Enable/Disable Show/Hide Name/Rename of controls by using a tab control with simple subforms in its pages with the corresponding controls for each step of the transaction. So, you have a functional and robust UI with the less VBA code. I believe, with a research for Access forms as wizards, you will find many samples, but, allow me to attach mine for demonstration and inspiration. WiZ_01.zip

    I would be glad to help you in any way but not in a way that drives you in dead-end streets.

    By chance did you look at the relationships in that "deeper" look?
    Micron,

    I have to concede that, while it was deeper, my look was very quick. I haven’t seen the schema until your remark but, as I said, I don’t think that it needs too many changes. AccountTbl_1 and AccountTbl_2 are instances of AccountTbl, not actual tables in db. The subcategories, is going to use them as details of transactions, if I understood well.
    Maybe this layout makes the process clearer.

    Click image for larger version. 

Name:	DaveSchema.JPG 
Views:	13 
Size:	98.2 KB 
ID:	43570
    When I said that Dave has to build it from scratch, notably I had in mind the UI and this redundancy of code and controls.

    Cheers,
    John

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

Similar Threads

  1. Replies: 10
    Last Post: 01-09-2019, 08:00 PM
  2. Replies: 1
    Last Post: 10-31-2018, 01:14 PM
  3. Replies: 2
    Last Post: 05-30-2018, 05:08 PM
  4. Cascading Combo Box and Subform Issues
    By RossIV in forum Forms
    Replies: 13
    Last Post: 07-18-2013, 09:16 AM
  5. Cascading Combo in SubForm
    By ggs in forum Forms
    Replies: 1
    Last Post: 02-16-2012, 01:32 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