Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: Simple Combo Box Question

    I have a form with a list of outstanding job requests to be done by various people eg employee1,employee2 etc.

    I want a list box on the form so when its selected I can choose eg person1 and view their job requests in more detail on another form which I already have. Basically I need the combo to point to and open the form with the record I select.

    I have the other form designed already, i just need the code for the list box to select a particular person to show on the form.

    Cheers

    Tom

  2. #2
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184
    I'm working on the same exact thing- dropdown of employees' names, which will fill the values into a subform when I choose a name. Email me if you get a solution, and I'll do the same.

  3. #3
    Join Date
    Mar 2004
    Posts
    42
    This code usually works for me to show the corresonding record in the related form or subform. I used the combo box instead of a listbox though.

    Sub Combo12_AfterUpdate()
    ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[Field1] = '" & Me![Combo12] & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
    End Sub

    ESM

  4. #4
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184

    Duh.

    I don't know about you, but I made it more complicated than necessary. I set my combo box's source property to the field name it represents (EmployeeID). Then, I linked the subform to the main form by Master and Child fields both =[EmployeeID].

    The fanciest thing I did was set the combo box's row source to a query. In that query, I created a field called ComboName, which concactenates the employee's first, middle and last names, so it looks like "Adams John Quincy" instead of showing the employee ID. It's actual stored value is the EmployeeID, though.


    The only code I put in is this:



    Private Sub cmbEmployee_AfterUpdate()

    'After the user selects an employee name, requery so the subform will have the most current info
    DoCmd.Requery

    End Sub

  5. #5
    Join Date
    Aug 2004
    Posts
    364
    Not got mine to work yet, dont really wanna do a query if i can help it. There must be an easy way to get the selected record in a combo box to open in a seperate form...

  6. #6
    Join Date
    Sep 2003
    Posts
    11
    Hi there,

    There's two ways to do it for me. As mentioned by Lisa Chow above, you can create a subform tethered by child and master field links, which will automaticlly update records to a particular record in the combo box . That's the simpliest way to do it. I think you should use that approach.

    I however, don't use this method anymore because i like to keep my textboxes and controls unbound. Having the fields unbound offers more flexibility because you don't run into read/write problems. In return for this though, you have to do a bit of coding. In essence, by not linking controls in a form to a database table you're creating a disconnected dataset. You have to code updates, additions, and deletions manually. I feel that the tradeoff between being disconnected and being connected is well worth it as i dislike the fact that when you edit a certain field it gets written into the database when you are in a connected state--which can be a bit of a headache sometimes.

    I'm not sure if i've helped, instead, I probably caused more grief. But honestly, that's how you learn the benefits of programming; being able to offer the ease of custom usability, flexibility, and enhancements to what you build.

    Regarding the disconnected approach, you'd have to use ADO.

    Regards.

Posting Permissions

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