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

    Not in list event to open form to combo box field

    Hi all,
    I have a combo (CboAddressType) with a not in list. There is an sql statement in it to put it in a table, then open up the form i want to link criteria too to fill in an address. Below is code but when i run this it opens form (AddressFrm) but i get a not on list msg, have to select from the list which its there? Why is this not recognizing that this was just put in the table. Form is run off a query but before form opens up, it should not qry correct?

    I am looking to have this put my TXT in table, then open form to that record i just added so i dont get this not in list dialog when the form opens up? I cant figure it out. Some where it needs to requery the qry before it opens up the form? I dont know. Any assistance would be just fantastic!
    Thanks
    Dave

    Code:
    Private Sub CboAddressType_NotInList(NewData As String, Response As Integer)
    Dim strsql As String, x As Integer
        Dim LinkCriteria As String
       
    x = MsgBox("Address Type is not in Current List, Would you Like to Add?", vbYesNo)
    If x = vbYes Then
        strsql = "Insert Into AddressTypeTbl ([AddressType]) values ('" & NewData & "')"
        'MsgBox strsql
        CurrentDb.Execute strsql, dbFailOnError
        LinkCriteria = "[AddressType] = '" & Me!CboAddressType.Text & "'"
        
        DoCmd.OpenForm "AddressFrm", , , , , , Me.CboAddressType
        Forms!AddressFrm!CboPerson = PersonID
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
    End Sub


  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You have to Requery the combobox to refresh the list with the new item.

    https://blueclawdatabase.com/notinlist-event-code/
    https://www.fontstuff.com/access/acctut20.htm
    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

    Not in list event to open form to combo box field

    Data.zip

    Hi all,
    I made some changes to code but still cannot get this to work correctly? I have attached db and it opens to PersonFrm. When I want to add a new Address Type (CboAddressType), it does the NotInList event, Opens form (AddressFrm1) and I get a not in list msgbox. This shouldn't happen as I am not linking any data for this, just opening up form to add a new record. I cant seam to be able to get rid of this message no matter what I do? Also, when I do add the new record, I cannot figure out where to put the Refresh code that I have on the close button? When I press close, it closes form but does not refresh the open form and I get a NotInList event fired again? How on earth can I solve this?
    Thanks
    Dave

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Tables should link on primary/foreign key fields. Fields designated as primary key should be values saved as foreign key. AddressTypeID is designated as PK yet you save AddressType.

    If you want to add a new address type then should open AddressTypeForm, not AddressFrm1. Looks to me like you are using combobox for selecting a complete address, not just address type. Two tables are involved when you add a new type. Should probably be using a subform for the address data entry/edit. AddressType combobox should be on that subform. Combobox RowSource should just be AddressTypeTbl.
    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
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    thanks June7,
    I wasn't real sure on how to do this but will most likely put an add button on it and block the NotInList event. I want to be able to select the address types for the Person and have the address info auto fill in. That part works pretty well so far but adding a new one has been a test for sure. I believe I will just go with a button for that as I try new things that seams to work as well as anything. I may even be able to fire it with the NotInList? Havent tried yet...

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why not save CSZID into AddressTbl instead of repeating this data?

    Why would address 'types' be specific to person? I envision types as generic descriptors like Home, Office, Warehouse, POBox, RV Park, etc, which would be an attribute of the address.

    Since AddressTbl is related to and dependent on PersonTbl, it should be in a subform. The subform container master/child links will synchronize records. Have combobox to select type on the subform.

    "Preferred" is misspelled as "perferred" - checkbox name and text in label - corrected in image.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	17 
Size:	49.3 KB 
ID:	39381
    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.

  7. #7
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi June7,
    Please let me try to explain this better. I travel for a living and stay at various addresses as well as a lot of my contacts that I send packages to. I need to store these addresses for later use. On my form, I don't want to see all the addresses but just one and be able to change it with a combo box. Other wise I will have all these sub forms on my primary form as this is not the only thing that goes on this form. What I am trying to accomplish is to show one address on my form that is preferred when on record and be able to change that to a different address by selecting it from a combo. I need to edit, add and such to the addresses also. I need to be able to tick the IsActive so that only the Active addresses show up in my combo. This is why I used a query for the form. If I use a subform then I have a form that is huge to say the least and trying to keep it a very simple form with the max amount of available data on it.

    There may be a better way of doing it but I don't know how. The way I did it on my form was the way I wanted to display it so I only see one address at a time and that I can switch between addresses by the combo. I have lots more stuff for this form to add still so form space is essential. Its the same as when I add phone numbers, I want to display the preferred phone type and the number and select in the same way by combo of Type being Home, Cell, Work..... which I haven't added to this form yet as trying to solve this issue before adding to it. By using types, I will not be storing home, work, and such types of addresses 20000 times in my db, once I have home I shouldn't have to store it more than once, just get it from the table?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I use Tab control to organize lots of info into a small form area. I have multiple subforms, each on its own page of Tab control.

    Your current combobox is causing issues because two tables are involved for adding a new address type. Perhaps you need to use cascading comboboxes in the procedure to select address record. Select address type in first combobox then another combobox of addresses will be filtered by type selected in first.
    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.

  9. #9
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thank you June7,
    I appreciate it
    Dave

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

Similar Threads

  1. Replies: 6
    Last Post: 12-03-2017, 02:23 PM
  2. Replies: 1
    Last Post: 09-15-2017, 07:57 AM
  3. Replies: 2
    Last Post: 03-13-2017, 07:54 AM
  4. Replies: 15
    Last Post: 07-22-2014, 07:32 PM
  5. Replies: 2
    Last Post: 03-26-2014, 07:30 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