data change on combo selection

[karmawangdi]

I have a main form that displays the employee ID and, First and Last names, and address. The subform it contains is supposed to contain items issued or to be issued to a particular employee whose ID is selected in the main form. The data on my main form and the subform change correctly when I navigate using the record navigater. However I want the employee ID to be contained in a combo box so that I can select whichever employee ID I want to issue items to. When I do that my subform data change as expected. But the employee names and address on the main form donot chage accordingly. What should I do so that when can change the employee ID from the combo I get both the subform data and the first and last names, and the address of the employee on the main form to chage, correctly reflecting the data pertaining to the employee ID selected in the combo ?

My main form is based on employees table that contains all the employee ID and Last and First name and address. My subform is based on ItemIssue table that contains the Employee ID to whom a particular item is issued, its itemID, issueID, Issue date and so on. The two tables as you know are related by the employeeID fields with one to many relation.

 

[KevinM]

Use the Combo Wizard to "Find a Record in My Form" and make sure you include the ID field (Hidden or not).

This wen selected should show all relevaent fields on the main and sudform.

HTH

 

[chrisk]

Make your main form unbound. Create a combo box (cmbEmployeeID) for employee ID with a row source of a query based on employees table. In this query include the other employee details you want to show on your main form (first name, last name, address, etc). Make your fields for employee first name, last name, address etc unbound. In the On Change event of the combo box put some code to update the unbound fields from the columns returned by the combo box query, eg

Private Sub cmbEmployeeID_Change()
Me.txtFirstName = Me.cmbEmployeeID.Column(1)
Me.txtLastName = Me.cmbEmployeeID.Column(2)
Me.txtAddress = Me.cmbEmployeeID.Column(3)
End Sub

(remember that the first column of the query, EmployeeID, will be column 0, so the next column, FirstName, will be column 1, etc).

Then make the subform bound to a query which has Forms![YourMainForm]![cmbEmployeeID] as the criteria for Employee ID.

Chris

[This message has been edited by chrisk (edited 05-25-2001).]