Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2006
    Posts
    3

    Unanswered: Combo box selection

    I have a combo box with 4 fields. ID#, Last Name,First Name, Middle Name. I would like the user to be able to quickly scroll through the list by Last Name but have the ID# populate the combo box. How can I do this this?
    Thanks
    Mike

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I have a combo box with 4 fields. ID#, Last Name,First Name, Middle Name. I would like the user to be able to quickly scroll through the list by Last Name but have the ID# populate the combo box. How can I do this this?
    Thanks
    Mike
    Set the properties as follows:
    Row Source Type: Table/Query
    Column count: 4
    column width: 0";1.5",0";0"
    bound column:2 (assuming last name is the second column).
    Row Source: a query containing the four columns in their proper order.

    In the AfterUpdate event, populate the various fields as follows (assuming there are text boxes on the form for everything):
    Me.tID = Me.cmbComboBoxName.Columns(1)
    Me.tFirstName = Me.cmbComboBoxName.Columns(3)
    etc.
    After you finish editing the data, populate the actual record in VBA.

    If you do it this way, the text boxes you populate have to be unbound, and should not have names that are identical to the record source field names. E.g. if the field name is FirstName, change it, like tFirstName.

    You can also do this without the AfterUpdate event. In the property sheet, you can set the control source of the text box tFirstName as follows:
    Code:
    = cmbComboBoxName.Columns(3)
    to populate it properly.

    HTH,
    Sam

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Cool

    Quote Originally Posted by daystarmike
    I have a combo box with 4 fields. ID#, Last Name,First Name, Middle Name. I would like the user to be able to quickly scroll through the list by Last Name but have the ID# populate the combo box. How can I do this this?
    Thanks
    Mike
    Hi Daystarmike,

    What I do in mine is simply for that form, go to DesignView, then right-click on that ComboBox, click on Properties, click on the Data tab, go down to RowSource and click on the elipses (...) That will take you to the SQL Statement: Query Builder and there you will see your fields. Under the field you wish click in the Sort: row and then select Ascending. Click in the same row for the ID field and delete that selection to leave it blank. That's about it and it will have your ComboBox in the order of the field you just selected.

    have a nice one,
    BUD

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Sam

    Regarding your reply

    If the order is ID#, Last Name,First Name, Middle Name in the combo box
    using a query or query builder the first column in this record source starts at column(0) not column(1) so Me.tID = Me.cmbComboBoxName.Columns(1) should be Me.tID = Me.cmbComboBoxName.Columns(0)

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by Poppa Smurf
    Sam

    Regarding your reply

    If the order is ID#, Last Name,First Name, Middle Name in the combo box
    using a query or query builder the first column in this record source starts at column(0) not column(1) so Me.tID = Me.cmbComboBoxName.Columns(1) should be Me.tID = Me.cmbComboBoxName.Columns(0)
    That is not the way I understand it from the help file. I understand that the 0 only means the index, 1 and up are column numbers. Am I incorrect?

    Sam

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    From the help file
    Column Property
    .
    .
    Remarks
    Use 0 to refer to the first column, 1 to refer to the second column, and so on. Use 0 to refer to the first row, 1 to refer to the second row, and so on.
    George
    Home | Blog

  7. #7
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Bud

    I have re-read the question.

    By having the combo box bound to the column containing the ID#, the ID # will populate the control source of the combo box. I have attached a sample database.

    Select a student from the drop down list, enter a result. Open the tbl_results
    and you will find that the number for the student entered is the same as the student_id in the tbl_student for the student that was selected.

    Sam
    Have a look at the coding behind the combo box, it shows how to populate fields from a combo box.

    Allan
    Attached Files Attached Files

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by Poppa Smurf
    Bud

    I have re-read the question.

    By having the combo box bound to the column containing the ID#, the ID # will populate the control source of the combo box. I have attached a sample database.

    Select a student from the drop down list, enter a result. Open the tbl_results
    and you will find that the number for the student entered is the same as the student_id in the tbl_student for the student that was selected.

    Sam
    Have a look at the coding behind the combo box, it shows how to populate fields from a combo box.

    Allan
    Hi Poppa,

    That was nice but I thought what he was actually talking about was this:
    I have a combo box with 4 fields. ID#, Last Name,First Name, Middle Name. I would like the user to be able to quickly scroll through the list by Last Name but have the ID# populate the combo box.
    Check out the .bmp below. I can scroll through the ComboBox with the Names showing, but after I select, Only the Number is populating the box. But hey, I could be wrong.

    Bud
    Attached Thumbnails Attached Thumbnails daystar.bmp  

  9. #9
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Bud

    At least he has two options.

Posting Permissions

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