Access 2007 Tutorial 5.4. Inserting into multiple fields using a Combo box and NotInList Event

You can work along with this file:
http://www.djdates.com/database-with-forms.accdb

Code can be found here:
http://www.djdates.com/2011/07/26/

In this video:

On Not In List event
Variables & Constants
msgbox()
if-else statement
Arrays
Split()
DoCMD.RunSQL
UBound()
Remaining Issues

Category:

Education

Tags:

• Access
• Access 2007
• Access 2010
• NotInList
• event
• VBA
• combo box
• combobox
• msgbox
• sql
• runsql
• ubound
• split
• database
• tutorial
• howto
• tips & tricks

[giorgio89789]

Can you do a video on multiple instances of tables in the relationship window. I know that we can make a relationship to the same field multiple times to one table by creating another instance of the table. My question is, do we have to reconnect all the relationship connections of the instance too?

[djdates]

@giorgio89789 Are you talking a table that exists only once in the database, but multiple times in relationship design? If I drag over a table again into relationship design, and use that instead, when I do a relationship report, use query design, or leave and come back into relationship design view, it's as if I created the relation on the original. I can also create multiple relations to single field without duplicating a table in that view, so I am a little confused as to what the issue is.

[dhyskRacer]

Great video, my issue is this doesn't work when you past in multiple records at a time in a form. Is there a way to 'captur' the not in table error and perform an action based on that? I also have to capture an error based on an index error as well. I figured the first would lead me to the second.

[djdates]

@dhyskRacer Remember, a form is a front-end for the table, not the table itself. Notice how we tell the form to update the list based on the new data in the table (acDataErrAdded)? Three things to try: 1.) find a form event that will fire before the insert/update (use msgbox's and experiment.) 2.) Use Access 2010 data macros, which add event-like functionality at the table level. 3.) Reexamine how data is added to the database. Use a staging table or external data connection with some SQL.

[giorgio89789]

Thank you so much for answering my question. Honestly, I didn't think you were going to see my question. This really helped me.

[djdates]

@giorgio89789 Great! Keep in mind, there is always more than one way to do this kind of thing. A variation on this has Access opening a new form for the user, populating one field from the combobox, but allowing the user to fill out the rest of the fields in the form. For examples, google: docmd openform newdata

[pisethnam]

Good !