From form to table

[faca]

Hello!

I need some help with form.

My mechanic wants to save WorkOrder form so he can print it next time without entering info again (Workorder number, date of receivement, date of hand over, description of work)

But the problem is that i don't know how .. i mean i created table->

WorkOrder


WorkOrderID
WorkOrderNumber
DateOfReceivement,
DateOfHandOver
DescriptionOfWork

but the problem is that when i link this table in query with Customer, Vehicle and Service tables than form is blank ??

Where is the problem?


Second question:

I have Material table and ServiceParts table... in material table is TaxRate and whenever i update TaxRate for example from 22 to 13 .. all previous records for this material in ServiceParts are changed to 13.

So what i did is i also saved TaxRate in ServiceParts table.. now the problem is that i need to manually re-enter material in ServiceParts again beacuse it doesn't get TaxRate from material table but if i enter new record than it's working fine.

So i'm asking is there any option to somehow update previous records without re-enter all again?

Database attached...

Thanks

[EZ Access Gideon]

Hi, You say created a work order table, but I do not see it in your example.I think that is the problem. Your fields in your workorder form are not stored anywhere because there is no table.

[faca]

Hi, You say created a work order table, but I do not see it in your example.I think that is the problem. Your fields in your workorder form are not stored anywhere because there is no table.

I'm sorry i've attached wrong database


I've managed to connect tables together but it always says new service id even though i clicked work order button in service form?

[EZ Access Gideon]

I changed a few things in your database to make it work.

1: If you load a new 'workorder' for a 'service', the 'workorder' does not yet exist. The query 'Workorder' will not show anything. You can not add the workorder, because it does not know to which 'serviceID' it need to link. Therefor I changed the query to use LEFT JOIN's:

SELECT Customer.FirstName, Customer.LastName, Customer.Contact, Vehicle.Make, Vehicle.Model, Vehicle.LicensePlate, Vehicle.VIN, Vehicle.EngineType, Vehicle.EngineVolume, Vehicle.EnginePower, Service.Kilometers, Service.ServiceID, tWorkOrder.WorkOrderID, tWorkOrder.WorkOrderNumber, tWorkOrder.DateOfReceivement, tWorkOrder.DateOfHandOver, tWorkOrder.DescriptionOfWork
FROM ((Customer LEFT JOIN Vehicle ON Customer.[CustomerID] = Vehicle.[CustomerID]) LEFT JOIN Service ON Vehicle.[VehicleID] = Service.[VehicleID]) LEFT JOIN tWorkOrder ON Service.ServiceID = tWorkOrder.ServiceID;

What it does is,when you open the form selecting the ServiceID, it will show the serviceID even if the WorkorderID does not yet exist. Now, if you start adding data, it will create a workorder linking to your ServiceID.

2: From the service form the button did not work to open the workorder. I changed the WHERE condition in the macro from '="[ServiceID]=" & [ServiceID]' to '"[ServiceID]=" & [ServiceID]'. The '=' sign at the beginning was double.

[June7]

Forms are for screen display, reports are for printing. Build a report and open report filtered to desired work order.

This is all basic Access functionality. Have you studied an introductory tutorial book?

[EZ Access Gideon]

Hi Faca,

Your second question is a bit more tricky. I would update the field [serviceparts].[taxrate] with [material].[taxrate] on the event OnUpdate. I saw you tried to do this in one of the forms, but your form was not working on my Access version.

[faca]

I changed a few things in your database to make it work.

1: If you load a new 'workorder' for a 'service', the 'workorder' does not yet exist. The query 'Workorder' will not show anything. You can not add the workorder, because it does not know to which 'serviceID' it need to link. Therefor I changed the query to use LEFT JOIN's:

SELECT Customer.FirstName, Customer.LastName, Customer.Contact, Vehicle.Make, Vehicle.Model, Vehicle.LicensePlate, Vehicle.VIN, Vehicle.EngineType, Vehicle.EngineVolume, Vehicle.EnginePower, Service.Kilometers, Service.ServiceID, tWorkOrder.WorkOrderID, tWorkOrder.WorkOrderNumber, tWorkOrder.DateOfReceivement, tWorkOrder.DateOfHandOver, tWorkOrder.DescriptionOfWork
FROM ((Customer LEFT JOIN Vehicle ON Customer.[CustomerID] = Vehicle.[CustomerID]) LEFT JOIN Service ON Vehicle.[VehicleID] = Service.[VehicleID]) LEFT JOIN tWorkOrder ON Service.ServiceID = tWorkOrder.ServiceID;

What it does is,when you open the form selecting the ServiceID, it will show the serviceID even if the WorkorderID does not yet exist. Now, if you start adding data, it will create a workorder linking to your ServiceID.

2: From the service form the button did not work to open the workorder. I changed the WHERE condition in the macro from '="[ServiceID]=" & [ServiceID]' to '"[ServiceID]=" & [ServiceID]'. The '=' sign at the beginning was double.

Hm... i've tried to fix this with your advice using LEFT JOIN's but i don't know why it shows only WorkOrder for first Service than everytime i open other Service and click WorkOrder button it only shows first one..