Complex Coding --> Please Help

[Rainlover]

however 1 major form that I require is to add an inspection for a client at a site

Which Form do you want to do this in.

[JFo]

Clients does not open from the Menu.

Can't see how it could work. There is no code behind the Button and no Event.

This is peculiar why it is losing its onclick event, you are correct there is no code this is the only button that has a macro embedded

I have changed it to code anyway

Attachment 5031

[JFo]

however 1 major form that I require is to add an inspection for a client at a site

Which Form do you want to do this in.

Well I have been trying to do this all in frmInspections

Thanks

[Rainlover]

button that has a macro embedded

I know 2010 is speecially designed for Macros. But I don't like them. Old school thinking I guess. My preference is not to use them at all. Except Autoexec.

Well I have been trying to do this all in frmInspections

I thought so. If you want to start with the Client then the Client should be on the Main Form and then Inspections should be on the Sub.

Look at your Relationships Layout. Print this out on A3 if you can. Then use it as your Map.

Should not your Equipment be attached to the ClientSiteJunction Table.

Your search would therefore be Client, Site and then Equipment,

[JFo]

My preference is not to use them at all. Yes I prefer code as well, however there has been at least a couple of things that I can get to work in macro and not in code

[JFo]

Should not your Equipment be attached to the ClientSiteJunction Table. This is a good idea at the moment it is only attached to client however it would be handy if it was attached to site as a piece of equipment could move from client to client and site to site

[JFo]

Your search would therefore be Client, Site and then Equipment,

Thinking client-->Site-->Inspection-->Equipment, however this is the bit I am stuck on

As an Inspection is only for one client on 1 site they could just be added at the time a new inspection was added however I can't even work out how to then search for the equipment and then open the checklist, this is the part I think I really need to solve

Not sure on exactly what you are suggesting but if I follow then it sounds like if you are adding another form in clients then it would be clients main, inspections 1st sub, equipment search 2nd sub, equipment 3rd sub and checklist 4th sub???

I know I am probably making harder as usual but not really understanding where you are going with this one

Thanks

[Rainlover]

A quick responce. Will leave it to you to think about.

Kill tblClientEquipmentJunction.

Add ClientSiteID from tblClientsSitesJunction to tblEquipment.

Change in tblInspections, ClientID to ClientSiteID.

Have a look at the attached Relationships. You are really going to hate me now, but remember that thing I sent you on the various stages of designing a Database.

This is not guaranteed to be 100% correct, but I think it is a lot closer.

Going out for a while.

[JFo]

A quick responce. Will leave it to you to think about.

Kill tblClientEquipmentJunction.

Add ClientSiteID from tblClientsSitesJunction to tblEquipment.

Change in tblInspections, ClientID to ClientSiteID.

Have a look at the attached Relationships. You are really going to hate me now, but remember that thing I sent you on the various stages of designing a Database.

This is not guaranteed to be 100% correct, but I think it is a lot closer.

Going out for a while.

Nothing is attached not sure if you meant to.

No hatred for you, just frustration at myself

It makes sense that an inspection is linked to a client and site relationship, however if I do the same for equipment how easy is it to then change the equipment to a different site or a different client?

Am well aware at this stage that I am going round in circles a bit, however I really, really struggling with integrating the 3 forms together it is doing my head in

Thanks

[Rainlover]

There is a saying in the Programing world.

Excessive code is the sign of a poorly designed Database.

[Rainlover]

It makes sense that an inspection is linked to a client and site relationship, however if I do the same for equipment how easy is it to then change the equipment to a different site or a different client?

Add the equipment again under a new Site or Client. Retire the old one. You can still pick up its history. Not a big concern at this stage.

[JFo]

OK learning the hard way for sure however can change the relationship easy, obviously the forms will then need to be changed to allow for client and site additions. I assume you also meant to do a link between tblClientsSitesJunction and tblInspections

Still at a loss how to select or add inspection --> search for equipment and add checklist

In this design there is a lot less code the only thing really that requires the code is the search and populating different checklists and tblResults, however at the moment this is not working as I would like

Thanks

[Rainlover]

Still at a loss how to select or add inspection -

Your Inspections Form would have the ClientSite in the header Then Equipment in the Sub. That's all.

I am not sure what Checklist is used for so I can't help with this one at the moment.

[JFo]

Then Equipment in the Sub. That's all. I want it like frmSearch to search for equipment

I am not sure what Checklist is used for so I can't help with this one at the moment. This is the whole point of the database a user selects the piece of equipment and this opens the checklist (the checklist contains all the questions for that equipment type and default answers if the equipment has been inspected before, etc and then the user answers every question where they need to and the data is populated into tblResults)

[Rainlover]

I want it like frmSearch to search for equipment

Create a new form for that purpose. One form cannot do everything. KISS


I am not sure what Checklist is used for so I can't help with this one at the moment. This is the whole point of the database a user selects the piece of equipment and this opens the checklist (the checklist contains all the questions for that equipment type and default answers if the equipment has been inspected before, etc and then the user answers every question where they need to and the data is populated into tblResults)

I need to think about this one. I thought you were going to use tblResults to do this.