Should I use NotInList for this application?

  • Thread starter Thread starter CAD Fiend
  • Start date Start date
C

CAD Fiend

Hello,

I have a combo box (cmbFirstName) on a form that is hitting a query
(qryFirstName) and then putting that selected (or typed by user) value
to the table field (name FirstName) on the table (tblUserInfo).

The query hits a look up table called (lulFirstNames). If the user
DOESN'T find the name they want in the combo box, they will just type it
in.

I would like to be able to "build" up the look up table's "vocabulary"
of names as the users use the form more and more over time.

What would you suggest? Would *NotInList* be a good canidate for this
application? How do I write the code, and WHERE do I put it? Please be
as specific as possible, I'm new to writing code for this type of
situation. And I DID read the help on this topic, but it is hard for me
to understand which way to go. I mean, it says you can go about this
with an *expression* or *macro* or *event procedure*, and I don't know
which one to go with.


TIA.

Phil.
 
Hi Albert,

Have you used this code? I'm not trying to be rude, but the reason that
I'm asking is because after doing some Googling I've seen many different
ways to approach this, and I just want to make sure I do the right one
(for my situation), that's all.

Thanks.

Phil.
 
Yes, that code is on the right track..and would be suitable approach....

About the only question here is do you want to prompt the user, or just have
the name added?
(probably good idea to prompt..since if you make a type-o, then you are in
trouble..since then all kinds of corrects and type-o would be added...

Code is about the best approach..and I would not try doing this with macros
for example....
 
Albert, see my comments in-line. Thanks for your responses. Phil.

Albert D.Kallal said:
Yes, that code is on the right track..and would be suitable approach....

About the only question here is do you want to prompt the user, or just have
the name added?
Click to expand...

I would rather have it so they get prompted. I will put some verbage urging the
user to be SURE that they have spelled the entry correctly.
 
I have tried to get the notinlist code to work and am having a heck of a time.

when running I get an error

Dim db as database
Compile error
User Defined tyhpe not Defined.

Basically I have a combo box that looks up the field diagnosticstudy in the
table diagnosticstudylist
This is the only field in the table.
If anyone could help add this to one of the sample codes so I can try it out.
Once this works I can apply throughout my program.

Thanks
 
Sounds as though you're using Access 2000 or 2002.

Database is an object in the DAO model, and by default, neither Access 2000
nor Access 2002 include a reference to that library.

With any code module open, select Tools | References from the menu bar.
Scroll through the list of available references until you find the reference
for Microsoft DAO 3.6 Object Library. Select it, click on the OK button, and
you should be okay.
 
Getting there I think.

I set Limit to list = Yes

Still getting error message "The text you entered isnt an item in the list"

Below is the code for event notinlist
Am I at least near the ballpark?
Thanks again.

Private Sub cbxAEName_NotInList(NewData As String, Response As Integer)
Dim db As Database
Set db = CurrentDb

'Add the new value to the field
db.Execute "INSERT INTO diagnosticstudylist(diagnosticstudy) VALUES (""" &
NewData & """)", dbFailOnError

'Tell Access you've added the value
Response = acDataErrContinue

db.Close
Set db = Nothing

'Add the new field to the table
db.Execute "ALTER TABLE diagnosticstudylist ADD COLUMN " & NewValue & "
diagnosticstudy", dbFailOnError

'Tell Access you've added the new field
Response = acDataErrContinue

db.Close
Set db = Nothing

'Ask the user if they want to add to the list
If MsgBox("Do you want to add this value to the list?", vbYesNo +
vbQuestion, "Add new value?") = vbYes Then

'The user clicked Yes - add the new value
db.Execute "INSERT INTO diagnosticstudylist(diagnosticstudy) VALUES (""" &
NewData & """)", dbFailOnError

'Tell Access you've added the new value
Response = acDataErrContinue

Else

'The user clicked No - discard the new value
Me.Combo15.Undo
'Tell Access you've discarded the new value
Response = acDataErrContinue

End If

db.Close
Set db = Nothing

End Sub
 
What's the ALTER TABLE stuff? It sounds to me as though your database model
isn't normalized!

In any case, you want acDataErrAdded if you've added the record, not
acDataErrContinue
 
Back
Top