Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2012
    Posts
    56

    Unanswered: Combobox notinlist event to add new record to table

    Hi all I have a form with a combo box on that I use to find a record on a form. When I get an entry that is not in the list I have code to do that.

    Code
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Private Sub CboDonorName_NotInList(NewData As String, Response As Integer)

    Response = AddNewToList(NewData, "tblDonorList", "Name", "Names", "frmNewDonorInput")

    End Sub
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Module Code
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Public Function AddNewToList(NewData As String, stTable As String, stFieldName As String, strPlural As String, strNewForm As String) As Integer
    On Error GoTo err_proc
    'Adds a new record to a drop down box list
    'If form name passed, then open this form to the newly created record

    'Declare variables
    Dim rst As DAO.Recordset
    Dim IntNewID As Long
    Dim strPKField As String
    Dim strMessage As String

    ' Display message box asking if user wants to add the new item
    strMessage = "'" & NewData & "' is not in the current list. " & Chr(13) & Chr(13) & _
    "Do you want to add it to the list of " & strPlural & "?" & Chr(13) & Chr(13) & _
    "(Please check the entry before proceeding)."

    If MsgBox(strMessage, vbYesNo + vbQuestion + vbDefaultButton2, "Add New Data") = vbYes Then
    Set rst = CurrentDb.OpenRecordset(stTable, , dbAppendOnly)
    rst.AddNew
    rst(stFieldName) = NewData 'Add new data from combo box
    strPKField = rst(0).Name 'Find name of Primary Key (ID) Field
    rst.Update
    rst.Move 0, rst.LastModified
    IntNewID = rst(strPKField)

    'if a form specified, then open the form with the primary key equal to the new record ID as the criteria
    If strNewForm <> "" Then DoCmd.OpenForm strNewForm, , , strPKField & "=" & IntNewID, , acDialog

    AddNewToList = acDataErrAdded 'Set response 'Data added'

    Exit Function

    Else
    AddNewToList = acDataErrContinue 'Set response 'Data NOT added'
    End If

    exit_proc:
    On Error Resume Next
    rst.Close
    Set rst = Nothing

    Exit Function

    err_proc:
    MsgBox "Error in Function: 'AddNewToList'" & Chr(13) & Err.Description, , "Function Error"
    Resume exit_proc

    End Function
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    When It comes back to the form I cant seem to requery the form to recognize the new record

    Any Help would be great thanks Bob

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Where, exactly, are you attempting to do the Requery?

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Oct 2012
    Posts
    56
    Hi thanks for the reply. when the procedure finishes it comes back to the comb box. but does not recognize the new record. What I want to do is requery and goto the new record. If I close the form and reopen it the new record is there.

    Thanks Bob

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So if you know you need ro requery the combo box, where is the code to force a requery. Say the combo is called mycombo then after adding the row to the underlying table use
    Code:
    mycombo.requery

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by healdem View Post
    So if you know you need ro requery the combo box
    That was the problem, I think! The OP was Requerying the Form, not the Combobox!

    So just so that this is clear to him...doing a Requery of the Form doesn't Requery the Combobox...that has to be done separately!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Missinglinq View Post
    That was the problem, I think! The OP was Requerying the Form, not the Combobox!

    So just so that this is clear to him...doing a Requery of the Form doesn't Requery the Combobox...that has to be done separately!

    Linq ;0)>

    ...seems its an ongoing problem:-
    http://www.dbforums.com/showthread.p...query-Comb-box
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •