Combo Box, Adding more data to ready values - the NotInList thing

J

Jack

I’ve created a Table records where one field had been designed to use Combo
Box tool and have the Row Source set to Value List. I created some ready
inputted data.

Then I created a Form record and I want the above field, when it comes to
entering data, should offer me the option of pulling down the Combo Box list
and select from the list, or if the data I am looking for is not in the
list, then I want to enter a new addition data, which should then end up in
the list for future.

I’ve found out that in order for it to work, I have to do something about,
while in Form design view, bring up Properties and under the Event tab, set
the ‘On Not In List’ to ‘Event procedure’ or some stuff like that.

But, I totally have no idea what to do. There don’t seem to be any
step-by-step details to help set it up.

I assume it works with all versions of Access, but just in case it actually
varies from one other, I’ll just point out that my Access is Access 97 as
part of the Office 97 package. (Windows 98).

Many thanks for your time.
 
W

Wayne Morgan

Whether or not you need the NotInList event depends on the settings in your
combo box. If the combo box is a single column combo box that pulls its Row
Source from the same table/field as it is bound to, then all you have to do
is set LimitToList to No.

While a combo box can be used this way, it usually isn't. It is usually used
to pull data from a "lookup table". In this case, you need to use the
LimitToList event to add data to this other table using code. To do this,
set the LimitToList property to Yes and set the NotInList event to [Event
Procedure] and click the ... button to open the code window at that
procedure. You will notice that the procedure as 2 parameters, NewData and
Response. NewData contains the data that you just typed into the combo box
(the data that needs to be added to the table) and Response is used by you
to tell Access whether you added the data and the combo box should update
itself or if you changed your mind.

You will find a sample of coding this at this link.

http://www.mvps.org/access/forms/frm0015.htm

You will, of course, have to adjust the names to match your combo box,
field, and table names.
 
J

Jack

Thank you for suggesting I find a sample coding at the link

But this is not very helpful. I’m not an expert. I’ve been trying to use the
sample code as an example but I don’t know how to get anything right.

If I enter a data that is not in the combo box list, I don’t want Access to
ask me if I want to add the new data that is not listed in the combo box to
be added to the combo box or stuff like that!

All I want is to type in a data that if is not listed in the combo box, it
then just accept it and enter the data into the combo box for future use.
Surely there must be something simpler than that?

I am not a programmer and have no idea how to program it.
your combo box. If the combo box is a single column combo box that pulls its
Row Source from the same table/field as it is bound to, then all you have to
do is set LimitToList to No.
used to pull data from a "lookup table". In this case, you need to use the
LimitToList event to add data to this other table using code. To do this,
set the LimitToList property to Yes and set the NotInList event to [Event
Procedure] and click the ... button to open the code window at that
procedure. You will notice that the procedure as 2 parameters, NewData and
Response. NewData contains the data that you just typed into the combo box
(the data that needs to be added to the table) and Response is used by you
to tell Access whether you added the data and the combo box should update
itself or if you changed your mind.
field, and table names.
 
W

Wayne Morgan

Yes, that can be done, you just need to remove some of the coding. However,
it is still going to require some coding. Taking the code from the link I
sent in my last message:

'************ Code Start **********
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Sub cbxAEName_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
*Dim strMsg As String

* strMsg = "'" & NewData & "' is not an available AE Name " & vbCrLf &
vbCrLf
* strMsg = strMsg & "Do you want to associate the new Name to the current
DLSAF?"
* strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

* If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
* Response = acDataErrContinue
* Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAE", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!AEName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

* End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
'*********** Code End **************The lines starting with apostrophes are
comment lines. They will be ignored. The lines I marked with asterisks can
be deleted to add the data without prompting. In the line

Set rs = db.OpenRecordset("tblAE", dbOpenDynaset)

replace tblAE with the name of your table. In the line

rs!AEName = NewData

replace AEName with the name of the field in your table that the new data
should be stored in. In the line

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

cbxAEName will be replaced with the name of the combo box on your form. This
line and the End Sub line should be created for you automatically when you
set the box in the Properties sheet to [Event Procedure] and click the ...
button. Use the ones created automatically, not the two here.
 
J

Jack

Yes, that can be done, you just need to remove some of the coding.
However, it is still going to require some coding. Taking the code from the
link I sent in my last message:

- SNIP -
add the data without prompting. In the line

- SNIP -

I followed everything word for word, letter by letter and it isn’t working
at all! I’m sorry but I can’t seem to get anything work. I’m not a computer
programmer, I’m not like you guys who seem to know how to set up codes and
stuff. It’s all Greek to me and I can’t figure out where am I going wrong,
in fact, I don’t ever know where to start. It’s easy for you because you
guys are into this sort of thing, but I am not. I feel like a
‘know-nothing-civilian’ in a room full of ‘know-everything-professionals’!

Surely there must be a very, very, very simpler code?

If my combo box shows a list that reads as…

Red
Blue
White

And if I can’t find what I am looking for, so I type in ‘Black’ then press
ENTER, bingo, the new data is now in the table and from then on the combo
box shows a list that reads as…

Red
Blue
White
Black

Surely there should be a very simple code, something like…

IF it is not in the list then add it to the list

Instead of all this…

Dim db AS DAO
Set db = CurrentDB
Rs.update
Else
ABC = ABC
ETC, ETC, ETC, ETC,
Whatever!!!

Which is where I haven’t the foggiest idea what it is talking about.
 
W

Wayne Morgan

The code is used to add data to a table that is the Row Source of a combo
box (or query based on the table). If you are using a Value List as the Row
Source (i.e. typing the values directly into the Row Source property in form
design view) then this won't work. While you can add things to the list,
they won't be "remembered" once you close the form.

I went back and reread your original message and see that you are using a
value list. I apologize for missing that the first time through and for the
confusion it has caused. To add things to the list, you will need to change
the Row Source Type to Table/Query and place the name of the table or query
in the Row Source, then the code will work. If you want to be able to sort
the data in the drop down (i.e. have it displayed alphabetically) you will
need to use a query based on the table.
 
Top