Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    NotInList or something else? (A2k3)

    I've been using an unbound combo box's NotInList event to add new records via a second data entry form. I'd like to skip using the second form but I still need to check to see if the name is already in the database. What's a good way to do that?

    E

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: NotInList or something else? (A2k3)

    I don't quite understand. The NotInList event occurs if the text entered by the user is not present in the Row Source of the combo box. Why would you need to check if the name is already in the database? If it were, the NotInList event wouldn't have occurred.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList or something else? (A2k3)

    Couldn't you set it as some sort of key that doesn't allow duplicates?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList or something else? (A2k3)

    My objective is use a single form to both verify that the data I'm entering is not already in the database and, if it isn't, add a new record using this same form. If there a relatively simple way to do this?

    E

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList or something else? (A2k3)

    Are you asking something like if the info already exists that it automatically pulls up all the info on that record? And if it doesn't exist you just continue to enter data?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: NotInList or something else? (A2k3)

    I have to echo Hans's question: if the item is in the list, it must be in the database, assuming the list is populated from the database. Perhaps if you clarify the problem we can answer your question. If you are using an unbound combobox to navigate to a particular record, then you can use the NotInList event to undo the combobox entry and move the form to a new record. Is that what you're trying to accomplish?
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList or something else? (A2k3)

    "you can use the NotInList event to undo the combobox entry and move the form to a new record"

    That's exactly what I'm after! Thanks a million.
    E

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: NotInList or something else? (A2k3)

    OK, do you need help with that? If you post your NotInList code, we can help you tweak it if necessary.
    Charlotte

  9. #9
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList or something else? (A2k3)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    Charlotte,

    Thought I was going to need help with it then ran across the following -- http://www.pacificdb.com.au/MVP/Code/NIL.htm -- and I was demystified. Here's the code I'm using:

    Private Sub cboFind_NotInList(NewData As String, Response As Integer)

    MsgBox "Record doesn't exist. Creating new record."
    Me.cboFind.Undo
    DoCmd.GoToRecord , , acNewRec
    Response = acDataErrContinue

    End Sub

    Thanks so much for pointing me in the right direction. I knew there has to be a way to add a new record to the existing form but I had been spinning my wheels. My stumbling block was that last line of code. It didn't make sense to me that it came after the DoCmd, in much the same way it didn't make sense to me that you defined your OrderBy and only then have OrderByOn. I'm learning, tho some days it feels more like knocking my head against the wall. You can teach an old dog new tricks...but sometimes we're just incredible sllloooowwwww.

    E

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: NotInList or something else? (A2k3)

    I think the most confusing thing about NotInList code is that you MUST supply a response, regardless of whatever else you do. Since your combobox is unbound, the move to a new record doesn't affect it, but trying to exit the routine means that whatever response you have provided will then be processed. Once you get that firmly in mind, it starts to make sense. You could have populated the response argument earlier in the code, but there wasn't any need to do so.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •