Need help controlling a form

[Mr_Si]

Hi all

I am writing a db for the company I work for.
It is an Order Entry / Enquiry Form / Job Tracking database.

Our Company is a consultancy for which equipment is taken out on jobs to do measurements of sound etc.

Anyway, one of the features of my db is that I want to include an equipment booking section.
I have a Tbl_Project and a Tbl_Equipment which are linked via the "Equiry#" field in both forms. This is to allow the Tbl_Equipment table to know which project it is tied to in a one-many relationship.
Thus there can be many bits of equipment for a single job, which is exactly what I need.

Now, in my form, I have allowed for up to 8 bits of equipment, but the problem is that even though I've named each set differently, (a set is Equipment_Model, Equipment_Brand and Equipment_Type), they all have the same "record control" and if one bit of equipment is selected in one section, then all the equipment sections change to that type.

The "equipment form" is a subform in the main "enquiry form" and the page with this subform on is only made visible if a checkbox is checked on the home page of the enquiry form that says that the enquiry has been made into a live project.

How do I make it so that each equipment section is independent?
I'm thinking I do this with VBA code in the Form_Equipment module...? (hence why I've posted this in here and not forms).

I've attached some screen shots which I thought might be helpful.

The following links take you there:

Form View

Design View

Table Design

Relationship

I really hope this made some sort of sense. If not, please ask and I shall try to explain / answer your question(s)

 

[Kiwiman]

Hi Mr_Si

It looks like you have broken normalisation rules regarding having 8 separate fields for your equipement per project, in the one table - basically you are repeating info. What if a project required 10 pieces of equipment?

Just looking at this you will need at least the follwoing tables for:
1. Customer info (PK - Customer ID)
2. Euipment info - (PK - Euipment Id) - hold details of all your equipment available
3. Project info - (PK - Project id) - holds the Project Header info - linked to the Cusotmer table by Customer ID
4. Project ID - (PK - a combination of Project ID and Equipment ID) - this way you can have as many pieces of equipement you require for any project

 

[wazz]

just to follow up on kiwiman, #4 is important. if all of your equipment is in the equipment table, then you've "locked down" all of your equipment with a single enquiry#. you need another table, tblProjectEquipment, as kiwiman described. both PK fields will be FK lookups. (can you add a ProjectID field?)

other:
- don't use # in a field name.
- can your booked date just be a field in tblProject?
- do you have lookups for equip model and equip brand? not necessary?
- might rename tblCategory - too vague; equipment category? customer cat? project cat?

 

[Mr_Si]

Hi Kiwiman and Wazz, thank you for your replies.

It's been so long since my computer science days and thus, SSADM work, that some of the processes I have forgotten. I'd thought I'd had my tables normalised to 3NF.

So I have a new table called Tbl_Project_Equipment to in I only have the Enquiry_ID and the Equipment_ID. Are you saying that this should be the master table for the IDs? and the Tbl_Enquiry and Tbl_Equipment look up to these for their keys?

I think you are, so I am going to change this now. Do I therefore gather that in this Tbl_Project_Equipment, I can have both fields as non-key and have no PK set in it at all? or should I set the Enquiry_ID to be the PK?

Thanks,

Si

Edit: Oh, the reason why booked date was a separate table is that I wanted to be able to have the option to add multiple visits. We work on big building projects sometimes, which are long term jobs. Often requiring more than 1 site visit or something.

 

[wazz]

see num4 from kiwiman - both fields make up the PK. highlight both fields in the table window and click the PK button; there should a key beside both fields. so, the combination of the two fields must be unique. use pencil and paper and experiment by adding values to the two fields remembering that there can be no duplicates.