NotInList Event

[rgreene]

Populate a lookup table on the fly

Hello All,

I have a database where I'm tracking the continuing education classes that employees have taken. In the database I have the course name as a lookup field that refers to tblCourseLookup. In this table all I have are the course names Some names are rather lenghty so I'm trying to make it so the class name can be entered from my main form it is automatically added to the tblCourseLookup table and be available to pick from a drop down next time I need it.
So right now my tblCourseLookup.coursename is completely empty. I want to go to my frmcontEd and enter 1st Aid in the course name field. Then go to the next employee record and choose the drop down and have 1st Aid listed....

Any Suggestions?

Thanks,
Rick

 

[Rich]

Not sure I fully understand what you're trying to do, but I assume you use a combobox to add / select from, in which case you can use the NotInList event to add to the look up table

 

[Mile-O]

Use the NotInList event of a combobox.

i.e.

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("MyTable")

If MsgBox(NewData & " is not in the selection provided. Would you like to add it?", vbQuestion + vbYesNo, "Unknown Course") = vbYes Then
    With rs
        .AddNew
        .Fields("MyField") = StrConv(NewData, vbProperCase)
        .Update
        .Close
    End With
    Response = acDataErrAdded
Else
    Response = acDataErrContinue
End If

Set rs = Nothing
Set db = Nothing

You'll need to set the LimitToList property to Yes, also.

 

[rgreene]

Thanks I think we're headed in the right direction. I copied the code into the Event procedure on the Not In List event. However it doesn't give me an error or anything. I enter in my course name that isn't in the list and I get the same thing prior to having the code. I figured I should at least get an error or something. If I need to clarify more let me know.

Thanks Guys!!!!

Rick

 

[rgreene]

I GOT IT!!!
I didn't see the note that said "You'll need to set the LimitToList property to Yes, also."

So it's working!!

You guys are awesome!!!!

Rick

 

[Mile-O]

Have a look at this:

 

[Mile-O]

Ah, okay, then...lol