Add a new record is not immediately available on form

I believe you could use the combo box's Not In List event to open a small
data entry form for the purpose of adding the item. You could requery after
closing the form.

"LoriO" wrote:

I have a Table: tblPlanType
Query: qry_tblPlanType
Form: frm_tblPlanType
When I add a new record to the form, I have a FIELD (TDType) - (ComboBox)
It's record source is a Query based off of the Table: tblPlanType
ComboBox - Limit to List property is YES

I want to add a response to the combo box and if it is not in the List I
want to add it to the Table (tblPlanType) and be able to use it without
having to start over on the record I was adding.

The code below seems to partially work but I get an error:
The text you entered isn't an item in the list
How do I get the combo box to 'update immediately' after I have entered a
new item in the Source Table?

I have an Event Procedure on the "On Not in List" of the TdType Combo Box

*********Code I am using***************
Private Sub TDType_NotInList(NewData As String, Response As Integer)
Dim ctl As Control
Dim rst As DAO.Recordset
Dim db As DAO.Database

Set ctl = Me!TDType
If MsgBox("Value is not is list. Add it?", _
vbOKCancel) = vbOK Then

Set db = CurrentDb()
Set rst = db.OpenRecordset("qry_TblPlanType")

With rst
.AddNew
.Fields("IssueType") = NewData
.Update

End With
rst.Close
Set rst = Nothing
Set db = Nothing

Repsonse = acDataErrAdded

Else
Response = acDataErrContinue
ctl.Undo
End If
End Sub

I changed the code to Open a form if I choose vbOK,
I add the new record to the form, then Close it
I have an 'on close' event to the form to requery the Original
(frm_tblPlanType) form and I am still getting the error message: "The text
you entered isn't an item in the list "

I tried the requery on 'close' because when I tried it on the original code
- I get an error that says I must save the record before I can requery.

Do you have any examples of code that works with the 'requery' or can you
tell me where to put the code -

"BruceM" wrote:

I believe you could use the combo box's Not In List event to open a small
data entry form for the purpose of adding the item. You could requery after
closing the form.

"LoriO" wrote:

I have a Table: tblPlanType
Query: qry_tblPlanType
Form: frm_tblPlanType
When I add a new record to the form, I have a FIELD (TDType) - (ComboBox)
It's record source is a Query based off of the Table: tblPlanType
ComboBox - Limit to List property is YES

I want to add a response to the combo box and if it is not in the List I
want to add it to the Table (tblPlanType) and be able to use it without
having to start over on the record I was adding.

The code below seems to partially work but I get an error:
The text you entered isn't an item in the list
How do I get the combo box to 'update immediately' after I have entered a
new item in the Source Table?

I have an Event Procedure on the "On Not in List" of the TdType Combo Box

*********Code I am using***************
Private Sub TDType_NotInList(NewData As String, Response As Integer)
Dim ctl As Control
Dim rst As DAO.Recordset
Dim db As DAO.Database

Set ctl = Me!TDType
If MsgBox("Value is not is list. Add it?", _
vbOKCancel) = vbOK Then

Set db = CurrentDb()
Set rst = db.OpenRecordset("qry_TblPlanType")

With rst
.AddNew
.Fields("IssueType") = NewData
.Update

End With
rst.Close
Set rst = Nothing
Set db = Nothing

Repsonse = acDataErrAdded

Else
Response = acDataErrContinue
ctl.Undo
End If
End Sub