Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2007
    Location
    Mandeville, LA
    Posts
    3

    Unhappy Unanswered: 2 tables, 1 common field auto-filled

    Our supervisors need to be able to evaluate their employees offline (disconnected from the company domain) via a stand-alone Access file. This Access questionaire (form) feeds a local Evaluations table 'Yes' or 'No' responses from approximately 30 questions. When it's time to upload these evaluations to the company SQL server a web page reads the department field and filters any reports based on this field. Another web page has already verified Active Directory department info and compared it to what's in the Evaluations table. These reports need to be filtered from other departments for the usual political reasons.

    This department field used to be a pull-down the supervisors would select from on the Access form, but if they rush during their evaluations they sometimes pick the wrong department. To circumvent this a web page hard-codes their DeptCode and DeptName from Active Directory in a single-row Division table within a brand-new but empty MDB file downloaded to their PC for future evaluations. The department pull-down is now fed by a query that joins the 2 fields from the Division table, but the supervisors have to click this pull-down to have it populate the department field in the Evaluations table. Schema wise, the Division table is one row of 2 fields, the Evaluations table is many rows of ~40 fields. I don't have time to rewrite a new MDB file everytime a new department comes online. So, I have to have a single DB/MDB file that works for ALL departments, both existing and new.

    My question is, how can I have their department auto-filled by that query WITHOUT the supervisor having to select the pull-down? I have tried several different text box configurations but none worked. Ideally, I want their department to appear in a readonly-type text field somewhere on the form. The department field should be automatically filled-in on each person evaluated.

    TIA . . .

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Not sure if this is what you're looking for but there is vba code in the MSAccess code bank which will retrieve information (ie. Department) from the Active Directory.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Sep 2007
    Location
    Mandeville, LA
    Posts
    3

    Red face

    Thanks for replying, but perhaps my initial explanation of this project was a bit long-winded (sorry).

    What I need is not AD programming assistance but how to tweak / program my Access form (hence, why this msg was posted to this group) so that I pick up the single-row dept info from table1 (Division) and write it to every row inserted into table2 (Evaluations). I thought that explaining the overall project would help eliminate any confusion but it seems to have attributed to / hindered it (again, sorry).

    Was this explanation a little clearer in what I'm trying to accomplish? If you read my initial post the supervisors have to click the pulldown to get the dept info selected. How can I get the dept info automatically selected with each evaluation? I have a textbox that captures today's date in it and inserts that into table2 (in a date field), but I can not for the life of me figure out how to get a similar textbox to capture the dept info from table1 (Division) and insert that to table2 (Evaluations) with each submitted evaluation.

    Again, TIA . . .

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    No....you were clear. I just thought you might find some info you could use in the Active Directory example as you mentioned it. You may be able to utilize the dlookup command to lookup a value in Table1 to insert/set as the value/default value for the field for Table2. You may want to read up on the dlookup command. It's were you can look up a value in a table given certain criteria.
    Last edited by pkstormy; 09-23-07 at 01:50.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Sep 2007
    Location
    Mandeville, LA
    Posts
    3

    Unhappy

    OK, that may be getting somewhere but what do I enter and where? I tried dlookup("[DeptCode]","Division") in a Control Source text box with no luck.

    Another unsuccessful effort was a ListBox:
    Code:
     Control Source = CodeAndName {1}
    Row Source Type = Table/Query
         Row Source = GetDefaultDept {2}

    1) field in Evaluations table
    2) GetDefaultDept (query) = SELECT right(Division.DeptCode,3)+" "+Division.DeptName AS FullDivisionName FROM Division;

    If possible I'd like to use the query as the data source since it combines the right 3 numbers of the dept code along with the full dept name into what I want auto-entered into the Evaluations table. Please be specific about what code goes where - thanks!
    Last edited by HoftHome; 09-23-07 at 17:47.

Posting Permissions

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