Not in list - Access Developer Forum - Computer Learning Zone


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Not in list

Carl Bush

3 years ago

I have a form QuoteF with a sub form quotedetailsf to produce quotes for customers. I pick products from the productT using a combo box on the sub form if the product entered is not in the list I use the code below

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

     If MsgBox("This is not in the list would you like to add it", vbYesNoCancel) <> vbYes Then
        Cancel = True
        Exit Sub
    End If

    DoCmd.OpenForm ("ProductF")
    DoCmd.GoToRecord , , acNewRec
    Forms![ProductF]![ProductCode] = NewData
    DoCmd.GoToControl "ProductCategoryID"

this works fine to add the new product but when I close the product form I cant get the productcombo on my sub form to refresh and pick the product ive just added can you point me in the right direction
Regards
Carl

	Kevin Robertson @Reply 3 years ago
Response is required after NewData is added. `Response = acDataErrAdded`

	Carl Bush OP @Reply 3 years ago
Hi Kevin Thanks very much for your extremally quick response I have added the line of code you suggested after the NewData but it doesn't make any difference when I close the product form the combo box still has the original text but the record is still dirty I cant figure out how to refresh the list in the combo box so the new record is added to the list. Regards Carl

	Brian Crawford @Reply 3 years ago
As you are adding the new product, you need to capture the ID for that new product so that you can then set the control to that new ID value. The new record ID won't be available until after you save it. After the Forms![ProductF]![ProductCode] = NewData add something like: DoCmd.RunCommand acCmdSaveRecord newRecordID = Forms![ProductF].Recordset.Fields("ID").Value

	Carl Bush OP @Reply 3 years ago
Hi Brian thanks very much for your reply sorry its taken a while to respond I've been tied up on other things. I tried your suggestion but it still didn't work. I did find some code on the web which seems to work

	Kevin Robertson @Reply 3 years ago
Does this work for you? Private Sub cboProducts_NotInList(NewData As String, Response As Integer) If MsgBox("""" & NewData & """ is not in the list would you like to add it", vbYesNoCancel) = vbYes Then CurrentDb.Execute "INSERT INTO ProductT (ProductName) " & _ "VALUES(""" & NewData & """)" Response = acDataErrAdded Else cboProducts.Undo Response = acDataErrContinue End If End Sub

	Carl Bush OP @Reply 3 years ago
Hi Kevin thanks very much for the code you suggested very kind of you and sorry for the delay in replying I have been crazy busy on other things but I have now had a chance to try it and it does work but it not exactly as I want. I have gone a different way and I will post the method I used later and would be extremally interested in your opinion if you have time to look it over

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View