Have an 'Open Form if NotInList' event, but I have to type data again???

[PTRACER]

I have a form on my database that I use for invoicing.

It has a [ProductCode] combobox where I type in my product codes and when the correct one is selected, it populates the other fields (e.g. Product Description, Price etc.) If I want to add a new product, I type directly into the [ProductCode] combobox and if the item doesn't exist, it triggers the following NotInList event:

Private Sub ProductCode_NotInList(NewData As String, Response As Integer)
    
    'Suppress the default error message.
     Response = acDataErrContinue
  
    ' Prompt user to verify they wish to add new value.
    If MsgBox("Would you like to add this product to the database?", _
         vbOKCancel) = vbOK Then
         
        ' open form to populate the various fields and carry up a new piece of the record
        
   DoCmd.OpenForm "Products", , , , acFormAdd, acDialog, NewData
      Response = acDataErrAdded

    Else
    ' If user chooses Cancel, suppress error message
    ' and undo changes.
        Response = acDataErrContinue

    End If

End Sub

The "Products" form is a separate form which has further details, such as Unit Price, Supplier, Stock Levels etc.

However, it opens up "Products" as a blank record with (New) in the ID box, I end up having to type the ProductCode in again. I'd like to create the new record automatically, putting [ProductCode] from the previous form into [ProductCode] on the new form.

What do I need to add to the VBCode?

 

[pr2-eugin]

Pass the ProductCode to the Form you are opening via the OpenArgs argument. Then in the Form Open method just place it in the appropriate field..

Something like..

DoCmd.OpenForm "Products", , , ,acFormAdd, acDialog, [COLOR=Blue]productCode[/COLOR]

Then in the FormOpen method..

Private Sub Form_Open()
    If Len(Me.OpenArgs & vbNullString) >0 AND Me.NewRecord Then
        Me.[COLOR=Blue]prodcutCodeControlName[/COLOR] = Me.OpenArgs
    End If
End Sub

Change all Blue Bits..

 

[PTRACER]

Apologies, where does the FormOpen method go? Oh is that in the "On Open" section of the form properties of the form that opens up?

 

[pr2-eugin]

It would go in the "Products" Form's open method..

 

[PTRACER]

Right, something is breaking somewhere, it's throwing an error code at me. It says it can't open the new form because of this line:

" DoCmd.OpenForm "Products", , , , acFormAdd, acDialog, ProductCode"

 

[PTRACER]

I've somehow got rid of the error, but it's actually doing exactly the same as before (Not creating a new record with what I typed previously)