Default values taken from another table

[manybeats]

I have 5 tables and want the value from one table to be transferred to another, based on certain criteria. Basically, I have tblProjectEC, where rates for a particular project are stored. Then I have tblECReport, where details concerning actual expenses for a particular project are stored. My tblECCode gives generic descriptions of these expenses.

On my form, I want users to enter the date, select a project, select an expense from any in the tblECCode. If that expense is set up in tblProjectEC, I then want the associated rate and unit from tblProjectEC to become the actual rate and unit in tblECReport. If no expense has been set up, then I want the field to remain blank and the user will enter the appropriate rate and unit themselves.

I have tried creating an unbound combo box (still shown in the database) which brings over the rate and unit, thinking I would then populate the unit and rate boxes using "=[ComboboxName].[column](#)", but that didn't work.

Then I tried using an IIf statement to pull the info for the unit field, but it is not working.

I've been working on figuring this out for some time with no luck. The form is frmTimesheet that I'm struggling with and I'm working in Access 2010. Please help!

 

[manybeats]

Go figure that after all this time working on a solution, I find one shortly after I make a post. On the After Update of my ECCodeFK I added:

strFilter = "[ECCodeFK] = " & Me!ECCodeFK & "And [ProjectFK] = " & Me!ProjectFK
Me!ECUnit = DLookup("[ProjectECUnit]", "[tblProjectEC]", strFilter)
Me!ECRate = DLookup("[ProjectECRate]", "[tblProjectEC]", strFilter)

This allows the value from the other table to be brought over, but also allows for it to be changed.