Design Of Database

[Melissa]

Hi

I need to create a database for windscreen repairs. I have one table for
"Clients" and one table for "VehicleDetails", also I have a table for
"Jobs".

In the clients table I have, "ClientID, ClientName,TelNo,CellNo"
IN the vehicledetails table i have" vehicleId, Make,MOdel, Reg,
VinNo,EngineNo"

In the JOBS table I have, ClientName,Reg,Make,Model

Now i have a form for each "Clients" and for VehicleDetails. I would like
to create the jobs Form now, and when I create a new job, i want to be able
to select the ClientName from a drop down list, and then that specific
clients vehicle information (Make,MOdel, Reg, etc) must automatically appear
in these fields on the Jobs form. I would like to avoid subforms if
possible?

Thank you

Melissa

 

[George]

Melissa,

I got a bit confused:

1) How your clients are related to the vehicles? One client owing more than
one vehicle?

2) By entering a new job this has to be: select a client, select a vehicle
he/she owns and then enter parts, date, amount etc?

GeorgeCY

Ο χÏήστης "Melissa" έγγÏαψε:

 

[Melissa]

Hi George

I cant believe It. Thats why everytime I have been trying to select a
client in the combo Box, it wont only select its specific vehicles. Also,
when I look in the ComboBox for the Clients, it creates a new "Row" in the
comboBox for every registration and vehicle?

I've really hit a blank with this one. How would I relate the Clients to
the vehicles?

Thank You

 

[George]

Hi Melissa,

As I understood well one client may own more than one vehicle and each
vehicle may have more than one maintenance. If so:

tblClients: ClientID(PrimaryKey), ClientName, Address, etc
tblVehicle: VehicleRegNo (PrimaryKey, if is so), ClientID (related to
tblClients)EngineNo, ModelID (this should be related to tblModels)
tblMakes: MakeID (perhaps autonumber-Primary key), MakeName: e.g. 1-BMW,
2-Volvo etc
tblsModels: ModelID(perhaps autonumber-Primary key), MakeID(number long
integer) to be related to the tblMakes, ModelName.

Please note that the above structure will be well as long as each vehicle
remains in the ownership of a single client. If the same vehicle may be sold
to another client, then you should create the so called many-to-many
relationship between Clients-Vehicles-ClientVehicles.

Please let me know if you need any further assistance.

Kind regards,

GeorgeCY


Ο χÏήστης "Melissa" έγγÏαψε:

 

[Melissa]

George.

Thank you very much. I have created the tables as you have explained.

Now to test whether it is related correctly. i have created a form "jobs"

On this form I have a drop down list to search for a client (which works)
Underneath that is a dropdown list for "VehicleRegNo", but it is not showing
any registration numbers in this drop down list, its blank.?

How do I get access to know that a vehicle belongs to a certain client (on
the form) ?

Thank you kindly

Melissa

 

[George]

Melissa,

First you must create the relationships between the tables.
e.g Tools – relationships – add all tables and then drag the ClientID from
tblClient to tblVehicles – Continue with the rest of the relationships.

Did you create a table with tblJobs? This should have three primary keys
i.e. ClientID and CarRegNo and date. And also some fields if needed.

Also a second table (tblJobsParts) related to the above to record all parts
for this specific job. Fields: ClientID, CarRegNo, PartID etc – This should
be related one to many with the above, i.e. one job with many parts

Secondly you may record all clients and their vehicle, either using the
tables or using a simple form based on tblClients with a subform based on
tblsVehicles (in order to assign to which client the vehicel belongs).

Finally you must create a form to record jobs (based on tblJobs and
tblJobsParts).

Have in mind that in order to show only the vehicles for the selected client
you should filter the vehicles drop down according to the client. Also you
must have this dropdown being requeried after selecting the client.

Hope you got a better idea.

GeorgeCY
[email protected]
remove 1 to contact me.


Ο χÏήστης "Melissa" έγγÏαψε:

 

[Melissa]

Hi George

Thank you very much for your help. I seem to be getting it right. However,
on my "Clients Form", I have a button which opens the vehicles form, this is
where I create vehicles for the specific client. this works, but i cannot
create More than one vehicle for a specific client. it lets me click" new
record" in the vehicles form, but when i open that client's vehicles again,
only the first one i added shows.

But, if i go into the clients table, and click + i can add as many vehicles
for that client as i want to, and then those vehicles i added DO show in my
vehicles form? howcome it only works from my table?

Thank you very much

Melissa

 

[George]

Melissa,

Have a look at the forms properties – You should select a Continuous Forms
as the Default View and not Single Form.

GeorgeCY

Ο χÏήστης "Melissa" έγγÏαψε:

 

[Melissa]

George

I have changed the form to "continuous" forms, but its still doing the same.
only the vehicles I add to clients in the "table" works. if i add it in the
form, it dissappears.

Thank You

 

[Melissa]

George.

I have changed the design of my database. I used a subform for vehicles,
and then I placed it on a "tab control", so when I switch to the next "page"
of the tab control, i select the vehicles for that client, and its working
brilliantly, thank you

However, on my "Jobs" form, when I select a certain client from the
dropdown, then I select a vehicle reg from the vehicle drop down list (which
belong to the specific client), and continue to load the "job info". This
works perfectly.

However, if the client reg is not there, i want to be able to click a button
(after selecting the client) which takes me into the vehicles form, and
shows existing vehicles for that client, and then I want to add a new
vehicle to the form, but if i do so...the vehicle wont save. I can only add
vehicles from the "clients" screen, not from the button on the "jobs"
screen. Why could this be happening?

thank you again. I hope my explanation makes sense.

Melissa

 

[George]

Melissa,

You can create a continuous form based on a query based on
tblClientsVehicles. This should filter the vehicles (in the query) of the
client shown in your opened form, e.g put a criteria under the client field
Forms![YourFormName]![YourClientFieldInThatForm].

Then, you can add a cmdButton on the form you described in order to open the
above form and filter records according to your client shown on the opened
form.

Have in mind that by closing the ClientsVehiclesForm you should requery the
DropDown you have for selecting the Jobs Vehicle.

Hope this helps.

George

You can

Ο χÏήστης "Melissa" έγγÏαψε: