Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2007
    Posts
    38

    Unanswered: Automatically completing fields in a form

    I know there are already various threads on hee relating to this, but I havent managed to get the examples to work.

    I have a database with a number of tables in it, Table 1 holds user details and table 2 holds manager details (4 fields Name, CC, GR, CL (for short)). I am trying to work out how, when i enter the managers name I can get the other 3 fields to automatically populate with the relevant details so this can then be linked to the user record ( I know control source is the user table and row source is the manager table)

    I have tried Dlookup but this doesnt seem to auto populate the fields. I have by fluke managed to get name to auto change CC but this didnt save to users.

    I am notinto programming within access so any help would by nice if it could be in idiot language please.

    MTIA

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you explain (posting any relevant code) how you have flukily managed to get the CC to change based on the name?
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2007
    Posts
    38
    um, no, srry, i cant, I got so annoyed with it last night not doing what i wanted i deleted the fields!

    apologis

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a possible solution to your problem is to use a listbox or combo box containing details of the managers, pulled form a table using whatever criteria you need.
    when the user selects a manager you can then either
    run a query to retrieve the detail you want by using the selected manager as the key. DLOOKUP is OK if you need to extrract a verylimited amoun of data.. generally I'd suggest you'd be better of using a SQL select and runn ign a query which returns the required values.

    or

    if you don't have to many managers stuff the listbox with the additional data as hidden columns.. you are not tied to only having one or two columns in a listbox.. sometimes it can make sense to stuff that listbox with the data required and then extract it on demand. I wouldn't recommend this approach if you have a large amount of data to stuff in the list box, or if the data is volatile. however for relatively static stuff on high latency networks it can be a godsend

    mind you it does beg the question in my mind if the data is coming from a table based on manager... should a FK to the manager provide the child tabel with the required data, and not have to repeat that data.... not knowing the deatils of your user requirements, but it does look as if the data model may need revisiting

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by wartroll
    um, no, srry, i cant, I got so annoyed with it last night not doing what i wanted i deleted the fields!

    apologis

    no need to apologise.. we've all been there........

  6. #6
    Join Date
    Nov 2007
    Posts
    38
    Many thanks healdem.

    table 1 holds 2000+ user records and table 2 holds managers - table 2 has 150 ish managers with 106 CL entries 8 gr entries amd 8 GR entries - each manager is linked to a CL a Gr and a CC. All of this data needs to be on the user table, but i can have 30 staff with the same manager, so am trying to make life easy by getting the system to auto complete for me (and ensuring spelling is all correct and the same etc)

    I started off using a combobox for the manager and one for each of the other fields, but then thought what if I (or someone else) enters the wrong entry on a user record, so started looking at auto completing these fields. In excel i would have used a vlookup formula, hence tring dlookup in Access. I am just trying to get to be able to put in manager A and the other 3 fields list the CC CL and GR for him, not too great a list, so i wouldnt have thought it would have been so hard!

    Many thanks

    Quote Originally Posted by healdem
    a possible solution to your problem is to use a listbox or combo box containing details of the managers, pulled form a table using whatever criteria you need.
    when the user selects a manager you can then either
    run a query to retrieve the detail you want by using the selected manager as the key. DLOOKUP is OK if you need to extrract a verylimited amoun of data.. generally I'd suggest you'd be better of using a SQL select and runn ign a query which returns the required values.

    or

    if you don't have to many managers stuff the listbox with the additional data as hidden columns.. you are not tied to only having one or two columns in a listbox.. sometimes it can make sense to stuff that listbox with the data required and then extract it on demand. I wouldn't recommend this approach if you have a large amount of data to stuff in the list box, or if the data is volatile. however for relatively static stuff on high latency networks it can be a godsend

    mind you it does beg the question in my mind if the data is coming from a table based on manager... should a FK to the manager provide the child tabel with the required data, and not have to repeat that data.... not knowing the deatils of your user requirements, but it does look as if the data model may need revisiting

Posting Permissions

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