Complex Coding --> Please Help

[JFo]

Morning Rain, I am still struggling to work out the benefits of this design. For the unknowing user I still prefer my original design --> using frmMainMenu select client from combo, if the client does not exist click the button to create new or if you want to edit client click edit current button (this also works well for Sites, contacts, inspections)

There is no other information at this time that needs to be directly associated with client besides contact infomration, so nothing more required in the table.

Yes there is one main client contact and then many client contacts for different sites.



In frmMainMenu this has been allowed for by adding a checkbox to the frmContact (available from frmClients and frmMainMenu) that indicates whether this is the Client's main contact

Site also works well in my current design, maybe the only thing that is a little complicated with the clients, sites, contacts relationships is adding/ associating a client with a site, do you see a better way to do this? If I change my Junction design to have a single junctionID primary then I need to re-code the linking as I used a compound key originally

What should we do now?

Thanks

[Rainlover]

What should we do now?

I am not sure what you are going to do but it is time for me to bail out.

Your design is something I can't support.

I wish you well with the project.

[JFo]

I thought this was the design you helped me create, what is wrong?

This is the reason why I am on this forum to create the correct design and value any opinions disclosing otherwise

This is why I have created the secondary menu and starting again to find a better way I just can't see why it is better then what we were creating in the first place at this stage

I thought we had got a lot working in the first design, I am confused as to why it is incorrect

Thanks

[JFo]

I apologise for pushing my incorrect designs I am trying to do myself if they are not encouraged. In all my IT works I try to implement best practices and designing this database is perhaps even more important

I really want to get my design correct and I need your assistance

Is this acceptable the design for frmClients2 with the command buttons (I think this will be OK for the users and seems fairly self-explanatory)

If you are willing to give me another chance I am prepared to start from the start and work with a different design

Nevertheless, if you still feel I am a lost cause I am disappointed however thank you for everything you have done up to now. I have learnt a lot I think

Thanks

Attachment 4876

[Rainlover]

OK. One more try.

First create that Client Form without the Combo.

Instead, at the top of the Page use a new Combo that will find the client you wish to edit.

There is a wizard that will do this if you don't have the code.

Next. Please explain why you don't want Navigation buttons. By hiding them all you are doing is forcing the user to use the Page Down and Page Up Keys. Same effect is it not.

[Rainlover]

There is no other information at this time that needs to be directly associated with client besides contact infomration, so nothing more required in the table.

If you are going to attach a Contact directly to the Client then you need to add ClientID to your Client Table and Client Form. In the form it would be a combo so that you can select from the Contacts table.

[JFo]

OK. One more try.

First create that Client Form without the Combo.


I have changed the combo to a text box, not sure if you meant to change or remove, can delete easy if need be

Instead, at the top of the Page use a new Combo that will find the client you wish to edit. Done... a new combo has been created with all client details included, I would usually put code in the afterupdate of the combo however awaiting instruction

Next. Please explain why you don't want Navigation buttons. By hiding them all you are doing is forcing the user to use the Page Down and Page Up Keys. Same effect is it not. I figure there is less chance of a user making a mistake. For the untrained layman that is not particularly computer savvy I would like to control everything the user can do with command buttons. You are correct that you can get around this with hotkeys however in the future the interface will be almost stand alone and will not allow this, I can leave them active for our purposes if you would prefer. I will not actually allow users to delete records either and therefore I removed the delete button from frmClients2

Thanks Rain

Attachment 4877

[JFo]

If you are going to attach a Contact directly to the Client then you need to add ClientID to your Client Table and Client Form. In the form it would be a combo so that you can select from the Contacts table.

Not quite sure what you mean by this ClientID does exist in Client Table and client form, I have changed the clientID text box to a combo...

Am I correct in thinking that I should make the control source a query and somehow make this combo return contact info and put the clientID in both tables

Thanks

[Rainlover]

I wrote ClientID.

Should have been ContactID

[JFo]

I wrote ClientID.

Should have been ContactID

No problem I have done this, ContactID combo added to form and contactID added to table

Does this still allow for 1 client to have many contacts (including 1 main contact and many other contacts for different sites, etc) and 1 contact to serve multiple sites and occasionally change from 1 client to another client?

Thanks

[Rainlover]

Table Adjustments, as per attached.

Add ContactID to tblClients
Add ContactID to tblClientsSiteJunction
Delete ClientID from tblContact

Look at the Form I created. Any questions

[Rainlover]

Does this still allow for 1 client to have many contacts (including 1 main contact and many other contacts for different sites, etc) and 1 contact to serve multiple sites and occasionally change from 1 client to another client?

Good thinking.

My latest version should solve this problem. Let me know if you think otherwise.

[JFo]

OK let's see if I follow your logic

Table Adjustments, as per attached.

Add ContactID to tblClients
Add ContactID to tblClientsSiteJunction
Delete ClientID from tblContact

Look at the Form I created. Any questions

By adding ContactID to tblClients this will allow a main contact to be added,

adding the contactID to the clientSiteJunction will assign contact to client as per site (what about when there is multiple contacts for same client on one site? this could happen)

Deleting clientID from tblContact means a contact can be associated with more than 1 client

Your form edits, adds and deletes fine for adding a client with navigation buttons, the only thing it does not do is requery or refresh the combo when a new client is added from what I can see

[JFo]

My latest version should solve this problem. Let me know if you think otherwise.

As per previous comments it seems to allow for everything I suggested even if not designed to do it yet except my above point

(what about when there is multiple contacts for same client on one site? this could happen)

[Rainlover]

By adding ContactID to tblClients this will allow a main contact to be added,

Correct.

adding the contactID to the clientSiteJunction will assign contact to client as per site (what about when there is multiple contacts for same client on one site? this could happen)

Yes this should happen but we need to test.

Deleting clientID from tblContact means a contact can be associated with more than 1 client

No. Answered more fully in my last point on this thread.

Your form edits, adds and deletes fine for adding a client with navigation buttons, the only thing it does not do is requery or refresh the combo when a new client is added from what I can see

You could requery the Combo Box to overcome this. I am not sure which event to use but try "On Enter" and "On Got Focus" Test this throughly.

If you want a Client to have more than one contact (Other than site Contact) you will need a Junction Table.

Change required.

Delete the Relationship between tblClients and tblClientsSitesJunction.

Create a Relationship between tblClients and tblContacts Via ContactID.