By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,639 Members | 2,262 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,639 IT Pros & Developers. It's quick & easy.

How should I use NotInList for this application?

P: n/a
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.

BTW, when I Googled "NotInList" there seemed to be many opinions on how
to do it.

TIA.

Phil.

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Phil:

Below is a KB article on the NotInList event. It shows two approaches to
using this event to add a record to a combobox.

http://support.microsoft.com/default...b;en-us;197526

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
"CAD Fiend" <pe******************@cox.net> wrote in message
news:42***************@cox.net...
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.

BTW, when I Googled "NotInList" there seemed to be many opinions on how
to do it.

TIA.

Phil.
Nov 13 '05 #2

P: n/a
Yes, NotInList would be the way to handle this. You would place the code in
the NotInList event of the combo box.

First, set the combo box to LimitToList = Yes and the On Not In List event
to [Event Procedure] then click the ... button to its right.

Example Procedure:
Dim db As DAO.Database, rst As DAO.Recordset
If Msgbox(NewData & " is not in the list, add it?", vbYesNo + vbQuestion,
"Name Not Found") = vbYes Then
Set db = CurrentDb
Set rst = db.OpenRecordset(lulFirstNames, dbOpenDynaset)
With rst
.AddNew
!FieldName = NewData
.Update
End With
Response = acDataErrAdded
rst.Close
Set rst = Nothing
Set db = Nothing
Else
Response = acDataErrContinue
Me.cboComboName.Undo
End If

NewDate and Response aren't Dim'ed because they are defined in the
procedure's header line.

--
Wayne Morgan
MS Access MVP
"CAD Fiend" <pe******************@cox.net> wrote in message
news:42***************@cox.net...
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.

BTW, when I Googled "NotInList" there seemed to be many opinions on how
to do it.

TIA.

Phil.

Nov 13 '05 #3

P: n/a
Thanks for the link, David, I will check it out.

David Lloyd wrote:
Phil:

Below is a KB article on the NotInList event. It shows two approaches to
using this event to add a record to a combobox.

http://support.microsoft.com/default...b;en-us;197526

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.

"CAD Fiend" <pe******************@cox.net> wrote in message
news:42***************@cox.net...
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.

BTW, when I Googled "NotInList" there seemed to be many opinions on how
to do it.

TIA.

Phil.


Nov 13 '05 #4

P: n/a
Thanks, Wayne, for the code. I will give it a go.

Wayne Morgan wrote:
Yes, NotInList would be the way to handle this. You would place the code in
the NotInList event of the combo box.

First, set the combo box to LimitToList = Yes and the On Not In List event
to [Event Procedure] then click the ... button to its right.

Example Procedure:
Dim db As DAO.Database, rst As DAO.Recordset
If Msgbox(NewData & " is not in the list, add it?", vbYesNo + vbQuestion,
"Name Not Found") = vbYes Then
Set db = CurrentDb
Set rst = db.OpenRecordset(lulFirstNames, dbOpenDynaset)
With rst
.AddNew
!FieldName = NewData
.Update
End With
Response = acDataErrAdded
rst.Close
Set rst = Nothing
Set db = Nothing
Else
Response = acDataErrContinue
Me.cboComboName.Undo
End If

NewDate and Response aren't Dim'ed because they are defined in the
procedure's header line.

--
Wayne Morgan
MS Access MVP

"CAD Fiend" <pe******************@cox.net> wrote in message
news:42***************@cox.net...
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.

BTW, when I Googled "NotInList" there seemed to be many opinions on how
to do it.

TIA.

Phil.


Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.