Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003

    Exclamation Unanswered: How do you add new entry to combo box using OnNotInList event

    The problem is my users are brainless and even though I tell them to just use their last names to assume their project records (their case files are filtered from a query of the last name field), they don't get it and sometimes use LastName plus first initial or they spell their names wrong. Then I get to hear "my records are gone" when they sign in. So to make this user proof I want to provide them only one opportunity to screw it up.

    I have three tables
    table 1 -Employee Info
    table 2 -Employee Name
    table 3 -Employee Project List

    Table 2 contains one column -Last Name (no dupes allowed). It is the "one" of the one to many relationships between the other 2 tables. Since the probablity is high that I could get more than one "Smith" or "Jones" I decided to use the Employee last name table as the bridge.

    New users fill out their information once in table 1 and it is here that I need to have the unbound combo box tied to the LastName column from table 2 be able to accept new data. This is my users one chance to spell their name correctly. Then when that employee wants to assume control of a project's records, they need only go to table 3 and find their name in the combo box. Since at this point they can't alter the spelling, they will be forced to get it right.

    I used MS Access Help to get code for the OnNotInList Event but I just can't seem to get it to work. It was also written for updating a value list and my combo isn't running on a value list.

    Any code you can offer to help would be great. My bug is coming at the line that says:
    ctl.rowsource = ctl.rowsource & ";" & new data

    I feel like a putz but I just can't get this to work.

    Thanks in advance

  2. #2
    Join Date
    Nov 2003

    You are obviously filling your ComboBox from the Last Name value located in Table 2 via a query (or do I assume wrong?) which then means that the ComboBox RowSourceType property is set to Table/Query. The code you provided is for a RowSourceType of Value List (not a good idea when the DB gets a lot of names).

    Fill the combo RowSourceType propery to Table/Query and basicly set the combo box up so that you can see the last names within it (Bound Column, Column Count, etc). Set the Limit To List property to Yes.

    In the OnNotinList event you might have code like this:

    Private Sub myComboName_NotInList(NewData As String, Response As Integer)
      MsgBox "Ther name you entered is not contained within the " & _
    	 "provided List. Please try again.", vbExclamation + vbOKOnly, _
    	 "Incorrect Name Supplied"
      Me.myComboName = Null
      Response = acDataErrContinue
    End Sub
    Self Taught In ALL Environments.....And It Shows!

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    If your users are that clueless consider taking the choice out of their hands - allow them to type in whqt ever the heck they likem and ignre it. Use their network logon instead. If you use that in lace of the name, then irresepective of what they type you will always see the authenticated logon id. . This thread has a nifty simple way of extracting the network logon, cheers PKStormy. A word of caution please resist the temptation to use the currentuser() function especially if you doubt the IQ of your users.

    The main downside that I can see is that you (or whoever is doing the assigniment) will then have to asign the userid to a project. perhaps a look up table might suffice. In fact that may be abonus as you can use the name but tie back to the user.


Posting Permissions

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