Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004

    Unanswered: Adding New Record thru combo box

    Does anyone knows what's going on here ?

    I have combo box on my main form and i'm using following code to add new record to combo box. Each time i add new record it adds to the table twice. For example if i put "XYZ" in combo box it would add new value to the combo box but when i go back in the table to see the value i will see XYZ twice as two different record.

    Private Sub cboSAENo_NotInList(NewData As String, Response As Integer)
    Dim strMsg As String
    Dim strSql As String
        strMsg = "'" & NewData & " ' is not in the SAE No list. "
        strMsg = strMsg & "Would you like to add it?"
        If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, _
                         "New SAE No") Then
                        Response = acDataErrContinue
            strSql = "Insert Into tblSAEs ([ParticipantsNo],[SAENo]) values ('" & Me.cboParticipantsNo & "','" & NewData & "')"
            CurrentDb.Execute strSql, dbFailOnError
            Response = acDataErrAdded
        End If
    End Sub--------------------------------------------------------------------------------

  2. #2
    Join Date
    Jul 2004
    Response = acDataErrContinue adds the record regardless of the user's response. Check the NotInList Help Remarks about this constant.

  3. #3
    Join Date
    May 2004
    Yes.....i try chaging the code the way you suggested but still no luck

    I'm attaching the sample db
    Attached Files Attached Files

  4. #4
    Join Date
    Nov 2003
    Sussex, England

    Duplicate entries

    Er... you have If not in List events raised against both combo boxes. As these both call the code to enter data this means you are always going to create two records if you enter info in each box!

    I would suggest you put the code:

    Dim strsql
    MsgBox "This SAE No does not exist. Creating new Record."
    strsql = "Insert Into tblSAEs (SAENo, ParticipantsNo) values ('" & NewData & "','" & Me.cboParticipants & "')"
    CurrentDb.Execute strsql ', dbForwardOnly
    Response = acDataErrAdded

    into a function and call the function from a button so that it can insert the data from both combo boxes once.

    It might be an idea to grey the button out until some conditions are met, such as a ontehr button being clicked (New) and the combo boxes: you might also want the code to check that neither combo box is blank (null) before allowing a record to be saved.

  5. #5
    Join Date
    May 2004

    Thanks for your response.

    I'm very new to VBA..........Is it possible that you can send me an example of putting my code into Function ? Or maybe you can walk me thru it ?


Posting Permissions

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