Results 1 to 2 of 2
  1. #1
    combine21 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    31

    combo box selection stored in table

    I have two cascading combo boxes in a form in which I want new entries to be stored in the table the other selections came from. My first box is categories which has: food, manipulative, sensory, cage furniture change out, and training, from the cateogory field in my table. My second combo box is based upon the first selection, which is description. So say I have a new food I want to store in my table. If I select Food, then type a new description, the new description is stored in the table, by the category is blank because food is not a new category. I have placed the following code in both the after updates.



    Private Sub Category_AfterUpdate()
    On Error Resume Next
    Description.RowSource = "Select qry_enrichment_all1.Comments " & _
    "FROM qry_enrichment_all1 " & _
    "WHERE qry_enrichment_all1.Category = '" & Category.Value & "' " & _
    "ORDER BY qry_enrichment_all1.Comments;"

    Dim i As Integer
    i = DCount("*", "Table1", "[Category]='" & Category & "'")
    If i = 0 Then
    CurrentDb.Execute "insert into Table1 ([Category]) select '" & Category & "'"
    End If
    End Sub

    and for the second combo box:

    Private Sub Description_AfterUpdate()
    Dim i As Integer
    i = DCount("*", "Table1", "[Comments]='" & Description & "'")
    If i = 0 Then
    CurrentDb.Execute "insert into Table1 ([Comments]) select '" & Description & "'"
    End If
    End Sub


    Is there a way to intertwine the two so that even if my category selection isnt new, but my desription is, that it will store both the category and description in the new record so it will appear in the correct combo box the next time? Thanks.

  2. #2
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    If you had a lookup type table for the combobox values, this would be easier. Then you could design a little vba code to open the lookup table and add the record.

    ex (in ado):
    dim QI as integer
    QI = msgbox("Add new description to lookup table?",vbyesno)
    if QI = vbno then exit sub
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from MyLookupTableName"
    rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic
    rs.addnew
    rs!Category = Forms!MyFormName!MyCategoryCombobox
    rs!Description = Forms!MyFormName!MyDescriptionCombobox
    rs.update
    rs.close
    set rs = nothing

    Otherwise if you want to base the comboboxes value on previous values entered, you'll probably need to update the data table first and then requery the comboboxes.

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

Similar Threads

  1. Pop-up Combo Box Record Selection
    By AKQTS in forum Forms
    Replies: 1
    Last Post: 08-11-2010, 01:01 PM
  2. Combo Box that doesn't allow selection
    By Ted C in forum Forms
    Replies: 20
    Last Post: 08-09-2010, 04:00 PM
  3. Change to selection in combo box
    By corystemp in forum Access
    Replies: 7
    Last Post: 06-28-2010, 10:20 AM
  4. Replies: 1
    Last Post: 08-26-2009, 10:45 AM
  5. Replies: 3
    Last Post: 02-26-2009, 10:17 AM

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