Add New Data with NotInList VBA for Combo box

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.

00:44 - 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.

01:29 - 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.

02:33 - 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.

03:39 - 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.

04:23 - 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.

04:59 - 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.

05:40 - 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.

06:13 - 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. .