Add a new record is not immediately available on form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 wrote some ideas off the top of my head without really considering it as
carefully as I should have. I did some more investigating. When you open
the form (at the Not in List event) you need to open it in dialog mode, which
suspends the rest of the code until the form is closed, as I understand it:

DoCmd.OpenForm "frmAddNew", , , , , acDialog
Response = acDataErrAdded

The next line of the code confirms that the new information is now on the
list, and the Not in List event no longer fires. I can see no need to
requery, although my test situation was somewhat different from yours.

By the way, there are all sorts of options for this situation, which you can
find by typing: Access "Not in List" "Error Message" (with the quotes) into
Google groups.
 
Thank you - this did it!

BruceM said:
I wrote some ideas off the top of my head without really considering it as
carefully as I should have. I did some more investigating. When you open
the form (at the Not in List event) you need to open it in dialog mode, which
suspends the rest of the code until the form is closed, as I understand it:

DoCmd.OpenForm "frmAddNew", , , , , acDialog
Response = acDataErrAdded

The next line of the code confirms that the new information is now on the
list, and the Not in List event no longer fires. I can see no need to
requery, although my test situation was somewhat different from yours.

By the way, there are all sorts of options for this situation, which you can
find by typing: Access "Not in List" "Error Message" (with the quotes) into
Google groups.
Click to expand...
 
Back
Top