ListBox, ComboBox criteria and query

[wotamelon]

Hi all,

I am troubled by this problem, I'm new to ACCESS (last time i touched base on VBA codes was 5 or 6 yrs ago where I took Microsoft Visual Basics for beginners)

Please help! My workplace was to develop a search and traceable system for their customers and job orders, I've put in some of the data in tables.

The Scenario is a CUSTOMER, CARS, JOB ORDERS picture.

A customer can own more than one car.
A car belongs to only one customer.
The car can have more than one job performed on it.

I have the following:

1 Tbl for CUSTOMERS (name, address, phone no. etc)
1 Tbl for CARS (make, model, yr..)
1 Tbl for JOB orders (i.e. CarRegNo. or CarTagNo., JOB DATE, Jobs performed in list, cost)
1 Tbl for Job Description (list of jobs i.e. engine washing, adjust handbrake)

I need to have them all displayed on 1 main form.
Problem is I don't know how to connect them together. My idea was that on a clean MAIN form, there's a combo box of CarRegNo. or CarTagNo. Then it will filter out to its JOB DATE. Then from the JOB DATE will display its corresponding JOB DETAIL.

I'm happy to use subform/s. I don't know how to link all of it together. It seems like coding needs to be involved. That's fine with me, but I don't know how to go about it now.


Please help

 

[Trevor G]

You need the design of your tables to include ID fields.

Customer table should have an ID Field as Autonumber (DataType)

Car table should have something like CustomerID Field as Number (DataType)

Job Table with CustomerID as Number (DataType)

Then you need to go to the Relationships window, and relate the tables via the Customer ID field in the Customer table, this will allow you to create relationships of One to Many.

That will then allow you to create your form with subforms that relate to the relevant tables and cars and jobs.

No VBA needed at this stage from your thread.

Hope this helps

 

[wotamelon]

You need the design of your tables to include ID fields.

Customer table should have an ID Field as Autonumber (DataType)

Car table should have something like CustomerID Field as Number (DataType)

Job Table with CustomerID as Number (DataType)

Then you need to go to the Relationships window, and relate the tables via the Customer ID field in the Customer table, this will allow you to create relationships of One to Many.

That will then allow you to create your form with subforms that relate to the relevant tables and cars and jobs.

No VBA needed at this stage from your thread.

Hope this helps

How should I set my primary key?


Right now...it's the following:

In Customer Tbl, CustomerID is an autono. (primary key)
In Car Tbl, CarReg is primary key?
In Job Tbl, JobID (number) is primary key?


And also, i'm faced with a dilemma. A Job order relates to car and its customer. Then, in the JOB order table should I include CustomerID and CARID as well?

 

[wotamelon]

This is what I have done so far....

but i cant seem to link up everything together...

problem now...
where there are more than 1 car for a customer, it doesn show up.
Where there are more than 1 JOB ORDER for a car, it doesn show up.
Where the JOB ITEM is Repeated, it doesn show up.

 

[Trevor G]

You must look at the relationships window and how you have them linked.

I have adjusted the relationships to One to Many and have created a new form with just one subform in the database.

I have added a combo box so you can select a customer name and the car detials then appear. The form name is frmCustomer, I hope you can now add the rest of your needs.

 

[wotamelon]

I've managed to take onboard what you mentioned about adjusting the relationship type and its subsequent effect.

I've put everything to one form, like a combo box that you've put in to search for the cars by using name. I've now added in the JOB ORDER as well.

problem is now the dates are the order dates are repeated as they appear in JOB ORDER. As well as the JOB ORDERS are not filtered according to the specific CAR.

please hav a look again for me =) if u or anyone else would like to shed some light!

many Thx

the attached file is an updated version