Form help - how to get this working ? (1 Viewer)

[DigitalS27]

Hi guys,

So, I have created a database with 3 tables:

tblEquipment,
tblCustomer,
tblEquipementMaint

So what we do is we enter the customer name, customer is related to tblEquipment, and equipment is related to equipmentmaint.

First the customer is entered on the database form, then the equipment on the equipment form, then the equipment serial number is selected on the equipmentmaint form and the maintenance done is entered on this form.

What I want to do is create another form where there is a dropdown box to select the customer, then another dropdown box for only that customer's equipment. When selecting the specific equipment serial number, there should be a subform to only display the maintenance history of only the selected equipment serial number.

Can somebody please help me on how to create a form like this ?

 

[GinaWhipp]

Hmm, so let me understand this... It's only one customer per one piece of equipment?

 

[DigitalS27]

Customer can have many equipment. 1:*
Lots of equipment can belong to only one customer.
Maintenance can be carried out on equipment more than once a year.

Just woke up so can't think clearly what the relationship syntax are :banghead:

 

[GinaWhipp]

Well, it's 3:00 am here and this is last post till I get some rest (and I did understand what you meant)...

Then you are missing at least one Junction table...
tblCustomersEquipment
ceCustomerID (relate to Customers table)
ceEquipmentID (relate to Equipment table)

This will make it easy to create a Form/Subform with Customers related Equipment and then what maintenance that equipment has had. Oh, only if a new record for maintenance is entered everytime maintenance is done on any one piece of equipment.