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.
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
Self Taught In ALL Environments.....And It Shows!
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.