From updates

[dommales]

Ok, this is a really bad question to ask, but it is driving me up the wall.

I have a table of information:

ConID name charge/hour
1 Dan 24
2 Dave 30

and I need to have the following table populated for hyours worked:

ID conID Charge/hour Hours worked Total
1 1 24 40 960
2 1 24 37 888
3 2 30 37 1110


The ID is an autonumber and I am running it in a subform.

I need the charge/hour to be updated from the first table. The hours worked will be manually inputted. And total is a calculated field.

These need to be saved in a history file.

I have tried using the default values in the text control boxes but have had no luck.

Help!

Dom

 

[Aivars]

I'm seeing you work with tables irrationally. It's not needed that you duplicte data in two tables. Create relationships ono to many between both your tables

Table1 Table2
FieldName Key FieldName Key
ConID primary ConID primary
name ID primary
charge/hour Hours worked     


Table1 (one) Table1 (many)
ConID        ---------< ConID       

 

[dommales]

The 2 tables are needed as one is a master (current Table) and the other is a history table. As along the point of a contract, we really should give contractors a pay rise.

With a history table that means we can report on old information.

I already have the one to many relationship set up, but thanks for the suggestion.

I really should add more information in to my questions.

Thanks

Dom

 

[Maquis]

Well, when I read your question, my first thought was the same as Aviars. Don't store the contractor's charge in both tables, but you did say this was a history table and in that case I can understand storing the fee since I assume the contractor's charge will change over time in the contractor's table.

How are you entering the information into the work order table? Are you using a form? If so, don't let your users enter data directly into the contractor's charge box, but in the change or lost focus event of the Contractor's box put this code.

Dim Criteria as string
Criteria = &quot;ConId = &quot; & me!contractor
me![charge] = dlookup(&quot;Charge&quot;,&quot;<Contractortable>&quot;,Criteria)

Of course change the syntax to match your tables. Maq B-)
<insert witty signature here>

 

[Aivars]

Sorry Dom, I unawares pushed <Submit Post> before.
I'm seeing you work with tables irrationally. It's not needed that you duplicte data in two tables. Create relationships one to many between both your tables:

Table1
FieldName Key
ConID primary
ConName
charge/hour

Don't name fields as Name because if you'll ask for Me.Name will be returned form name

Table2
FieldName Key
ConID primary
ID primary
Hours worked  

Table1 (one) Table2 (many)
ConID        ---------< ConID  

Now it's possibly to create queryies which include all necessary fields without data double (never save the calculated fields!)

Example:
Main form's recerdsource
SELECT * From MyTable;

Sub form's recerdsource
SELECT * From MyTable1;

Subform's Data source
Link Master Fields = &quot;ConID&quot;
Link Child Fields = &quot;ConID&quot;

Create textbox on subform for Total:
DataSource of this textbox =forms!MainForm![charge/hour] * [Hours worked]

Use wizard for subform creating
Aivars