Results 1 to 11 of 11
  1. #1
    orion is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    10

    Adding New Values to Combo Boxes that are Bound to Each Other


    Hello
    I have two combo boxes CboMatrix and CboType. I have a query that binds CboType to CboMatrix. So every time I select a value from CboMatrix only related values show up in CboType. This part works fine. I am also trying to add values to CboMatrix and CboType through the “On Not In List” Event. I am able to add new values to CboMatrix just fine. But when I add a new values to CboType it says the new value is added and then goes into a loop with the standard error message from access “the text you entered isn’t an item from the list………………”.
    Is it possible to add new values to combo boxes that are bound to each other or am I being too ambitious.
    Please give me some direction....
    Thanks

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I would think so. What is the CboType NotInList code look like?

  3. #3
    orion is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    10
    So here is the code

    Private Sub CboType_NotInList(NewData As String, Response As Integer)
    Response = acDataErrContinue
    If MsgBox("Sample Type " & NewData & " is not in list. Add it?", vbYesNo) = vbYes Then
    Dim db As Database
    Dim rstType As Recordset
    Dim sqlType As String
    Set db = CurrentDb()
    sqlType = "Select * From Sample_Type"
    Set rstType = db.OpenRecordset(sqlType, dbOpenDynaset)
    rstType.AddNew
    rstType![Type] = NewData
    rstType.Update
    Response = acDataErrAdded
    rstType.Close
    End If
    End Sub

    I also tried using the modifying example from the link below but it does the same thing

    http://www.fontstuff.com/access/acctut20.htm

    Thanks

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You did not set the value of Response in your code. There are two possible values:
    Response = acDataErrAdded ' Tell cbo to requery
    Response = acDataErrContinue ' Let cbo display the error

  5. #5
    orion is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    10
    I'm sorry I don't quiet understand, I have set the value of responses in my code,
    Response = acDataErrAdded on line 12
    Response = acDataErrContinue on line 1

    Unless you mean that they need to go on another location on the code

    Thank you

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Where you have them is just fine. Sorry, I guess I'm going blind here. Or at least a little nuts.
    Try adding what I've highlighted in RED:
    Code:
    Private Sub CboType_NotInList(NewData As String, Response As Integer)
       Response = acDataErrContinue
       If MsgBox("Sample Type " & NewData & " is not in list. Add it?", vbYesNo) = vbYes Then
          Dim db As DAO.Database
          Dim rstType As DAO.Recordset
          Dim sqlType As String
          Set db = CurrentDb()
          sqlType = "Select * From Sample_Type"
          Set rstType = db.OpenRecordset(sqlType, dbOpenDynaset)
          rstType.AddNew
          rstType![Type] = NewData
          rstType.Update
          Response = acDataErrAdded
          rstType.Close
          Set db = Nothing
          Set rstType = Nothing
       End If
    End Sub

  7. #7
    orion is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    10
    It still does the same thing, but when I force the form to close and open the table “Sample_Type” the new value that I typed is written in the table.
    Thanks.

  8. #8
    orion is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2009
    Posts
    10
    And I also have the query for CboType the display values related to CboMatrix, I don’t know if this is causing the issue [Forms]![Sample1]![CboMatrix]
    Thanks

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    It could easily be confused because of you using a reserved word [Type] as a field name.

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I'm also going to recommend a different way to add that record:
    Code:
    Private Sub CboType_NotInList(NewData As String, Response As Integer)
       On Error GoTo Err_CboType_NotInList
       '-- We may need to add another Supplier
       Response = MsgBox("[" & NewData & "] is not yet a Sample Type..." & vbCr & vbCr & _
                         "Would you like to add a New Sample Type to your DataBase?", vbYesNo)
       If Response = vbYes Then
          '-- Create a new Type record
          Dim MySQL As String
          MySQL = "Insert Into Sample_Type([Type]) " & _
                  "Values(""" & NewData & """)"
          CurrentDb.Execute MySQL, dbFailOnError
          Response = acDataErrAdded
       Else
          Response = acDataErrContinue
       End If
    Exit_CboType_NotInList:
       Resume Next
       Exit Sub
    Err_CboType_NotInList:
       MsgBox Err & ": " & Err.Description
       Resume Exit_CboType_NotInList
    End Sub

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I would like to see a sample of your db that exhibits the problem if you could.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. combo boxes
    By googenfrog in forum Forms
    Replies: 3
    Last Post: 07-03-2009, 05:41 PM
  2. Replies: 7
    Last Post: 05-16-2009, 08:08 AM
  3. Replies: 3
    Last Post: 02-26-2009, 10:17 AM
  4. combo boxes
    By labrams in forum Forms
    Replies: 0
    Last Post: 04-20-2006, 09:28 AM
  5. Combo Boxes
    By Mxcsquared in forum Forms
    Replies: 0
    Last Post: 01-19-2006, 04:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums