Not In List Update/Add problem

[eaglei66]

Ok, here goes. I am new to this site and I am hoping you can help me, which im sure somebode can. I have messed around with Access a bit but i have a task that I need some help with. So, here goes.

I have several tables, some are lookup tables and some are the main database tables. I have a form that has a data entry spot on it that is using a look up table, and what I need it to do is be able to recognize when an entry is not listed in the lookup table and then open another form or a message box telling them this and asking if they want to add this entry to the lookup table and if so either add it automatically and move on or open that lookup tables form and allow for a new data entry and then move on.

Is this possible? Please help!

Thanks in advance!

Frustrated in Dallas!
Eaglei66

 

[ColinEssex]

I think what you're saying is that you have a ComboBox and want to add what the user types in to the underlying table if its NotInList.

If so, have a look at this thread - this has been covered many times.

http://www.access-programmers.co.uk...=&threadid=21581&highlight=combobox+notinlist

Col

 

[eaglei66]

Combo Box Not In List Event

Yahoo!!!
Thank YOU! Thank YOU! Thank YOU!
Thank you very much for your help!!.
your Code Didn't exactly work, but it sure did get me headed in the right direction and i finally got it to work.

Here is the code I ended up with.

---------------------------
Private Sub LocationID_NotInList(NewData As String, Response As Integer)

Response = MsgBox("The Cellar Location you have entered is new and not in this list." & vbCrLf & vbCrLf _
& "Would you like to add it?", vbQuestion + vbYesNo, "New Cellar Location?")
If Response = vbYes Then
Set db = CurrentDb
Set rs = Recordset
Set rs = db.OpenRecordset("locations")
With rs
.AddNew
.Fields("btllocation") = NewData
.Update
End With
Response = acDataErrAdded
Else
Me.Undo
Response = acDataErrContinue
End If

End Sub
--------------------------------------

I really appreciate your help. I was able to read some other threads on this forumn and get some other help as well.

I'm not sure why I had to use different verbage with the db. and set stuff but I am just glad I finally got it working the way i want it !

Now i can go on to my next problem...
but more on that later....

so, look for my next thread and hopefully you can get me in the right direction for it...

Eaglei66