OK, I'm putting together a job costing form (with subforms in tabs such as Materials, Labour, etc.). This example will be just for a single "Job" form with a "Labour" subform. If I can get this I can just add more subforms
First, my tables are Clients (ClientID, ClientName, Address), LabourRates (LabourID, Type, Rate) and Jobs (JobID, CustomerID, date). Do I create the "Jobs" table first and then create a "Jobs" form off of it?
Next: for the subform, I assume my best course of action is to create a "Job Details" query for my subform. What fields should I need? I'm thinking JobDetailsID, ClientID, JobID, LabourID, Rate, Hours (entered by user). Basing the subform record source on this query is the best option, right? Do I need JobID in this query? I assume I do, even if for the future.
Ideally, I'd like to select the "Type" of Labour (eg. Overtime: $40.00) in a combo box on the subform and have the "Rate" field in the subform automatically populate with the corresponding rate. Some people seem to say to add some code to the OnUpdate or OnDirty options (Me.TextBox = Me.ComboBox.Column(3)). Others seem to think that my query will allow me to do that if I set the record source of the subform to the query and the source for the "Rate" text box to...? I'm not sure.
There's a good example in a thread I saw, but it only involved a form and not a subform. Both the "LabourID" combo box and the "Rate" text box are in a subform. Does LabourID come from the "Job details" query or from the "Labour" table? What about "Rate". I'm confused...
So, here's what I think I've learned so far:
I assume it's best to avoid lookup fields in tables and use queries instead;
Base forms on those queries when possible, especially when you're looking to bind fields.
Any help is immensely appreciated.
Thanks!
I apologize for my newness but I'd like to get this stuff right. I'll try to be as clear and simple as possible.