Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Nov 2005
    Location
    London, Gtr London, United Kingdom
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autofill Access (2000)

    Now I know that there been several posts regarding this, and I promise, i've looked at them, and other stuff on the net and MShelp, and I still can't work it out!!! I'm being very stupid. (totally new to access)

    I've attached a really basic db, that on the form Entry Form, after a user has selected a name from the pull down list, the Phone and Cost Code fill in!! simple I know, but i'm close to tears with frustration and confusion.

    some basic advice would be enourmously appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autofill Access (2000)

    Unless you want to be able to modify the phone number and cost code in tblMainData, you shouldn't have those fields in tblMainData, because it is already available in tblLookup.
    The Name field in tblMainData should be a number field (Long Integer) since it is linked to the ID field in tblLookup.
    You should create a relationship between the tables and enforce referential integrity for the relationship, so that the user cannot enter non-existing persons in tblMainData.

    Create a query based on tblMainData and tblLookup joined on ID vs Name. Add all fields (or *) from tblMainData plus the phone number and cost code fields from tblLookup. Use this query as record source for the form.

    See attached version.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Autofill Access (2000)

    I attach a modified version that works OK.

    Your question actually touches on a number of issues.

    If chooisng a person completely determines the Phone and the Cost Code, there is no need to store them in both the Lookup table and the Main table. Instead all that is stored in the Main table is the ID number of the person, then you look up the Name, Phone and Cost Code whenever you need them.

    So the main table just has a personID field which is a number.

    The form now draws its data from both tables using a query. Once you choose person using the combo box,the name is displayed and the Phone and Cost centre are got from the other table and displayed, but all that is stored is the hidden PersonID value.
    Regards
    John



  4. #4
    Star Lounger
    Join Date
    Nov 2005
    Location
    London, Gtr London, United Kingdom
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill Access (2000)

    OK, this is beginning to made sense now.. Thank you guys.

    What I am thinking though, and I've noticed with these, is that if I were to change a name in tblLookup, then it is reflected throughout the rest of the database, which thinking about it, isn't what i want. i.e., If someone changes cost code, the cost code will change in an old record. That record needs to keep it's original cost code.

    So does this mean that I do need the names, cost code, phone stored in the Main table as well so once it's there it's there? does that make sense. So it's half and half! though I would want the Lookup protected so only an administrator can change and update the list.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autofill Access (2000)

    In that case, you DO need the cost code field in tblMainData, and you can use code in the After Update event of the Name combo box to populate it from the Cost Code field in tblLookup. See the attached modified version.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Autofill Access (2000)

    I agree with Hans that it is probably only cost centre that should be stored in the main table.

    If a person changed their name or phone number why would you want to asscoiate their old phone numer and name with the record in the Main table.
    Regards
    John



  7. #7
    Star Lounger
    Join Date
    Nov 2005
    Location
    London, Gtr London, United Kingdom
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill Access (2000)

    I've just been taking the file apart and I think i'm beginning to finally understand what's going on, though it's still all very confusing. I think, with a fresh start tomorrow it should all fall into place.

    Thanks again guys.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •