cres1121
asked on
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
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
ASKER
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.
Contractors_master---Copy--2-.accdb
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.
Contractors_master---Copy--2-.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Than you Pat...
You're welcome.
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.