Cascading Combo Box lookup

[veroli]

Hello all,

hoping someone can point me in the right direction. Having not used access for many years as am an infrastructure guy
but with an understanding of databases to a basic level, I've embarked on doing some access work.

I'm building a simple vehicle management db for a small garage that services and repairs cars.
I've built the tables and relationships and done some simple forms.
I am trying to build an order form where
1. The user selects a customer from a combobox
2. The second combo box displays the registration of the car(s) that customer owns.

I've managed this with a query and using cascading combo boxes and it re-queries successfully, as some customers have more than one car, the registrations display correctly and reload
when a customer is selected in combo box 1.

What i want to now do is display the rest of the vehicle details on the form(or maybe sub-form)

From there i can generate an order/maintenance request for the that vehicle for that customer.

It seems like it should be easy but am not quite sure how to do it.
I think it should be something like

get vehicle details(the fields) from vehicle table where registration equals combo box2

Not sure also how to display it sub form ? data grid etc

the idea will be then the order/maintenance is saved in a another table as a record and an invoice produced.

thanks for any help



veroli

[mike60smart]

Hi Veroli

Welcome to the forum.

Can you upload a zipped copy of your database?

[mike60smart]

You would normally have a Main Form which has Controls bound to fields in a Table.

So your selection of the Customer and vehicle would store the Primary Key values in the underlying Table.

You would then need a related Table for the Order and another table for the Maintenance carried out.

[veroli]

Thanks Mike,

i will have a look at doing what you said,
Ive already got some customer info in it at the moment albeit test data so would need to anonymise it to upload which i will do later

thanks
again

[Bulzie]

Similar to mentioned above, you could have a main form that has the Customer and Vehicle combo boxes in header and a Vehicle subform in the detail section. The Vehicle subform recordsource would be a query of the Vehicle table with criteria being the Vehicle combo box value/key. On the Vehicle combo box, you can use AfterUpdate Event to requery the subform once they select the specific car. Then the Vehicle detail should not show on the subform.

[ArviLaanemets]

I assume, you have a table, where are registered, which cars all customers have (something like tblCustomerCars: CustomerCarID, CustomerID, CarID).
And I also assume, that the form you are designing, is based on cars table.

So instead of cascading combos, why don't you use a single one, linked to CarID, and as RowSoure having a query like

SELECT cc.CarID, cust.CustomerName & ": " & car.CarRegistration As CustomerCar FROM (tblCustomerCars cc INNER JOIN tCars car ON car.CarID = cc.CarID) INNER JOIN tCustomers cust ON cust.CustID = cc.CustID ORDER BY 2 ASC

When you click on combo, you'l have selections like "John Smith: XYZ 123", ordered by customers and then by cars of this customer. When you select any of selections, the combo will get the value of CarID of selected car. And no code is needed!