Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2003
    Posts
    106

    Unanswered: Show data in a combo box dependant on another combo

    Can anyone help?
    I have two combo boxes, each linked to a separate table. I would like to select a field from the first combo which would automatically select a field in the second combo.

    Thanks

  2. #2
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    In the first ComboBox's AfterUpdate Event Place the following Code:


    Code:
    Private Sub Combo1_AfterUpdate
         Dim SqlStr as String
    
         SqlStr = "SELECT {Fields} FROM {Table} WHERE {Field} = "& Combo1 & ";"
    
         Combo2.RowSource = SqlStr
         Combo2.Requery
    
    End Sub
    Where {Fields} are the Fields you want in the combobox
    {Table} the table it is based on
    {Field} the field the criteria is based on
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

  3. #3
    Join Date
    Aug 2003
    Posts
    106

    Thanks

    Thanks!! This is really helpful

  4. #4
    Join Date
    May 2011
    Posts
    10

    Cascading combo boxes.

    Hi there,

    I tried this with my own data, and i cant seem to get it to work. I would like users to be able to select a directorate in the frist combo box, that will filter departments in the second. I put this is the row source of my first combo box (cboDirect):

    SELECT DISTINCT AllDepartment.Code FROM AllDepartment ORDER BY AllDepartment.Code;

    And this in the afterupdate event on the first combo box:

    Private Sub cboDirect_AfterUpdate()

    Dim SqlStr As String

    SqlStr = "SELECT [Department] FROM [AllDepartment] WHERE [Code] = " & cboDirect & ";"

    cboDept.RowSource = SqlStr
    cboDept.Requery

    When i select a record from the first combo boxes, the second combo box is comming up blank everytime.

    Any ideas?

    Harriet

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If AllDepartment.Code is not numeric (just a supposition) you need to enclose the value of the combo in single quotes:
    Code:
    SqlStr = "SELECT Department FROM AllDepartment WHERE Code = '" & Me.cboDirect.Value & "';"
    Have a nice day!

  6. #6
    Join Date
    May 2011
    Posts
    10
    that worked!!!!! brilliant! thank you!

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  8. #8
    Join Date
    Jul 2011
    Posts
    3

    help with combo box

    Good morning, straight to the point, i have this form i use to log in and out of "merchandise" i create a combo box that look to the query to show the "name" what i want is once i select the name the phone number and id number shows in another box or combo box, so in theory, when people comes i just select the name and the rest got selected automatically, that makes sense? I can send screenshots if needed)

    Note : sorry for my poor English

  9. #9
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Quote Originally Posted by mikevillarroel View Post
    Good morning, straight to the point, i have this form i use to log in and out of "merchandise" i create a combo box that look to the query to show the "name" what i want is once i select the name the phone number and id number shows in another box or combo box, so in theory, when people comes i just select the name and the rest got selected automatically, that makes sense? I can send screenshots if needed)
    You need to write an SQL statement that looks something like:

    Code:
    "SELECT [~name field~], [~phone field~], [~id field~] FROM ~yourTable~ WHERE [~name field~] = " & ~comboBox name~
    Replacing "~blah blah blah~" with your field, table and combo box names.

    That will recover the information you require, then to display it in a form, you'll need to create a couple of text labels in design view, then in the VBA screen, create an AfterUpdate event precedure for the combo box, that looks like:

    Code:
    Dim strSQL As String
    Dim rs As DAO.Recordset
    
    strSQL = "SELECT [~name field~], [~phone field~], [~id field~] FROM ~yourTable~ WHERE [~name field~] = " & ~comboBox name~
    
    Set rs = CurrentDb.OpenRecordset(strSQL)
    
    If Not ((rs.BOF) And (rs.EOF)) Then
    ~your phone number label name~.Caption = rs![~phone field~].Value
    ~your id number label name~.Caption = rs![~id field~].Value
    End If
    
    'Tidy up
    rs.Close
    Set rs = Nothing
    That should change the two labels you created to reflect the corresponding values for whatever "name" you select from the combo box.

    Hope this helps!

  10. #10
    Join Date
    Jul 2011
    Posts
    3
    Quote Originally Posted by kez1304 View Post
    You need to write an SQL statement that looks something like:

    Code:
    "SELECT [~name field~], [~phone field~], [~id field~] FROM ~yourTable~ WHERE [~name field~] = " & ~comboBox name~
    Replacing "~blah blah blah~" with your field, table and combo box names.

    That will recover the information you require, then to display it in a form, you'll need to create a couple of text labels in design view, then in the VBA screen, create an AfterUpdate event precedure for the combo box, that looks like:

    Code:
    Dim strSQL As String
    Dim rs As DAO.Recordset
    
    strSQL = "SELECT [~name field~], [~phone field~], [~id field~] FROM ~yourTable~ WHERE [~name field~] = " & ~comboBox name~
    
    Set rs = CurrentDb.OpenRecordset(strSQL)
    
    If Not ((rs.BOF) And (rs.EOF)) Then
    ~your phone number label name~.Caption = rs![~phone field~].Value
    ~your id number label name~.Caption = rs![~id field~].Value
    End If
    
    'Tidy up
    rs.Close
    Set rs = Nothing
    That should change the two labels you created to reflect the corresponding values for whatever "name" you select from the combo box.

    Hope this helps!
    That's what I get : (when Name, Phone# and SSO# are the 3 columns in my personal info Query )
    Attached Thumbnails Attached Thumbnails untitled.bmp  

  11. #11
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    It needs to be put in VBA code, not an SQL Query.

    Push Alt+F11

    Open the form you're working on, fill in the blanks (denoted with '~') with the names for your form and tables.

    You probably need to do some reading on how to do this, as to achieve this requires some knowledge of VBA and SQL.

  12. #12
    Join Date
    Jul 2011
    Posts
    3
    Quote Originally Posted by kez1304 View Post
    It needs to be put in VBA code, not an SQL Query.

    Push Alt+F11

    Open the form you're working on, fill in the blanks (denoted with '~') with the names for your form and tables.

    You probably need to do some reading on how to do this, as to achieve this requires some knowledge of VBA and SQL.
    I will, thanks!

Posting Permissions

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