Queries and list box

[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

 

[Galaxiom]

The subforms will be nested as a series. JobList inside JOBS inside CARS inside CUSTOMERS.

Use the Master Link Fields and Child Link Fields Properties of the subformcontrols to connect them together.

The search combos will be unbound and used as a filter on the records in the Recordset of the form. There are many thresd on the forum about searching from combos and textboxes.

 

[wotamelon]

The subforms will be nested as a series. JobList inside JOBS inside CARS inside CUSTOMERS.

Use the Master Link Fields and Child Link Fields Properties of the subformcontrols to connect them together.

The search combos will be unbound and used as a filter on the records in the Recordset of the form. There are many thresd on the forum about searching from combos and textboxes.

Thx for the reply,
My 'joblist' is a list of items such as those ones you get from the inventory. To display them inside JOBS, how do i do that? as in in datasheet view, then I go filter them out on the using combo box etc etc?

But the thing is, this MAIN FORM where all the subforms will embed is only for display, not for editing. The input of data is in different forms. do i make any sense? =S

hope im not confusing u

 

[Galaxiom]

Every subformcontrol has a SourceObject property. This is normally a form it displays. That form has a RecordSource property which is a query that selects the data you want to show.

If you are not editing the data use a Snapshot RecordsetType.

 

[wotamelon]

how about I post up the template with some data I've made up?

I've made a series of subforms and most of them link (expt for JOB LIST)...

but 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.

so yeh I'll post it up.

thanks for giving me a start up tho from previous replies.

 

[Galaxiom]

Use Continuous Forms or Datasheet view in the form properties.

 

[wotamelon]

I have successfully set up the interface for linking up everything together!

Thx Galax!

However, On my job order, the same dates keep repeating.
It's in a Query, in subform, view in contiuous form
i.e.
JOB DATE ItEM Qty
_________ ____ ___
10/2/10 Product A 2
15/2/10 Product B 1
15/2/10 Product A 1
15/2/10 Product C 1

How do I fix this?
That's problem 1

The second problem is that, because JOB orders can come from cars (more than 1) that belongs to same owner. How do I filter these criteria out so that when the car detail displays, the JOB ORDER wont display all JOBS from that CUSTOMER?


THx
ill post my template up