Not In List Event Question?

[mrmims]

Hello,



I have a form (FRM_Inventory_ADD) which adds components to cabinets. If some one enters a Part Number in ComboBox "cboComp" which is not in the list, I am running a "Not In List" event to open another form (FRM_Components_ADD) to create the new item.

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


Dim Msg As String
Dim CR As String


    CR = Chr$(13)


    ' Exit this subroutine if the combo box was cleared.
    If NewData = "" Then Exit Sub


    ' Ask the user if he or she wishes to create the new partnumber.
    Msg = "'" & NewData & "' is not in the list." & CR & CR
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
       ' If the user chose Yes, start FRM_Components_ADD in data entry
       ' mode as a dialog form, passing the new PartNumber in
       ' NewData to the OpenForm method's OpenArgs argument. The
       ' OpenArgs argument is used in Components ADD form's Form_Load event
       ' procedure.
       DoCmd.OpenForm "FRM_Components_ADD", , , , acAdd, acDialog, NewData
       
    End If
    
End Sub

When FRM_Components_ADD opens, this runs On Load to enter the new part number automatically:

Private Sub Form_Load()


   If Not IsNull(Me.OpenArgs) Then
      ' If form's OpenArgs property has a value, assign the contents
      ' of OpenArgs to the PartNumber field. OpenArgs will contain
      ' a PartNumber if this form is opened using the OpenForm
      ' method with an OpenArgs argument.
      Me![PartNumber] = Me.OpenArgs
   End If


End Sub

Everything works great EXCEPT when I close "FRM_Component_ADD" and go back to the original form. How do I clear the ComboBox "cboComp" and then Refresh the form and then have the new Part Number already selected in "cboComp"?

I tried:

PropertySet [cboComp] = Null

to clear the ComboBox, but i get errors.

Any ideas?

Thank you!

[ItsMe]

I would probably do a save just before opening the other form to avoid warnings.

if me.dirty then me.dirty = false
DoCmd.OpenForm "FRM_Components_ADD", , , , acAdd, acDialog, NewData


Then, have a click event to save the record (again) and close FRM_Components_ADD. You may want to do some other validation with the user. Give them a chance to edit the value and or cancel.

After your validation, I would do two saves. One save for the pop up form and another save for the original form to avoid issues with Access warnings.

if me.dirty then me.dirty = false
if forms!FRM_Inventory_ADD.dirty then forms!FRM_Inventory_ADD.dirty = false
forms!FRM_Inventory_ADD.ComboName.Requery
forms!FRM_Inventory_ADD.ComboName.Value = Me![PartNumber]
docmd.close acform, "FRM_Components_ADD"

[mrmims]

Hello,

Sorry for the delay in response. I tried the dirty method:

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


Dim Msg As Variant
Dim CR As Variant


    CR = Chr$(13)


    ' Exit this subroutine if the combo box was cleared.
    If NewData = "" Then Exit Sub


    ' Ask the user if he or she wishes to add the new customer.
    Msg = "'" & NewData & "' is not in the list." & CR & CR
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
       ' If the user chose Yes, start FRM_Components_ADD in data entry
       ' mode as a dialog form, passing the new PartNumber in
       ' NewData to the OpenForm method's OpenArgs argument. The
       ' OpenArgs argument is used in Components ADD form's Form_Load event
       ' procedure.
       
    DoCmd.OpenForm "FRM_Components_ADD", , , , acAdd, acDialog, NewData
     
    If Forms!FRM_Inventory_ADD.Dirty Then Forms!FRM_Inventory_ADD.Dirty = False
    Forms!FRM_Inventory_ADD.cboComp.Requery
    
    End If
    
End Sub

When I close FRM_Components_ADD to go back to the FRM_Inventory_ADD the process starts all over and it immediately asks me if I want to add the part number which does not exist. If I click yes it goes back to the component ADD form, and if I click no I get an error message saying "The item is not in the list" and goes to my VBA code with "Forms!FRM_Inventory_ADD.Dirty = False" highlighted as the problem child.

The combobox "cboComp" which I originally enter the Part Number never clears down for me to requery.

Any thoughts?

Thank you again for your help

[ItsMe]

Why are you using requery before you even save a new record from the new form? I would not have this line in the Not In List event, if that is what I am looking at.
Forms!FRM_Inventory_ADD.cboComp.Requery

You should save the current form before opening the other form. So place this line before the docmd
If Forms!FRM_Inventory_ADD.Dirty Then Forms!FRM_Inventory_ADD.Dirty = False

Actually, you do not need the fully qualified name here. You can use the Me. shortcut.
If Me.Dirty Then Me.Dirty = False

Use the fully qualified name when the code is saving another form (the other form).