How to add data in "event not in list" combo box fr a query source

[budich]

Hi Guys,

Does anyone knows how to make “event Not In List” for the data which were come from a query.
Lets make me clear this :
1. I have 1 combo box which it’s data comes from a query, combo box is fill the field contName in my batch entry form.
2. The query is comprise of 2 table : Container and Owner. In container the table is very simple :
- ContrID (autonumber)
- Contname
- Size
- Type
- OwnID
And the table linked by Owner’s table which are comprise of :
- Own ID
- Owner name
- Etc.
The query has a link in OwnID.
3. Normally, when I fill the contname (combo box) in my batch entry form (say from table : batchentry), it will show :
Contname ; Size ; Type ; Ownername
When I choose the contname it will shows automatically size, type and ownername

4. When The user cannot find the required container (event not in list), it will go to the command below :


Private Sub ContrID_NotInList(NewData As String, Response As Integer)

Dim strmessage As String
Dim dbs As DAO.Database
Dim rsttypes As DAO.Recordset

strmessage = "do you want add a data '" & NewData & _
"' in the container list?"

If Confirm(strmessage) Then
'add new data in table container
Set dbscontacts = CurrentDb()
Set rsttypes = dbscontacts.OpenRecordset("container&quot
rsttypes.AddNew
rsttypes!CONTNAME = NewData
rsttypes.Update
Response = acDataErrAdded 'requery the list.

Else

Response = acDataErrDisplay 'display the error.

End If
End Sub

5. the contname can be added to the table and it will have an error says :THE MICROSOFT JET DATABASE ENGINE CANNOT FIND A RECORD IN THE TABLE ‘OWNER’ WITH THE KEY MATCHING FIELD(S).
6. I think my procedure is wrong and HOW TO ADD ALSO FOR THE SIZE, TYPE AND OWNER NAME FIELD AS WELL.

Thank you in advance,

 

[hsitz]

The Access Developer's Handbook, Vol 1, by Getz and Litwin has some canned code for handling this smoothly. Probably to much to describe here, but that would be an excellent resource to turn to. If you don't have it, take a look at it at the bookstore. I think every Access developer should have it.

You can get some information and some code at their website:

http://www.developershandbook.com

-- Herb

 

[budich]

hsitz,

Thank's alot your reply is helpfull
see you

 

[hsitz]

Upon looking closer at your question, I'm not positive the ADH book answers it EXACTLY. But there is a comprehensive section on "Taming the 'not in list' event" that should help you in solving your problem. -- Herb

 

[budich]

Yes, I need "not in list" only.
thank you