Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2006
    Posts
    7

    Angry Unanswered: Combo boxes saving wrong info HELP!!!

    I have 3 filter comboboxes on a form that are bound to a table. the problem is that it saves the id # columns but on the form it shows text. How do I save the text columns to my table. This is what i have so far......

    Option Compare Database
    Private Sub cboCategory_AfterUpdate()
    Dim sType As String

    sType = "SELECT [Type].[Type #], [Type].[Type] " & _
    "FROM Type " & _
    "WHERE [Category ID] = " & Me.cboCategory.Value
    Me.cboType.RowSource = sType
    End Sub

    Private Sub cboType_AfterUpdate()
    Dim sItem As String

    sItem = "SELECT [Item].[ID], [Item].[Item] " & _
    "FROM Item " & _
    "WHERE [Type ID] = " & Me.cboType.Value
    Me.cboItem.RowSource = sItem
    End Sub
    Last edited by eastwood99; 07-12-06 at 20:31.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    From the two combo query samples you provided, ensure the following within the properties window for each ComboBox:

    1) Set the Bound Column to 2
    2) The Limit To List will set to No. Reset it to Yes. If you don't want to limit to the list then leave at No.
    3) Column Count property is set to 2
    4) Column Widths property is set to 0";1"


    If you don't care to have the Record ID within a column of your ComboBox then leave all the simply get rid of the [Type].[Type #], and the [Item].[ID], from your SQL strings and set the Column Widths property to 1".


    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Apr 2006
    Posts
    157
    i usually use cboCategory.column(x) to extract a particular column from comboboxes
    Only quitters quit!

  4. #4
    Join Date
    Jul 2006
    Posts
    7
    I tried setting bound column to 2 but I get "Enter parameter" pop up. So far what I have is a combo box "Category" which has an after update event procedure that has this statement:

    Private Sub cboCategory_AfterUpdate()
    Dim sType As String

    sType = "SELECT [Type].[Type #], [Type].[Type] " & _
    "FROM Type " & _
    "WHERE [Category ID] = " & Me.cboCategory.Value
    Me.cboType.RowSource = sType
    End Sub

    Next I have a combo box "Type" which shows the drop down results filtered by the Category combo box. The"type" combo box also has an after update event that has this statement:

    Private Sub cboType_AfterUpdate()
    Dim sItem As String

    sItem = "SELECT [Item].[ID], [Item].[Item] " & _
    "FROM Item " & _
    "WHERE [Type ID] = " & Me.cboType.Value
    Me.cboItem.RowSource = sItem
    End Sub

    The results are then filtered and shown in my third combo box "Item"

    All 3 combo boxes have column count 2 and width at 0:1"

    The 3 combo boxes are all linked by ID codes in 3 different tables: Category, Type and Item. I hope this clarifies what I 'm trying to do I can send the database if necessary.

  5. #5
    Join Date
    Jul 2006
    Posts
    7
    cboCategory.column(x) ? do i add that to my where statement?

  6. #6
    Join Date
    Jul 2006
    Posts
    7
    im not sure where to cbocategory statement

  7. #7
    Join Date
    Jul 2006
    Posts
    7
    can anyone help

  8. #8
    Join Date
    Jul 2006
    Posts
    7
    Here is my database just go to forms and select the yellow drop downs and then view the results in my "all" table. I want it to record the text in my combo boxes and not the ID #'s
    Attached Files Attached Files

  9. #9
    Join Date
    Nov 2003
    Posts
    1,487
    As I had mentioned earlier...simply change the Bound Column to 2.
    The problem initially lays within the code you used to set the Row Source for the other two Combo Boxes (the Queries).

    Because you are referencing the ID number of the previously selected combobox item within your query you need to inform the query where to find it. Since we changed the Bound Column to 2 the box displays text but we're setting the query for the next Combo to look for a Number which it would get if it were Bound to Column 1. So...in your query string we tell the WHERE clause to get the ID number from Column 0 of the ComboBox which is where the ID is stored.

    Here is what your query string should look like:

    sType = "SELECT [Type].[Type #], [Type].[Type] " & _
    "FROM Type " & _
    "WHERE [Category ID] = " & Me.cboCategory.Column(0)

    There are other ways you could of handled this but I'm not going to get into that here. Each to their own.

    Here is your sample back to you with the corrections:
    Attached Files Attached Files
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  10. #10
    Join Date
    Jul 2006
    Posts
    7
    Thank you for your help. As you can tell my vb statements could be better. I wish I knew a better way to go about it but I am definitely relieved that it’s fixed. Thanks again!!!

Posting Permissions

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