Page 9 of 16 FirstFirst 12345678910111213141516 LastLast
Results 121 to 135 of 232
  1. #121
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691

    Please wait. Just checking a few things.

  2. #122
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    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.

  3. #123
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    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

  4. #124
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Does it still need to have a ClientContactJunction table?

    I cannot see how 1 site can have 1 client with different clientsite contacts

  5. #125
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    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.

  6. #126
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    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

  7. #127
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    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

  8. #128
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    Have a look at the attached sample.

  9. #129
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    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

  10. #130
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    I will post later.

    You need to learn Queries next.

  11. #131
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    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.

  12. #132
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    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.

  13. #133
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Quote Originally Posted by Rainlover View Post
    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

  14. #134
    JFo is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    393
    Quote Originally Posted by Rainlover View Post
    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.
    On it, not to worry too much I have already had to do this a few times

    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

  15. #135
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Coding question
    By kzoli62 in forum Access
    Replies: 1
    Last Post: 07-04-2011, 05:09 PM
  2. Combo Box Coding
    By Hale in forum Forms
    Replies: 2
    Last Post: 05-04-2010, 10:56 AM
  3. Coding for Empty Combo Box
    By gazzieh in forum Programming
    Replies: 1
    Last Post: 03-26-2010, 05:22 PM
  4. Button Coding
    By yamie in forum Programming
    Replies: 3
    Last Post: 02-22-2010, 10:45 AM
  5. Bar-Coding
    By texasprincess7 in forum Access
    Replies: 1
    Last Post: 02-12-2009, 10:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums