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

    Requery combo on form from cmd button on a different form

    Hi all,


    My main form has an unbound combo on it that is populated by a query. If no record is present i hit an add button which opens a form to add a new record. When i hit close on that form by CmdClose button, i want it to requery the combo on the main form so its not blank any longer. I have the following code but it just doens work. I have tried several methods but just cant get it to requery the combo. If I go out of record on main form and then back, then its there.

    Code:
    Private Sub CmdClose_Click()
        
    If Me.NewRecord Then
    Me!Preferred = (DCount("PersonID", "Person2AddressTbl", "PersonID=" & Me.CboPerson & " AND Preferred = True") = 0)
    End If
        
    Forms!PersonFrm.Controls!CboAddressType.Requery
    Forms!PersonFrm.Refresh
    DoCmd.Close

  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,818
    Do you mean if item is not in combobox list? Usual approach is to use NotInList event of combobox. 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.

  3. #3
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi June7,
    Its not really a conventional method i am using for this. On my main form (PersonFrm) i have a way to display address type which i pick from a combo (CboAddressType) . If no address is present, then the combo is not enabled. For the first record you have to use an add button to open the address form to add a new record. Reason being is I only want to display the addresses for this person on the form. I don't want to use not in list as then I get a list of tons of address types that have nothing to do with this person. If that makes any sense. So, once I add the new address record on the pop up form and close it, I want to have the combo (CboAddressType) on my PersonFrm to requery without having to go out of the record and back to it. The combo (CboAddressType) is an unboud combo, with a record source from a query, that populates address info per the address type selected.

    So basically, once I hit close on my pop up form, I want the combo to requery and populate.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not sure I fully understand the logic of what you are trying to do but to do this

    So basically, once I hit close on my pop up form, I want the combo to requery and populate.
    in the popup form close event put

    forms!myMainFormname!combocontrolname.requery

    you might find this link useful for how to reference controls in other forms regardless of which form you are currently in
    http://access.mvps.org/access/forms/frm0031.htm

  5. #5
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi June7,
    I tried that and it didn't do anything.

    Forms!PersonFrm!CboAddressType.Requery

  6. #6
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Put that on forms close event, still doesnt work? Have to leave record and go back to it for it to requery

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I tried that and it didn't do anything.
    in that case there is something about the combo rowsource which hasn't changed. I am assuming the rowsource is based on whatever table your subform is based on

  8. #8
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    CboAddressType on PersonFrm
    AddressType Rowsource on PersonFrm
    SELECT [Person2AddressQry].[AddressID], [Person2AddressQry].[PersonID], [Person2AddressQry].[AddressType], [Person2AddressQry].[Address1], [Person2AddressQry].[Address2], [Person2AddressQry].[City], [Person2AddressQry].[State], [Person2AddressQry].[ZipCode], [Person2AddressQry].[County], [Person2AddressQry].[Preferred] FROM Person2AddressQry ORDER BY [Preferred], [AddressType];


    CboAddressType on AddressFrm
    AddressType rowsource on AddressFrm
    SELECT [AddressTypeTbl].[AddressTypeID], [AddressTypeTbl].[AddressType] FROM AddressTypeTbl ORDER BY [AddressType];

  9. #9
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    I also have this code on the on current event of PersonFrm

    Code:
        CboAddressType = DLookup("AddressID", "Person2AddressQry", "PersonID=" & [PersonID] & " AND Preferred=True")
        If IsNull(CboAddressType) Then
        CboAddressType.Enabled = False
        LblAddressTypeEdit.Visible = False
        CmdAddAddress.Visible = True
        Else
        CboAddressType.Enabled = True
        LblAddressTypeEdit.Visible = True
        CmdAddAddress.Visible = False
        End If

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    need to see what person2address query is - and also the recordsource to your subform

  11. #11
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Data Sample.zip

    Hi June7,
    I took out about 60,000 records to make small enough to upload, but should not conflict with what is in tables currently, I hope!

  12. #12
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    As far as a sub form, there is not a subform. I didn't want a subform on my main form. Takes up way to much room and I don't want to see every record for the person..... I know its not conventional but I like how this works currently just cant get it to requery the combos
    Thanks

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    regret I am out of time - need to go see a client. Perhaps June can take it back.

    Might be an idea if you explain what form(s) you have an issue with and how to replicate what you are trying to do

  14. #14
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thanks
    PersonFrm is the form. When db opens it goes to navigation, then hit Personal Contacts button, will take you to form in questions, related form is AddressFrm.
    Thanks again, sorry about confusion

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,818
    This does get confusing. There are two AddressType comboboxes. One on PersonFrm and one on AddressFrm. You have NotInList event code for both. Adding a record in AddressFrm creates new records in 2 tables, 3 if adding a new AddressType.

    Just requerying PersonFrm combobox doesn't do much. Even though combo list is updated, combo is UNBOUND and disabled. Code in PersonFrm Current event enables combo and sets its value with DLookup. Need to execute that same code after new address data is committed. Put PersonFrm Current event code into a sub behind PersonFrm (don't use Private qualifier) then it can be called by PersonFrm current event:

    Private Sub Form_Current()
    Settings
    End Sub


    And it can be called by AddressFrm like:

    Private Sub Form_Close()
    Forms!PersonFrm.Settings
    End Sub


    Tested and works.


    Note: Might want to change spelling of "Perferred" to "Preferred" in "Make this the Perferred Address"
    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.

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

Similar Threads

  1. Requery Combo Box from second form
    By blueman in forum Forms
    Replies: 14
    Last Post: 11-16-2015, 12:03 PM
  2. Requery a subform form a combo box in a main form!
    By lewisoh in forum Database Design
    Replies: 2
    Last Post: 09-10-2015, 07:23 AM
  3. Replies: 7
    Last Post: 07-01-2015, 10:29 AM
  4. Requery split form data button
    By Evans2 in forum Forms
    Replies: 3
    Last Post: 09-08-2014, 08:59 PM
  5. Replies: 6
    Last Post: 01-13-2010, 02:41 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