Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo box in Form (2003)

    I'm designing an Access data entry form. I have one populated table, which contains LastName, FirstName, ID (key). I created a combo box field to search on the ID, but I also want to take the related LastName and FirstName and have those automatically displayed on the form when I choose the ID.
    Essentially, I'm creating a variation of a Contacts table, but I want to be able to speed data entry by selecting from an existing table that just has the LastName, FirstName, ID fields.
    I've been this way before, but for the life of me, I cannot remember how to do this.

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box in Form (2003)

    Have you used the wizard? It will set the focus of your form automatically (assuming your form's data source is the table you're referring to). As far as data entry, is it going to be into the same table, or another table, linked by ID?

    If your form does have a data source, the following code in the combo box's after update event will set your forms focus to the current record. You can drag fields from the field list onto your form.

    Private Sub cboCOMBOBOXNAME_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = '" & Me![cboCOMBOBOXNAME] & "'"
    Me.Bookmark = rs.Bookmark
    End Sub
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo box in Form (2003)

    You can set the Column Count for the combo box to 3, and include LastName and FirstName in the row source of the combo box.
    You can then use text boxes on the form with control source

    =[ComboBoxName].[Column](1)

    and

    =[ComboBoxName].[Column](2)

    (Columns start counting at 0, so Column(1) is actually the second column, etc.)

  4. #4
    New Lounger
    Join Date
    Sep 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box in Form (2003)

    Thank you, Hans. Your solution was just what I needed!

Posting Permissions

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