Sub Form Data Entry

[OldBloke]

Hi All

I have been playing around with a data display data entry form. What I need it to do is show the details of the job, job report, staff, time on site and so on with the agreed price for the visit and give the data entry option to change to a different price.

Hope that was clear (ish).



So in other words if I quoted you $20.00 to clean your stove each week and for some reason on the third week I left the lid off the cleaning can in your stove, so I decided to do this week free for you, I want to enter $0.00 in my subform for only that week.

Now the issue is not the subform itself (I think), the issue seems to relate to the underlying query.

The underlying query is being forced on me by the relational data model.

If I use qryLabour by itself I can alter field sell price all I like - which is exactly what I want to do. However Agreed Price is not contained within this query and it is not possible to add Agreed Price to this query. So whilst I am able to enter a sell price I cannot see the Agreed or Quoted price. This is a major issue as your oven maybe $20 and your neighbours may be $35 as it is a different model etc.

My SQL looks like:

SELECT DISTINCT qryJobLabour.JobID, qryJobLabour.JobStaff, qryJobLabour.JRNumber, qryJobLabour.JobType, qryJobLabour.JRDate, qryJobLabour.StartTime, qryJobLabour.EndTime, qryJobLabour.TravelTime, qryJobLabour.WorkedHours, qryJobLabour.WorkCost, qryJobsPrice.AgreedPrice, qryJobLabour.Sell, qryJobLabour.AdditionalWork
FROM qryJobLabour INNER JOIN qryJobsPrice ON qryJobLabour.JobID = qryJobsPrice.Job
WITH OWNERACCESS OPTION;

[NTC]

I would say that perhaps you are accidentally making it more complicated than need be. A Distinct query will not be updateable as you have probably found.

It is common for any sale ( or service ) to have multiple details - and this is a classic parent/child table relationship. Make your main form sourced on the parent. Make your subform sourced on the child - - and when you insert it the wizard will help set up the cross referencing field.

Then when you go to a customer....all their details will display and you can add/edit those details freely.

Hope this helps.