Major confusion with adding new records after NotInList event

[PTRACER]

Hi all,

I have a form for entering products into an invoice, I have a datasheet and I start typing the product code in the box and if it exists in the database, it autocompletes the field, then populates the other columns with the product description, price, etc.

If the product code doesn't exist, it asks me if I want to add the product.

This is where the problems start - firstly, I have to manually type in the product code again, I would like it to copy over automatically.

Secondly, once I've added the new product and closed the form, I go back to the Invoice and I have to delete the product code, select an existing product (else it brings up the "Not In List, would you like to add?" thing again), then delete that product and re-type the product code of the new records. It doesn't refresh the query despite having a Refresh in there.

Here are my various codes...pretty simple, but then I am a beginner at this stuff:

Firstly from my invoicing form - called PartsSubForm:

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

    Dim ctl As Control
    
    '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
 
    Else
    ' If user chooses Cancel, suppress error message
    ' and undo changes.
        Response = acDataErrContinue

    End If

End Sub

Private Sub ProductCode_GotFocus()

Me.Refresh

End Sub

Private Sub ProductCode_AfterUpdate()

Me.ProductDescription = Me.[ProductCode].Column(2)
Me.CostPrice = Me.[ProductCode].Column(3)
Me.SalePrice = Me.[ProductCode].Column(4)
Me.Quantity = "1"

End Sub

And from the Products form, aka, the form I use to view, add, edit and delete products:

Private Sub Form_Close()

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

End Sub

 

[RuralGuy]

If successfully added then the following:
Response = acDataErrAdded
...causes the ComboBox to Requery its RowSource and it will now find the new entry. You should open the next form with the acDialog argument to halt the code in the first form until you complete and close the next form. You should also pass the NewData to the next form in the OpenArgs argument and place it in the correct control on the next form in the OnLoad event.

 

[PTRACER]

The Invoice form stays open all the time, the Products form opens and closes separately. I added Response = acDataErrAdded to AfterUpdate() but it didn't do anything.

 

[RuralGuy]

Your NotInList code should look something like:

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

' 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
      [COLOR="Red"]DoCmd.OpenForm "Products", , , , acFormAdd, acDialog, NewData
      Response = acDataErrAdded[/COLOR]
   Else
      ' If user chooses Cancel, suppress error message and undo changes.
      Response = acDataErrContinue
   End If

End Sub

 

[PTRACER]

That's sorted, now I need to somehow force the combobox in the Invoice form to refresh on its own as I still have to delete the text (else it still says it's not in the list if I click off of it), click off of it or press Enter to accept the entry, retype it and then it's there.

 

[RuralGuy]

What I posted should do that for you. What is the SQL for the RowSource of the ComboBox and what to you have in the ColumnWidths property?

 

[PTRACER]

Row Source: SELECT Products.ProductID, Products.ProductCode FROM Products ORDER BY Products.ProductCode;

Column Count: 2
Bound Column: 1
Column Widths: 0cm;5cm

If I just select ProductCode in the Query, it displays the ID for some reason, so I have to have the ID and the ProductCode selected, and the ID there but hidden.

 

[RuralGuy]

When you "key in" a new value, is it ProductID or the ProductCode?

 

[PTRACER]

Product code. Which might be something like MSW7PRE-C.

 

[RuralGuy]

That should work. Do you want to post your db?

 

[PTRACER]

That should work. Do you want to post your db?

I would, but it's got a couple of hundred customers' names and addresses in it.

 

[RuralGuy]

HOw about creating a new db and import what is needed to demonstrate the issue and delete all but two of the Customers and then scramble them or Mickey Mouse them?

 

[PTRACER]

Hate to be a pain in the backside, but I backed up the database, closed it, made a copy, deleted all the records, put some dummy data in and your code fix works after all

I don't know why it didn't initially.

Many thanks for your help, you have made things so much easier for me!

 

[RuralGuy]

That's great! Whatever it takes to get it working is good, right? Have a good one.