Combo Selection Doesn't Update Rest of Form 3

[CindiN]

There have been many questions like this, but I can't seem to nail it down. I have an Employee Table and a Training Table. The Training Table has the EmployeeID as a foreign key and I created a Combo box to show the Employee FName and LName in the EmplID Field. I then made a form off the Training Table. The Combo box transferred over to the form just fine, but when an Employee Name is selected, all the other fields don't update to that selected Employee. I think I need to create an Event Procedure in the AfterEvent of the EmployeeID control, but I'm not sure how to write it. I'm just learning about SQL and VB. Thanks in advance for your help!!

 

[ntp]

Your combobox afterupdate event might look like this:

Private Sub combo0_AfterUpdate()
me.filter "[EMPLID] = " & me.combo0
me.requery
End Sub

This is assuming your form has a specified data source with a field called EmplID. The filter will return all records with an emplID the same as the one selected from the combobox. the requery is to update the form.

ntp

 

[Quehay]

What's the relationship between Emps and Training Recs? If it's one to many why not have a sub form to show the training records on the Emps form. Another option would be to base the Training form on a query that matches the Emp to a training record and displays the fields (typically not updatable) for the Emp related to the training rec (lookup up "Autolookup" in the help).

 

[CindiN]

Thank you for the input. Ntp, when I use the code you gave, it comes back asking for a macro name, what am I doing wrong? My trainingforms combo box for EmployeeID has it's Control Source set to EmployeeID from the training form, not the table, does that matter? Also, it is pulling in the employee fname and lname from a combo box on the table, does that make a difference?

Quehay, the relationship between Employee Table and Training Table is one to many. Because I have the Training Table set with the EmployeeID already on it, doesn't that match the employee to the training record? It works fine when I update my records from the Training Table, why wouldn't that just carry over to the Training Form? I need to be able to update the training records. I am familiar with Autolookup, and tried that earlier as a way to do this, but it didn't accomplish what I needed. Any suggestions?

Thanks for the help and quick responses, what do I do now?
CindiN

 

[ntp]

Your forms data source should be the Training table.
The combobox should be bound to the employeeId field and consist of three columns: employeeID, FName, LName.

You select an item from the combobox it returns the employeeid. the filter is applied to the form, all training records for the employee with that Id (one-many) will be displayed. make sure navigation buttons are enabled and put controls for all the fields from the training table that you want to be displayed.

That's how your form should be setup for the previous procedure to work.

 

[CindiN]

ntp, I'm sorry to be so slow with this, but thank you for your patience.
You said my forms Data Source should be Training Table... Is Record Source and Data Source the same? If so, yes, form's Record Source is set to Training Table.

The combo box IS bound to the EmployeeID field (from the EmployeeTable or from the TrainingTable??). Currently, it is bound to the EmployeeTable and it has the Row Source containing the following Statement:

SELECT DISTINCTROW EmployeeTable.EmployeeID, [LName] & " ," & [FName] AS Name FROM EmployeeTable ORDER BY EmployeeTable.LName, EmployeeTable.FName;

This creates the FName and LName in the combo box, I select a name and that's when the Training Table Fields should change to match the EmplName I selected. But they don't. When I put your Statement above in AfterUpdate of the EmployeeID Combo box and try to run it in the VBA Editor, it just comes up looking for the macro name. Here's a silly question for you, am I supposed to replace... where you have (combo0)in the statement, with the name of the control, (cboEmployeeID)? If not, (I mean using the combo0)then I get a Compile error saying "Invalid use of Me keyword. I changed the ME.cboEmployeeID to Me.[Name], then it comes up with a compile error on the Me.Filter.

Sorry, I'm soooo confused.
CindiN

 

[ntp]

yes you need to change Ocmbo0 to cboEmployeeID. I'm sorry I assumed you would have realised my code was only a sample that needed to be adapted to your variables. That is why you were getting the Macro problem. The interpreter did not know what combo0 was so it assumed it was some macro.

ntp

 

[ntp]

I noticed something in your last message
>>try to run it in the VBA Editor, it just comes up looking for the macro name.
Are you getting the same error when you open the form?

Your code should look like this -

Private Sub cboEmployeeID_AfterUpdate()
me.filter "[EmployeeID] = " & me.cboEmployeeID
me.requery
End Sub

ntp

 

[CindiN]

ntp, Thanks for your help, I appreciate your time. But, I wrote explaining my situation, and I'm not sure you read my last response entirely. I will try to post my original question again.

Thanks Again.

 

[Quehay]

CindiN:

If I understand what you're trying to do. . .

The combo box is your way of going to those record(s) within tbl_Training that correspond to a particular employee. Think about it: What you want to find is a Training Rcd# that has the EmpID as the value in the EmpID fKey field. If there's just one record that meets this criterion, your combo box needs a "Find" operation on the AfterUpdate event. If you put a combo on a form and use the wizard, checking the "find a record in my table based on the selection" option (the one on the bottom) you'll get the code written for you.
However, a one to many relationship means that more than one Training Rcd will have a given EmpID, so what you should do is filter. ntp's suggestion is precisely what's called for--didn't that work?