Need some help with forms and subforms

[Base]

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.

[NTC]

I understand, but do not share, the view on anti-lookup fields entirely. There are some cases, for example - the 2 letter State abbreviation - where it is a very efficient design and there is no harm done.

Where one does tactically decide to use a lookup field - I recommend that there be no key field in the source table - in my state example - it is of no value to bind to "1" rather than "AL" (for the state of alabama) - - and it is this binding to the key that does cause much of the problems down the road that support the anti-lookup philosophy.

If one is working primarily in continuous forms - a look up field is extremely useful.

I didn't really follow the bulk of your post but hope this helps in a general way.

[hertfordkc]

I think I understand the three tables which you discussed. However, to talk about calculating expenses, another table?, related to job ID, must describe the different elements of the job, e.g. hours of type 1 labor, hours of type 2 labor....which could be queried against the labor table to get an expense for each type, or for the entire job.

Editorial follows.
<Quote>
I apologize for my newness but I'd like to get this stuff right. <Quote>
"Right" is the way that gets you to your goal quickly and with fewest headaches. Don't worry about computer efficiency unless you are working on something with hundreds of thousands of records or more. The user can wait another second. It's not worth another hour of your time to save that second.

[Base]

hertfordkc, I wasn't too concerned about totals and calculations - those can be added in an "extended" query, if my research proves correct.

So, my proposed structure would be:
Tables - Clients, Labour Types, Jobs (I guess I'll have to add job total. I'm using the Jobs form as an entry form for this table, right?)

Queries - Job Details (Labour Type, Rate, Hours), Job Details Extended (Labour Type, Rate, Hours, Total)

Forms - Jobs (populates/refers to Jobs table)
Subform - JobDetails (from Job Details Extended query)

So, my Labour types combo-box refers to LabourTypeID in the Job Details Extended Query or from the Labour Types table? And if I want the "Rate" field in the Job Details Subform to show the corresponding rate for the selected LabourType do I use the "Rate" field from the Job Details Extended query? Or do I have those backwards?

[hertfordkc]

hertfordkc, I wasn't too concerned about totals and calculations - those can be added in an "extended" query, if my research proves correct.

So, my proposed structure would be:
Tables - Clients, Labour Types, Jobs (I guess I'll have to add job total. I'm using the Jobs form as an entry form for this table, right?)

Queries - Job Details (Labour Type, Rate, Hours), Job Details Extended (Labour Type, Rate, Hours, Total)

Forms - Jobs (populates/refers to Jobs table)
Subform - JobDetails (from Job Details Extended query)

So, my Labour types combo-box refers to LabourTypeID in the Job Details Extended Query or from the Labour Types table? And if I want the "Rate" field in the Job Details Subform to show the corresponding rate for the selected LabourType do I use the "Rate" field from the Job Details Extended query? Or do I have those backwards?

You alluded to Job Details in your original post and in this post you refer to it as a query. In thinking about database design, defining what data is being saved (in tables) is the first order of business. I was trying to suggest that you define a table that contains Job Details. In this post, you mention Job Details as a query, perhaps thinking about using it in conjunction with a form to collect the data.
While that will be necessary, I would first define the table and then design the queries and forms.
While it may be possible to collect the Jobs Detail data and use it without saving it in a table, I can't imagine a situation where I wouldn't save it in a table.

[Base]

Goal - Create a "Jobs" form that shows costs for each labour rate type used based on labour rates, labour rate types and hours. For now we won't bother with the hours and totals. I just want to make sure my linkages are correct.

Tables - Clients, Labour Rates, Jobs, Job Details

Queries - Job Details Extended (Same fields as Job Details table with an extra "Total" field to be added later)

Jobs Form- Control Source Jobs Table
Jobs Form Fields (only 2 of them):

1. Client (drop-down box),
2. Date

JobDetails Sub-Form - Control Source:Job Details Extended Query
JobDetails Sub-Form Fields (only 2 of them):

1. Combo box - control source JobLabourTypeID in Labour Details Extended Query. Fields from Labour Rates table
2. Text box - control source JobLabourRate in Labour Details Extended Query. Fields SHOULD be populated by combo box selection

So...am I correct in using this structure? I base the sub-form on the query and select the combo box records from the Labour rates table.

The problem is, when I use the suggested event procedure to update the text box based on the combo box selection, I get an error: "A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control"

I used the code:

Option Compare Database
Private Sub JobLabourTypeID_AfterUpdate()
Me.JobLabourRate = Me![JobLabourType].Column(2)
End Sub

I notice that when I enter the right half of the equation, the "Column" option doesn't pop up for me to select - I have to type it. So I know something's wrong.

I've tested this auto population with very simple forms based on a table, but my setup doesn't seem to work.

I appreciate all your help.

Thanks