requery in a afterupdate for a combo box on a datasheet subform

Have a  datasheet subform with two combo boxes.  CBOcontractorid is just a drop down and pick the contractor.  CBOPayrate is using a query to go back cbocontractorid on subform to get payrate for that contractor.  

I have also put a afterupdate requery macro on cbopayrate

So record 1 of the subform I choose Susan as a contractor and then in the combo box of cbopayrate the rate of 25 comes in.  Perfect.  Except, it makes me choose from the drop down to get the (Only)  payrate value versus me it just filling in the value.

The real problem is when I go to the second record of the subform.  Choose my contractor it will bring up the rate from the first record not the rate related to this contractor.  It does not requery to give new correct rate.

I have tried many things and I know it is simple but help
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The answer you need depends on what you are using payrate for and whether there is more than one pay rate for a contractor and we can't tell either from your description.  So, I'll answer the unspoken question which is how to get the subform to display the rate for that contractor.

Change the Recordsource for the subform to be a query that joins the table it is currently bound to to the contractor table.  That will allow you to bind the contractor rate to a control on the form which solves the visibility issue.  When you choose a contractor from the drop down, Access will auto-magically populate the rate field.  When you use this method, you should set the locked property of the rate control to locked to prevent accidental update.

Of course, if there are multiple rates for a contractor, this won't help at all and you'll need to give us more information.
cres1121Author Commented:
It will be different payrate for each contractor eventually but right now it is just one.  I have both the contractorid on the subform is a combo box with a drop down.  I have a query on the cbopayrate w with a criteria to the cbocontractorid on the subform that should fill in the contractorid for the query.    So, I think I understand your logic and I think I am doing it correctly.  

The first record is fine in my datasheet.  But when I got to the second line it brings back the payrate from the first person.  I have downloaded all your examples I could find on cascading references but I am stumped.  The requeries give me errors and I am just so frustrated.  I have been working on this for weeks.  

Go to form tbltransummary1 so you can see what is happening.
The big problem is that you are all wrapped up with table level lookups.  Lookups should only exist on forms (sometimes reports).  Putting them on the table may at first seem like a good idea.  In reality, it causes nothing but confusion because at any point in time, it is not clear whether or not you are referring to the underlying ID value or the visible value and that causes issues with VBA and queries.  It would be best to get rid of them now.

Another problem with the lookups is that you have not implemented them correctly.  For example, the rate look up on tbltransdetail includes a join to tbltransdetail.  All this does is cause the same rate to occur multiple times in the pick list.

A future problem is that the combo for the rate must be date sensitive.  If you are keeping the rates by contractor for a date range, the assumption is that over time, there will be multiple rates for a contractor.  Your code will need to ensure that the selected rate is valid for the payweek.

Personally, I would not store the link to the rate, I would store the actual rate for several reasons.
1. It makes working with history easier.
2. I prefer to use a DS view subform and there is no way to resolve the issue of the RowSource satisfactoraly with a DS form.  You would have to switch the subform to be continuous so you can use the technique I used in the cascading combos example of having one control on top of another.

My advice.
!. Remove all the table level lookups.  When you need to see the "text" value, use a query that joins the two tables.  Users NEVER, EVER get to see raw tables or queries so the crutch of the table level lookup is only for you.  As the developer, you should not need a crutch since you know how to write a query to get what you need.
2. Save the rate rather than a link to it.  This will make historical reporting easier and will eliminate the problem with the DS subform combo not showing what you want.  The rate field will be populated by the code in the ContractorID combo.  When you select a contractor, the code will have to pick up the rate for the given pay week.
3. Figure out how you will actually use the rate table once it has multiple rates for a contractor.
4. Remove summaryID and detailID from the subform.  They do not need to be visible.
5. Fix the relationship diagram to create the correct relationships between ALL tables.  Some are missing.
6. The relationship between the invoice and the transdetail and contractors is muddy.  You need to clarify.  There should be NO circular relationships so there is something logically wrong with what you are doing.  
7. You have a repeating group in tblTransDetail.  You need a separate table for deductions.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cres1121Author Commented:
Than you Pat...
You're welcome.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.