Updating 2 tables in 1 to Many

[David Mitchell]

Apologies for the Subject Heading!

I am working on a small invoicing app for a local Tyre and Exhaust
Centre. They want to be able to type in the Vehicle Registration
Number and it automatically bring up the customers name and address.
This in itself seems fairly straightforward except that 1 customer can
have many vehicles.

My tables are set up as follows:

TblCustomer (CustID as PK) in one to many with tblVehicle (VehicleID
as PK and CustID as FK)
tblVehicle (VehicleID as PK and CustID as FK) in one to may with
tblInvoice (InvoiceID as PK and VehicleID asFK)
tblInvoice (InvoiceID as PK and VehicleID asFK) in one to many with
tblInvoiceDetail (InvoiceDetailID as PK and InvoiceID as FK)

I thought of using a combobox and NotInList but don't know how to get
the data in both tables updated. There are other issues like change
of ownership etc.

Can anyone give any pointers? Thanks in advance for any help.

[hamking]

Each customer may have more than 1 vehicle, but each vehicle's ID
should correspond to a CustomerID in tblVehicle. Creating a query
with VehicleID as input value will pull correspnding Customer info
from tblCustomer.

What data needs to be updated, thought you just needed to pull
customer info. With change of ownership do they still need old owner
info linked to the car, can they just delete the old info.
da**************@talk21.com (David Mitchell) wrote in message news:<c3*************************@posting.google.c om>...

Apologies for the Subject Heading!

I am working on a small invoicing app for a local Tyre and Exhaust
Centre. They want to be able to type in the Vehicle Registration
Number and it automatically bring up the customers name and address.
This in itself seems fairly straightforward except that 1 customer can
have many vehicles.

My tables are set up as follows:

TblCustomer (CustID as PK) in one to many with tblVehicle (VehicleID
as PK and CustID as FK)
tblVehicle (VehicleID as PK and CustID as FK) in one to may with
tblInvoice (InvoiceID as PK and VehicleID asFK)
tblInvoice (InvoiceID as PK and VehicleID asFK) in one to many with
tblInvoiceDetail (InvoiceDetailID as PK and InvoiceID as FK)

I thought of using a combobox and NotInList but don't know how to get
the data in both tables updated. There are other issues like change
of ownership etc.

Can anyone give any pointers? Thanks in advance for any help.

[Pieter Linden]

da**************@talk21.com (David Mitchell) wrote in message news:<c3*************************@posting.google.c om>...

Apologies for the Subject Heading!

I am working on a small invoicing app for a local Tyre and Exhaust
Centre. They want to be able to type in the Vehicle Registration
Number and it automatically bring up the customers name and address.
This in itself seems fairly straightforward except that 1 customer can
have many vehicles.

My tables are set up as follows:

TblCustomer (CustID as PK) in one to many with tblVehicle (VehicleID
as PK and CustID as FK)
tblVehicle (VehicleID as PK and CustID as FK) in one to may with
tblInvoice (InvoiceID as PK and VehicleID asFK)
tblInvoice (InvoiceID as PK and VehicleID asFK) in one to many with
tblInvoiceDetail (InvoiceDetailID as PK and InvoiceID as FK)

I thought of using a combobox and NotInList but don't know how to get
the data in both tables updated. There are other issues like change
of ownership etc.

Can anyone give any pointers? Thanks in advance for any help.

This question appears to be backwards. If a vehicle can only belong
to ONE owner, then nothing to it. You can do something like load the
combobox with multiple columns...
SELECT Vehicle.VIN, Owner.LastName
FROM Vehicle INNER JOIN Owner ON Vehicle.OwnerID=Owner.OwnerID
ORDER BY Vehicle.VIN, Owner.LastName;

and then refer to the hidden columns in your combobox...

me.cboVIN.Columns(0) <==VIN
me.cboVIN.Columns(1) <==Owner.LastName

[David Mitchell]

Thanks for both your answers, how would I deal with new vehicle
registrations not on the system (for existing and new customers)?