Please wait. Just checking a few things.
Please wait. Just checking a few things.
I have attached my latest.
Please note. I have not deleted any tables, I have just hidden them in the relationships window.
Result
A Client can only have One Contact.
A Client can have Many Sites.
A Site can have Many Clients and Many Contacts.
Thanks Rain all your statements are true
A Client can only have One Contact.
Yes a client can only have one main contact
A Client can have Many Sites. Yes a client can work at multiple sites and a site can also have multiple clients working at it
except the last statement
A Site can have Many Clients and Many Contacts.
Is not entirely accurate as it is the client that has the contacts the site does not have contacts essentially. However as long as we can match the contact to the client and the site then this may be irrelevant
Thanks
Does it still need to have a ClientContactJunction table?
I cannot see how 1 site can have 1 client with different clientsite contacts
Does it still need to have a ClientContactJunction table?
It does if a Client has multiple contacts. This is for Clients not Clients at a Site.
I cannot see how 1 site can have 1 client with different clientsite contacts.
You will need to enter in the ClientsSitesJunction Table the Same Client, Site Combination with different Contacts.
This is where I would use either Continious Forms or Datasheet as a Sub Form to Sites. I have no idea what you intend to do.
It does if a Client has multiple contacts. This is for Clients not Clients at a Site. No --> one client only ever has 1 main (head office) contact, this may change when the contact leaves or is replaced however there will always be only 1 main company contact
You will need to enter in the ClientsSitesJunction Table the Same Client, Site Combination with different Contacts. OK
This is where I would use either Continious Forms or Datasheet as a Sub Form to Sites. I have no idea what you intend to do. This is where I am confused as the contact is a client contact not a site contact overall. Quite often the same client contact can be the contact at multiple sites
Furthermore...
(rarely but occasionally a client contact will change to a different client and it is possible they may change to be the new client contact on the same site)
Does all this make sense, these relationships are the root of my problems?
Thanks
You will need to enter in the ClientsSitesJunction Table the Same Client, Site Combination with different Contacts. OK
This is where I would use either Continious Forms or Datasheet as a Sub Form to Sites. (Change the word Sites to Clients) I have no idea what you intend to do. This is where I am confused as the contact is a client contact not a site contact overall. Quite often the same client contact can be the contact at multiple sites
Have a look at the attached sample.
OK yes I admit you are correct The datasheet displays all clients/ Site contacts and the combo box changes clients and the main contact is displayed as well. It is correct and my way is not...
the only thing I don't like is the way it is displayed, the navigation and the way the data is entered
Seeing at the length of time I have now spent on this project the reality seems to be that I get my entire database up and working in this method first and then if possible change it later to different display, navigation, data entry, etc
I just need it all to work first, functionality is the most important thing
Thank you for being so patient
Since I have annoyed you last I tried to branch out alone with your technique and build a form for Sites, hopefully I have not implemented too many bad practices at this stage
Here is the result, notably I still need to be able to requery cboStates to display it automatically
Attachment 4882
Thanks Again
I will post later.
You need to learn Queries next.
Have a look at "frmClientContactsSub" in design View.
Open the properties and move to the DATA Tab.
Look at Record Source. Click in the White line and then click on the Ellipse. The three dots on the far Right. This will show a pectoral view of the Query. Notice how it uses three different Tables. By using a query in this way there is no code required to populate the Controls on the form.
Try using this method on your site form.
Now for the bad news.
Because of all the changes you made in code and the copying of forms your Database is ready to CRASH.
Solution.
Create a new blank Database.
Import all the Tables and the queries that you wish to use.
Next delete the code behind the forms you wish to keep. Paste the Code into a word document.
With all the Forms that you wish to keep go to Properties, Other Tab, and find Has Module. Change this to NO.
Now import these Forms into the new Database.
Copy the code from the word document into each form.
Compile and then do a compact and repair.
You should now have a clean Database.
Have a look at "frmClientContactsSub" in design View.
Open the properties and move to the DATA Tab.
Look at Record Source. Click in the White line and then click on the Ellipse. The three dots on the far Right. This will show a pectoral view of the Query. Notice how it uses three different Tables. By using a query in this way there is no code required to populate the Controls on the form.
Try using this method on your site form.
Thanks Rain
This is actually how I made my qryfrmClientsSite for my sub form for frmSites
Is My form made correctly?
Either way you are correct up until a week ago or so I was writing all my queries in SQL and not using design view at all
Now I am using design view and then adding extra stuff after, for example a where clause is still easier for me than criteria, however I am working on it
Cheers
On it, not to worry too much I have already had to do this a few timesNow for the bad news.
Because of all the changes you made in code and the copying of forms your Database is ready to CRASH.
Solution.
Create a new blank Database.
Import all the Tables and the queries that you wish to use.
Next delete the code behind the forms you wish to keep. Paste the Code into a word document.
With all the Forms that you wish to keep go to Properties, Other Tab, and find Has Module. Change this to NO.
Now import these Forms into the new Database.
Copy the code from the word document into each form.
Compile and then do a compact and repair.
You should now have a clean Database.
Thanks for the tip, will make a fresh one asap, tried to this afternoon but have to figure out which forms, code and even tables I still need
Didn't know I could use word I have always used notepad or a text editor of some variety, the use of notepad will probably explain why my indenting was so bad
frmSites needs a query to include "State" as part of your Record Source. The Control State should be a Text Box not a Combo Box.
Site Name is not required in the sub Form.
Going back to States. You need a Form to add Cites and their State. This can be called when a new City is not on the list.