NotInList Problem

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

Guest

I have a combo box on my main form which displays a selection list from
another table. The combo box is set to "Limit To List". On the NotInList
event, I have the following code:

Private Sub Purchase_Order_Property_Code_NotInList(NewData As String,
Response As Integer)
[Purchase Order Property Code].Undo
Response = 0
DoCmd.OpenForm "New Properties"
[Purchase Order Property Code].Requery
End Sub

This displays my "New Properties" form, allowing the user to add a new
record. After PgDn to save the record, then closing the "New Properties"
form, the combo box does not display the newly added record in the selection
list. If I now close the main form and re-open it, the new record is
displayed in the selection list.

Reading through other messages has led me to believe that I must save the
record at some point for it to be displayed. Please help. Thanks!
 
The problem is that the Requery happens too soon. Open the "New Properties"
form in Dialog mode so that the rest of the code in that sub doesn't run
until you've closed the form:

DoCmd.OpenForm "New Properties", WindowMode:=acDialog
 
Adding to what Doug wrote:

DoCmd.OpenForm "New Properties", WindowMode:=acDialog
Response = acDataErrAdded
don't need this ==> [Purchase Order Property Code].Requery

Brian

Douglas J. Steele said:
The problem is that the Requery happens too soon. Open the "New Properties"
form in Dialog mode so that the rest of the code in that sub doesn't run
until you've closed the form:

DoCmd.OpenForm "New Properties", WindowMode:=acDialog


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


hfreedman said:
I have a combo box on my main form which displays a selection list from
another table. The combo box is set to "Limit To List". On the NotInList
event, I have the following code:

Private Sub Purchase_Order_Property_Code_NotInList(NewData As String,
Response As Integer)
[Purchase Order Property Code].Undo
Response = 0
DoCmd.OpenForm "New Properties"
[Purchase Order Property Code].Requery
End Sub

This displays my "New Properties" form, allowing the user to add a new
record. After PgDn to save the record, then closing the "New Properties"
form, the combo box does not display the newly added record in the
selection
list. If I now close the main form and re-open it, the new record is
displayed in the selection list.

Reading through other messages has led me to believe that I must save the
record at some point for it to be displayed. Please help. Thanks!
Click to expand...
Click to expand...
 
hfreedman said:
I have a combo box on my main form which displays a selection list from
another table. The combo box is set to "Limit To List". On the NotInList
event, I have the following code:

Private Sub Purchase_Order_Property_Code_NotInList(NewData As String,
Response As Integer)
[Purchase Order Property Code].Undo
Response = 0
DoCmd.OpenForm "New Properties"
[Purchase Order Property Code].Requery
End Sub

This displays my "New Properties" form, allowing the user to add a new
record. After PgDn to save the record, then closing the "New Properties"
form, the combo box does not display the newly added record in the selection
list. If I now close the main form and re-open it, the new record is
displayed in the selection list.

Reading through other messages has led me to believe that I must save the
record at some point for it to be displayed. Please help. Thanks!
Click to expand...


The essential part of this kind of thing is that your code
wait for the New Properties form to close by using the
WindowMode argument. You also are not setting the Response
value properly. If you set it to say the data was added,
then the requery is automatic.

There are other things are are commonly done in this kind of
situation especially related to the question of why you are
using the Undo method, but the basic outline is just these
two lines:

DoCmd.OpenForm "New Properties", _
WindowMode:= acDialog
Response = acDataErrAdded
 
Hi Gents,

This is great! One more small item and it will be perfect. In the "New
Properties" form, I seemed to have lost the ability to PgDn to save the
record. Tabing to the next record still works though. If I launch the form by
itself, the PgDn works. Before I added your new code, the PgDn also worked.
This is not a big deal at all, but if you can think of a quick fix let me
know. Thanks for everything. Very much appreciated!

Brian Bastl said:
Adding to what Doug wrote:

DoCmd.OpenForm "New Properties", WindowMode:=acDialog
Response = acDataErrAdded
don't need this ==> [Purchase Order Property Code].Requery

Brian

Douglas J. Steele said:
The problem is that the Requery happens too soon. Open the "New Properties"
form in Dialog mode so that the rest of the code in that sub doesn't run
until you've closed the form:

DoCmd.OpenForm "New Properties", WindowMode:=acDialog


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


hfreedman said:
I have a combo box on my main form which displays a selection list from
another table. The combo box is set to "Limit To List". On the NotInList
event, I have the following code:

Private Sub Purchase_Order_Property_Code_NotInList(NewData As String,
Response As Integer)
[Purchase Order Property Code].Undo
Response = 0
DoCmd.OpenForm "New Properties"
[Purchase Order Property Code].Requery
End Sub

This displays my "New Properties" form, allowing the user to add a new
record. After PgDn to save the record, then closing the "New Properties"
form, the combo box does not display the newly added record in the
selection
list. If I now close the main form and re-open it, the new record is
displayed in the selection list.

Reading through other messages has led me to believe that I must save the
record at some point for it to be displayed. Please help. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
You shouldn't need to PgDn to save the new data. Closing the form will be
all you need to do.

Here's what I have in one of my Dbs: (without error handling)

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

Dim ctl As Control

If MsgBox("Would you like to add this Payee?", vbYesNo) = vbYes Then
DoCmd.OpenForm "frmPayees", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctl.Undo
End If

End Sub

Private Sub Form_Load() 'frmPayees

If Not IsNull(Me.OpenArgs) Then
DoCmd.GoToControl ("Payee")
Me.Payee = Me.OpenArgs
End If

End Sub

When I close frmPayees, my combobox automatically displays the newly added
value.

HTH,
Brian
 
Back
Top