Hello my fellow Access developers and users
Categories for questions on this form
can be anything you want ...
major subjects like Math, English, Science, and History
or you can make them more specific.
When New Data isn't there,
it can be added, right away,
as its thought of.
hi, this is crystal
This is a Chemistry question,
and specifically, Organic Chemistry.
The category isn't filled out yet
The textbox part of a combo box
lets you type something new.
If "Organic Chemistry" was here,
it would be between
"Ones and Tens"
and "Parts of speech".
Its not in the list.
Do you want to add the New Data?
Yes
Now the new data is here.
This is a wonderful way for users to create data
that will be used for lookup and reference.
Let's go to the design view of the form
and see how this works.
This is the category combo box.
It's named CategID
because it is actually storing a long integer
even though it's showing text.
Here is the Category table:
the design view
and the datasheet view
Press Alt-Enter to show the Property Sheet if you don't see it.
Mine is floating instead of docked.
Click the Event tab in the Property Sheet
And now, in the Not In List event.
It says "[Event Procedure]"
That means VBA code is behind this form
Let's figure out what this procedure will be called.
It will be the control name
and then an underscore and the event
CategID_NotInList
Does that sound right?
Let's see.
Click the builder button, "..." to the right,
or press Ctrl-F2 for the builder,
and this takes you to the
VBA class module
that is stored behind this form.
The procedure declaration and parameters
were automatically created by Access
And there's the name, CategID_NotInList
NewData is what was just typed in --
Organic Chemistry, in this case.
Response gets set to a value
depending if there is still an error
or the value got added
End Sub was also added by Access.
Between the beginning and the end
is what to do.
This sub calls a custom procedure
named combo_NotInList
We're sending it the table name and field name,
which happen to be the same in this case,
and then the standard parameters for NotInList,
NewData and Response.
Right-click on combo_NotInList
and choose "Definition" from the shortcut menu.
This takes us to that procedure.
The reason this is written generically
is to make it flexible
so the NotInList event is quick to implement
whenever and wherever you want it.
In this procedure,
the tablename will be referred to as psTablename
and the fieldname, psFieldname.
"p" means parameter and "s" means string.
NewData and Response are called the same
as in the code behind the form for NotInList.
We could have changed the names here,
but I chose to leave them the same.
Ignoring errors isn't the best way to handle errors, but it is a way.
A string is dimensioned to hold an SQL statement,
and another string for a message.
Response is initialized to still be an error.
Then a message is constructed to ask the user
if they want to add a new record.
The new data they typed in is displayed
and they're asked if they want to add it.
Then MsgBox is used to show the message,
and yes/no buttons,
and a title called "Add New Data?"
If the user doesn't pick Yes, this code simply exits.
Before exiting, what you could do here is undo the entry.
But I like to keep it and let the user make corrections to it,
or delete it, if that's what they want to do.
Then comes code to construct the SQL statement.
We're going to insert into whatever table name was passed
And whatever fieldname was passed
is going to be set to the new data.
Debug.Print sSQL
writes the SQL statement that nwas constructed
to the Debug, or Immediate, window.
Very helpful if there's a problem.
And if there's not, it just did something extra.
You can comment this once everything is working ok
in your database with it.
Instead of dimesioning a database object,
or just using CurrentDb a couple times in the code,
this uses With and End With for CurrentDb.
The SQL statement is run: execute the SQL.
If a record was added,
then .RecordsAffected will be greater than zero.
If a record wasn't added, then it won't be.
If it was added, then the response will be data was added
and the user just continues.
Now we exit the sub
In summary, remember that
you can use the combo box NotInList event
to add data to the table that the list comes from
You can download the NotInList code, and lots more,
from MsAccessGurus.com
Links are in the video description.
If you're not building an Access application, what's stopping you?
Access takes information management to the next level,
and makes you more efficient.
If you'd like to connect with me, 1-on-1
and let me help you with your project,
I would love to help get you on the right path.
Go to MsAccessGurus.com
and I look forward to hearing from you
Thanks for joining me.
Through sharing, we will all get better.