Tables/Relationships

[Housey]

Hi,

I am trying to create a db for service orders for customers. At the moment I have four tables, customer, service_order, parts and totals.

I have one form for customer records that has a button that when clicked opens another form for that customer's service orders. The service order form has two subforms, one for parts and one for totals.

When I try to add a new service order for my test customer it says "you cannot add or change a record because a related record is required in the table 'customer'.

As you can see here I have three relationships setup. cust_no in customer table is a PK and so is service_order_no in service_order table.

Also I am having problems with the totals, as the fields are from different tables the equations won't work from within the subform (I guess I need some kind of query). I need the totals in a seperate table other wise I have a total for every part entry.

Any help would MUCH appreciated.

Cheers

Housey

 

[Pat Hartman]

When I try to add a new service order for my test customer it says "you cannot add or change a record because a related record is required in the table 'customer'.

- The customerID is not being populated. In the BeforeInsert event of the popup form, you need to populate the CustomerID:

Me.CustomerID = Forms!FirstForm!CustomerID

I used the BeforeUpdate event because that event is fired as soon as someone types something into the form and your code won't be what dirties the form, the user typing will have dirtied the form.

The Parts table needs a primary key.

You shouldn't be storing totals so I don't understand what the other table is.

 

[Housey]

Thanks Pat!

I will add that event... and a primary key in the parts table...

I have a totals table because before when I had the totals fields in the parts table there was total for every part and I wanted to add them all together etc...

 

[Housey]

OK, I am a bit further now

I have added some code in BeforeUpdate event which gives over the customer value - works great thanx.

Now I'm trying to add the total. I have got rid of the totals table and put the fields in the parts table (I also gave the parts table a part_id PK but not sure how thats gonna benefit me ).

Trouble is working with multiple rows in a table and different some forms. For example I have the calculation for parts_total as:

=Forms!parts_subform!Price*Forms!parts_subform!Quantity

... but I just get the old #Name? - any ideas???

 

[The_Doc_Man]

As a very general - and usually very GOOD - rule, never store the result of a calculation that can be reliably repeated at will.

For totals, write a summation query instead. Or use a domain-aggregate function like DSUM, DAVG, etc.

The theoretical issue is that when you store a total, you have an implied field that probably ISN'T stored, but should be, or that isn't a key but should be yet can't be...

You see, the total is associated with some invoice number or whatever. But it also could be updated later if the service order cannot be closed right away. And in that case, you need to store the date associated with the the total AND that date must be independent of the service order date. BUT... if the total's date isn't the prime key of the table in which you store it, you are violating one of the normalization rules because you would be storing data in a table but that stored data depends on something other than the holding table's entire prime key. A definite no-no.

 

[Housey]

Hey thanx for posting!

I see your point and now have the parts total working with DSUM and the others are calculated from that.

However, where is the best place to use the DSUM function?

I tried it as a control source value, no good, tried it in load form which is ok as it works the calculations ok but then if I update any values it doesn't update the parts total field. I also tried afterupdate and beforeupdate to no avail.

 

[Housey]

oh, its ok.... I managed to sort it out by putting it in the form_current function and it works ok...... I'm sure i'll be back though