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.
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
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.