returning results from a form called from notinlist

[JC]

I am using the notinlist event to add records to a parts
table. The code is below. My question is what if the
user decides AFTER he gets into frmMiniParts form that he
does not want to add the record - how do I get that
information back to the notinlist subprocedure and set
Response to acDataErrContinue?

Thank you.

Private Sub PartNum_NotInList(NewData As String, Response
As Integer)
Dim i As Integer
Dim Msg As String

If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not currently in the list."
& vbCr & vbCr & "Do you want to add it?"
i = MsgBox(Msg, vbQuestion + vbYesNo, NewData)
If i = vbYes Then
DoCmd.OpenForm "frmMiniParts", , , , acFormAdd,
acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

 

[Neil]

Hello JC,

There may be an easier way to do this, but the only way i have found that
works is this. If the user has confirmed that they want to add the number,
proceed to open the form as you have done but dont pass acDataErrAdded as
the response. Undo the field and set the response to acDataErrContinue (see
below) . As far as the user is concerned they are adding a new part with all
the necessary details in the new form (with the form already on a new record
and the part number filled in for them). As far as Access is concerned, the
user has undone the changes made and will retry to add new data. What you do
now is when the user inserts/updates a mani part record or when the mini
part form is closed, check to see if the main form is open and requery the
part combobox. For example, the NotInList event will change to:

If i = vbYes Then
DoCmd.OpenForm "frmMiniParts", , , , acFormAdd, acDialog, NewData
' You need to undo this field to be able to requery later
Me.PartNum.Undo
End If
' Response will always be continue regardless of the Button pressed in
the message box
Response = acDataErrContinue

In the MiniParts Close event you would use something like (this code will
only work with Access 2000 onwards):


Private Sub Form_Unload(Cancel As Integer)

' Check to see if the main parts form is open
If CurrentProject.AllForms("MainFormName").IsLoaded Then
' Requery the combobox
Forms!MainFormName!PartNum.Requery
End IF

End Sub

You can use this code in the after update event and after insert event as
well if needed to keep the combobox up to date. After the mini parts form is
closed, the PartNum combo is requeried with the new details now available
for selection. Just be aware that if the PartNum combo is being edited an
error will occur when Access tries to requery. Also, it would be wise to
inform the user to enter the new item. Once this has been entered they will
then be able to select this from the part num combo box.

HTH,

Neil.