Avatar of kwilsokl

asked on 

Access subform\table lookup and validation

I have an access 2003 database that has 3 table and 1 form with 2 sub forms nested in it.

The main form which has 2 sub forms (Serviceplan, and claims) where a user will input some information that deals with services, units and cost per unit. . in the first sub form ServicePlan a user will select a service they are performing, the number of units and the rate.

The second sub form is for claims to be submitted against the (serviceplan subform). There is a table that stores  67 services with different rates.  

Here is what I need to do but I don't know where to start.

when a use fills out the (ServicePlan) sub form I need the form to not allow a price over the caped amount that is stored in the service table.

SO for example if they said I can do a service for $50 per hour and it will take me 2 hours. I need this to look at the service table and see if the $50 per hours is not over the rate we are willing to pay. so if the rate in the service table was $60 then it would except the $50 value.

Then in the claims subform if they would submit a claim against the service plan they generated in the above ServicePlan subform.the above sub form should only except claims at the rate that was approved and specified in the ServicePlan sub form.

Sorry it is very hard to communicate. Basically what we are trying to do is only except services that are within our budgeted amount and once the service is accepted only allow claims against the agreed amount.
Microsoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon