Results 1 to 6 of 6
  1. #1
    eugzl is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    68

    add new item in multi-cascade combo boxes

    Hi All.
    I created multi-level cascade combo boxes with option to add new item(s). But I would like also create a code to pass value from parent form to child form and then when new items are saved into database table from child form return to parent form and refresh data in it. What I mean. For instance, I have parent form with multi-level cascade combo boxes. When user select value from first combo box and didn't find needed value in second combo box then user will click button to call child form to add new item to second combo box.
    Question1: How to pass selected value from first combo box from parent form to child form and to make that value read only?
    Then when user entered all required data and saved their in child form user will close child form to return to parent form.
    Question 2: How to refresh list and display a new added value in second combo box when user will return to the parent form?
    The same story with third combo box.
    Question 3: I cannot figure out why that code:
    Code:
                    DCount("*", "tblDeviceTypes", "DeviceType =" & !DeviceType & " AND BrandID = " & !BrandID) > 0 Then
                    MsgBox "Brand and Device Type already exist. Please enter a different Device Type.", vbOKOnly
                    SetSaveClose Me, False
                    Cancel = True
                    Exit Sub
    return an error message:
    Click image for larger version. 

Name:	ErrorMsgDT.jpg 
Views:	13 
Size:	12.2 KB 
ID:	46829

    When that piece of code commented i have ability to save a record. I will be appreciate if someone show how to solve those problems.
    In the attachment is last version of DB file.


    Thanks.
    AddNewCascadeCB.zip

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    1,648
    If you have string variables/controls you need to surround them with single quotes, unless the variable/control has a single quote?
    Requery combos after adding new values for source.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,653
    DeviceType is a text field so need apostrophe delimiters.

    Try:

    If DCount("*", "tblDeviceTypes", "DeviceType ='" & !DeviceType & "' AND BrandID = " & !BrandID) > 0 Then
    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.

  4. #4
    eugzl is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    68
    Hi June7. Thanks for replay.
    Now DCount works. Thanks. What about Question1 and 2

    Thanks.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,653
    1. several ways to pass data between forms - OpenArgs, global variable, TempVars, second form references first form
    just closing second form should return focus to first form
    if code must continue executing on first form, the second form should be opened with acDialog parameter - this will suspend code execution on first form until second form closes

    2. common approach is to use combobox NotInList event to add new items into lookup table 'on the fly' during data entry. Review https://blueclawdatabase.com/notinlist-event-code/
    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.

  6. #6
    eugzl is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    68
    Thanks for the link. But I'm new in Access and don't know yet what properties I must to set up in such form. I will appreciate if someone show how to modify my attached file by add options that I asked in my first post.

    Thanks

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

Similar Threads

  1. multi-cascade combo boxes
    By eugzl in forum Access
    Replies: 9
    Last Post: 11-15-2021, 03:35 PM
  2. Replies: 3
    Last Post: 08-20-2017, 02:58 PM
  3. Replies: 3
    Last Post: 09-19-2016, 03:22 AM
  4. cascade combo boxes in continous forms
    By storm1954 in forum Forms
    Replies: 3
    Last Post: 05-10-2012, 06:00 AM
  5. Sub-Sub Form Cascade Combo Boxes
    By Huddle in forum Access
    Replies: 4
    Last Post: 03-22-2012, 01:42 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