Lookup in subform

[ehrendreich]

In the attachements I have my relationship diagram including all pertainate tables/fields. In addition I have a screen shot of the form in question.



The problem I am having is performing a lookup for a wage rate. The look up must use the employeeID field to match the employeeID field in the tbleWageRate. Each employee can have a different pay rate. There are only so many type of pay rates. I have a tblWageType table. This defines these wage types and includes a type name and type description.

I want to store the WageRateId. I want to show a list of all of the rates available to the employeeID that is listed in the main form and show the type name and description. I also want it to populate the WageRateAmount field on the form as an audit check.

I hope that I am clear about this I have not developed in access in a long time.

Thanks

Bill

[RuralGuy]

So you want to change the WageRateAmount control in the SubForm to a ComboBox that looks uo the value? Where are you having a problem?

[ehrendreich]

I do not know how to send the employeeID field from the main form to the query that I am using to look up the info in the tblWageRate. It also needs to update when I move between Employee records. I have a parameter in the query called EMPID.

[RuralGuy]

You have an EmployeeID field in the RecordSource of the SubForm (tblShiftMaster). I'll bet this is the LinkChild/MasterFields property of the SubFormControl and changing the Employee in the MainForm changes what appears in the SubForm.

[ehrendreich]

How can I take that employeeID value and pass that to my query?

Basically I have no idea how to look up the rate info by employee and look up the rate type information, display all of that and store the rateid.

I can't figure out how to do this. I suspect that VBA on Enter and Click events would to it. I would imagine that if I set the varible on the Enter event and on click used that value to call the query with the employeeID value as an arguement. I would imagine after that I would have to close the query.

I have no idea how to do this. Maybe there is a simpler way.

Thanks for your help on this.

[RuralGuy]

Use the ComboBox wizard to create the cbo for you. It knows how do do all of that. It is all done in the RowSource of the cbo with a WHERE clause.

[ehrendreich]

Using the cbo wizard I can return all of the WageRate info. I can not however filter the results so that only those that match the EmployeeID value from the main form. It needs to update this every time it goes to a new record.

SELECT tblWageRate.WageRateID, tblWageRate.EmployeeID, tblWageRate.WageTypeID, tblWageType.WageType, tblWageType.WageTypeDescr, tblWageRate.WageRateAmount
FROM tblWageType INNER JOIN tblWageRate ON tblWageType.WageTypeID = tblWageRate.WageTypeID;

It does not give me a WHERE statement. Is there a WHERE statement that I can use to pass the current tblEmployeeMaster.EmployeeID record each time the combo box is clicked.

Also it seems that once I access the combo box it allways shows the same thing. Ie it does not try to update each time it is accessed.

[RuralGuy]

Add this WHERE clause to your RowSource:
SELECT tblWageRate.WageRateID, tblWageRate.EmployeeID, tblWageRate.WageTypeID, tblWageType.WageType, tblWageType.WageTypeDescr, tblWageRate.WageRateAmount
FROM tblWageType INNER JOIN tblWageRate ON tblWageType.WageTypeID = tblWageRate.WageTypeID
WHERE tblWageRate.EmployeeID = Forms!tblEmployeeMaster2!EmployeeID;
...then Open a code window for the GotFocus event and do a Me.cboName.Requery
...using your ComboBoxName of course.

[ehrendreich]

Thank you. That did the trick. I knew there was a simple way. It has been a while.

[RuralGuy]

You're welcome. Glad we could be of some help.