NotInList not updating

[daniels012]

I have a Subform that I enter products. If the Product is not there it asks if you would like to add it. When yes is pressed, it opens a form called "AddProduct". You then add the product and close the form. It takes you back to the control you entered the product in originally. Then when you go to type the new product... the message box appears again???? If I hit no on the message box... the new item appears and I can go about my business. This would be fine if I was the only one using this form.

Once I enter the new product, It will not requery or refresh. What am I missing??

I know I am green when it comes to coding... But I really need some help.
I am not sure how to send a zipped file, but I can do that if needed?

Thank You,

Michael

 

[PHV]

You then add the product and close the form
Just after closing the form you may add code like this:
DoEvents
Me![name of combo].RowSource = Me![name of combo].RowSource

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[FancyPrairie]

Check out the function I included in this thread thread702-931558

 

[daniels012]

Hello PHV,
Where would I enter this??

Here is a sample of my code I have now:
Private Sub ProductID_NotInList(NewData As String, Response As Integer)
If MsgBox("The Product " & NewData & " you entered, does not exist yet." & vbCrLf & vbCrLf & "Do you wish to add it?", _
vbQuestion + vbYesNo) = vbYes Then
Response = acDataErrContinue
DoCmd.OpenForm "AddNewProductsFrm", acNormal, , , acFormAdd, , NewData
Else
Response = acDataErrAdded
ProductID.Undo
DoCmd.Save
ProductID.Requery
End If
End Sub
By the way... I cannot open in Dialog (which someone suggested)or my program locks up!

Michael

 

[FancyPrairie]

I think you might have things turned around. Try this:

Private Sub ProductID_NotInList(NewData As String, Response As Integer)
    If MsgBox("The Product " & NewData & " you entered, does not exist yet." & vbCrLf & vbCrLf & "Do you wish to add it?", _
    vbQuestion + vbYesNo) = vbYes Then
               DoCmd.OpenForm "AddNewProductsFrm", acNormal, , , acFormAdd, , NewData
       Response = acDataErrAdded 
       Else
        Response = acDataErrContinue        ProductID.Undo
        DoCmd.Save
        ProductID.Requery
    End If
End Sub

Also, I think you should open the form as acDialog so that your code does not get executed until the form closes. Otherwise the Response=acDataErrAdded will get executed prior to the record being added in the form.

 

[daniels012]

I'll try it!
Where do I put the acDialog? Also Maybe it is messing something up because I also have:

Private Sub ProductID_GotFocus()
ProductID.Undo
DoCmd.Save
ProductID.Requery
End Sub

In the Got Focus of my Control?

Thank You,
Michael

 

[TheAceMan1]

[blue]Where do I put the acDialog?[/blue]

[blue]DoCmd.OpenForm "AddNewProductsFrm", acNormal, , , acFormAdd, [purple][b]acDialog[/b][/purple], NewData[/blue]

See Ya! . . . . . .