Duplicate vehicle reg. (1 Viewer)

We have a database where we deal with car repairs.
We enter the customer name and then a dropdown list shows the Vehicle registration number, Make and Model. These are attached to the customer name (from table “Customers”) in the table “Vehicles”.
The VehicleReg is the primary key, so no duplicates!
However, sometimes these vehicles are bought and sold, and the new owner wants work done on the car, we to enter the Customer name on the form, then add the VehicleReg, and we have a problem adding this vehicle to the Customer, as it already exists in the database in prior jobs with the previous owner.

I’ve tried to set up, so that when the VehicleReg is duplicated, I can change the owner, but this keeps stalling and going round in circles.

Suggestions on a postcard please.

Stu

you need to provide more information - table structure and relationships for a start.

also clarify what 'stalling and going round in circles' means. You get errors? if so what errors? you don't get the required result? - if so what result do you get.

And clarify what version of access you have, is the database split, is it multi user, is the backend access or something else?

Thanks for the quick reply CJ, I have to go to a meeting now, but I will hopefully give you the info you requested this afternoon. Once again, thanks for looking at my post.

I have got somewhere since my last post, however I am repeatedly asked to enter the object “VehicleReg” to get to the form which can make the changes (i.e. the vehicle owner “Customer”).
I would like somehow, to automate the “VehicleReg” (as entered on the first form “JobNumber”), to appear on the forms I am using to go through this process,

Here are the details you requested.

This issue pertains to 3 tables:
JobNumber, Customers, & Vehicles

JobNumber field structure is:
JobNo Autonumber
Customer Text, lookup from table Customers
Date Date field no connections
VehicleReg Lookup from table vehicles
VehicleMake Updates when Vehicle Reg is selected, if on system
Model Updates when Vehicle Reg is selected, if on system
I have not entered a couple of items as they are completely independent.

Customers field structure is:
CustomerName Text
Address1 Text
Address2 etc…………….

Vehicles field structure is:
VehicleReg Text (PrimaryKey)
VehicleMake Text
Model Text

When entering data on the JobNo form, field “JobNo” auto updates; “Customer” is a dropdown from table “Customers”; “Date” chooses todays date but can be edited; “VehicleReg” is a dropdown showing cars already entered for the customer in “Customer” field, “VehicleMake” & “Model” update when the “VehicleReg” is entered.
If the “VehicleReg” is not registered to our customer on the system, a form opens to enter their new vehicle details: “VehicleReg”, “VehicleMake” & “Model”. However if the “VehicleReg” is on our system belonging to another customer, we are told that this would create a duplicate entry. I have created a form to resolve this issue, but before that opens for editing I am asked to enter the VehicleReg yet again.

How do I automate the entry “VehicleReg” (as entered on the first form “JobNumber”) to appear on the forms I am going through? Sorry to repeat myself.

I am using MSAccess2016, the database is split (MSAccess backend on server), no more than 3 users.

Hope this is enough info, feel free to ask more.

Where a previous owner exists you need to use an update query or the sql equivalent in a vba procedure to change this to the new owner.
I would also recommend you have an owner history table which should be used to store the previous owner details rather than just overwrite them

As for repeatedly being asked for vehicle reg, press ctrl break when it happens to identify where the code is asking for the info, then check why. Recommend you check the form record source as it is probably required as a parameter

ridders said:

Where a previous owner exists you need to use an update query or the sql equivalent in a vba procedure to change this to the new owner.
I would also recommend you have an owner history table which should be used to store the previous owner details rather than just overwrite them

Click to expand...

I have done all of that already, and it's all working well. However I am still being asked to enter the VehicleReg to go into each form. I would like this automated,

I am still being asked to enter the VehicleReg to go into each form

Click to expand...

your structure looks OK (you need to store make and model in the job table because vehicle reg can be transferred to a different vehicle)

with regards being continually asked, this may be to do with the way you have set your forms up, what are the form recordsources, combo /listbox rowsources and any dlookups you may be using

You are missing one table.

Vehicle needs a junction table to Customer.

One customer can have more than one vehicle.
One vehicle can have more than one customer (as you've found with transfer of ownership).

Junction table would have CustomerID and VehicleID to join the two together.

Job would then link off of the junction table as each job is paid for by A customer (normally) for work done on A vehicle.

Mark_ said:

missing one table.

junction table to Customer.

Click to expand...

This arrangement is generally referred to as "Many to Many" which is a slightly misleading description because it is actually two One to Many's!

I did a blog and some YouTube videos describing it here:-

http://www.niftyaccess.com/many-to-many-relationship/

Sent from my SM-G925F using Tapatalk

I'm late to this party, but I don't think you should be using VehicleReg as a primary key. If one of your customers has a personal number plate, they will be very likely to transfer it to a new car when they change, and I suspect you don't really want the servicing history of the old car assigned to the new one.