Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Unanswered: Displaying A Record using Cascading ComboBoxes

    I am a coding novice (if that). I have to build a database for work and im definitely not the guy for it, but I am the only one that has any experience with access at all.

    Anyways.

    My issue is this. I have a 2 tier cascading combo box set up. My table is labed tblMaterial. In this table I have the 2 columns I am using for the 2 boxes, which are "Type" and "material". In the type column I have the area in which the descriptions fall under. Tray, Supports, Cable, Glands, etc.

    The way that my form needs to function is to select a Type, for example , Tray. When I select this, it lists all different tray we have , by description. Now after I select a description, I need it to find that description (which is unique) and display its record. As it stands right now, no matter what I choose, it only displays my first record from the table and nothing else.


    This is my code for the 2 boxes:

    Option Compare Database

    Private Sub cmbType_AfterUpdate()

    cmbMaterial.RowSource = "Select tblMaterial.Description " & _
    "FROM tblMaterial " & _
    "WHERE tblMaterial.material_Type = '" & cmbType.Value & "' " & _
    "ORDER BY tblMaterial.Description;"

    cmbMaterial = cmbMaterial.Column(0, 0)
    End Sub


    Any help would be greatly appreciated.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by jHat View Post

    This is my code for the 2 boxes:
    If that's the code for the 2 (combob)boxes, that could be the problem! You only list code for your Type combobox, which is apparently working, not for your Material combobox, which is giving you trouble!

    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
    Mar 2012
    Posts
    3
    So what code should I have? I have the material box being populated from the type box. I just need to , when I select the item in the material box, to display that record.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    If you're going to retrieve a Record, based on a selection from the cmbMaterial Combobox, you'll have to have code in its AfterUpdate event to do so. Something like this
    Code:
    Private Sub cmbMaterial _AfterUpdate()
          
    Dim rs As Object
    
    Set rs = Me.Recordset.Clone 
    rs.FindFirst "[Description] = '" & Me.cmbMaterial & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
      
    End Sub
    Also, why do you have this line

    cmbMaterial = cmbMaterial.Column(0, 0)

    in the cmbType_AfterUpdate event? In this kind of code this would normally be

    cmbMaterial = ""

    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

  5. #5
    Join Date
    Mar 2012
    Posts
    3
    I put the (0, 0) in because it was showing the old selected item based on the top.

    I would pick a gland type and it would show a tray type until I clicked the box below and selected a new record.

Posting Permissions

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