Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2007
    Posts
    43

    Unanswered: Show data from multiple tables on ONE form using ComboBox and ListView

    Hello i have created the database concept and model for Employees with passcards. Attached is the schematic of my database.


    Now, i want to create one main FORM in the Access2007, in that form i want to view information from every table, and being able to edit/delete/update all tables using only this main form.

    For this, i am adding one blank new form, and begin editing it. i use "Add existing fields" button on the top menu, and add text items.

    BUT! i have 7 related tables, so...for example i want to add ComBoBox displaying TitleName from the t_titles table, for current employee, for this:

    1. i have added comboBox control on the form.
    2.used the wizard to select both TitleID and TitleName from t_titles table.
    3.choosed to hide primary key.


    after all of these, when i press to expand my ComboBox on the form i can see list of all titles from t_titles table. but when i select any title, and press SAVE button, there are no changes in the e_employees table... and when i scroll the records using next and previous buttons i dont see that TitleName changes for every record...

    please could you say me how to make it work?

    The next thing which i want to do is to view in the ListBox all the Areas where my employee may enter.

    for this, i am placing ListBox Control on the form, and using Wizard add values of AreaID and AreaName to it, choose hide primary key.. well, after all that i can see all the area names from a_areas table... but not the particular areas for current Employee...

    i think i begin correctly with placing controls and assigning values....but where is my error?

    or should i create two separate Queries for Titles and for Access areas? if yes, then how to link these queries with my FOrms Controls wizard? (how to assign to select from CURRENT record?)

    thanks!
    Attached Thumbnails Attached Thumbnails rel3.gif  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    traditionally you would have different screens for different purposes, so you would have a masterfiles section which allowed users to make changes to divisions & departments, titles and the like.

    when you desing your employee form you then use a suitable control (usually a list box or combo box for those elements that refer to another table ad foreign keys)

    for items which refer to child records ie where they may be multiple sub items for this record you woudl design those elements as sub forms

    so in this case i would expect discrete forms for
    d_divisions
    d_departments
    t_titles
    a_areas
    I would expect a single form for e_employees
    im not certain if c_cards merits its own table, unless you are allowing one user to have more than one card, if they ghave only one card then arguably the information should be stored at the employee level, unless you want to record what cards an employee has had over time.. if so you may want to record that fact that card X was valid between dates.

    Arguably the permission for a a card is an attribute of the employee.. but agian thats down the business logic you are trying to model.


    best thing to do is to have a play with access design a few forms and see what happens.

    stylisticallyI alwaays find it easier to design the parent forms first and get some (meaningfull) data in
    so thats forms for d_divisions. d_departments, t_titles & a_areas
    having done that then design the form for e_employees
    having done that then c_cards.. then the 'fun' starts how do you design the form for r_rights...

    have a stab at it, and if you hit problems then come back. generally speaking you will get a better response here if you make an effort.. you'll learn more, and people will probably try to help, if you make no effort you may get no response or worse a working response that may not be very good or deliberately obscure.

  3. #3
    Join Date
    Nov 2007
    Posts
    43
    yes too complicated, now i was experimenting with Wizards! it could give some results, but later i had difficulties with adding new records on the form... at least is it possible to view only the areas of access for current record on the ListBox??? if its possible it would be enough for me!!!

  4. #4
    Join Date
    Nov 2007
    Posts
    43
    and guys! i want to ask you one more thing! please take a look at my Database Schema in my First Post attachment!

    as you can see i have separate table c_cards for card data... but i was thinking...what if i will hold card data like CardName,IssueDate,ExpireDate in the e_employees table?

    since...every Employee in my Database WILL have only ONE card... so...according to this, is it nice to remove c_cards, and hold that data in e_employees?

    thanks

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by TeslaShock
    yes too complicated, now i was experimenting with Wizards! it could give some results, but later i had difficulties with adding new records on the form... at least is it possible to view only the areas of access for current record on the ListBox??? if its possible it would be enough for me!!!
    well pardon me... if that was to complicated for you then I doubt I will be able to simplify it enough to meet your requirements.

  6. #6
    Join Date
    Nov 2007
    Posts
    43
    no, i just wanted to create it all on one form and being able to view and edit from this form... for example, user will not change name of department from this main form...he will just change under which department is current employee... and he will not change name of areas... (i can do it manually inside database, since it happens rarely) so i need only ONE form, which will let u change: employee data, card data, and assign new area rights for it.. it must be easy...i just dont know some trick...thats why i asked you. anyway thanks

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ...no you probably need something to set all those masterfile values to start with.

    then you have a data entry screen(s) which allow users to pull together all the data.

    for you employee form you could have the department and other stuff as list boxes

    Access works on a one form per query basis.
    its far easier to stick to that methodology in Access. you can add sub forms to an existing access form to handle child data

  8. #8
    Join Date
    Nov 2007
    Posts
    43
    okey, thank you so much. and next thing which i would like to ask you... When i edit the e_employees table directly, in the TitleID there is a DropDown list.. i press it, and choose any speciality for this person. (i have achieved this by choosing DropDown list of TitleID in the e_employees constructor mode)

    Now, i want to do the following for the Areas... for example, i expand my employee, his card... and now i want to assign area rights for him just from dropDown list. I tried the method which worked for TitleID....but here it doesnt work...it says something like type mismatch...

    have any idea how to do it?

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes lots.. but I doubt it will be easy to explain here, or anywhere
    allcating multiple areas to a single card where a single card is owned by an employee is potentailly complex.. its not unattainable. but I think it may be out of your league right now

    if you have the car detaisl as part of the employee record and not in a separate table then its achievable by having a sub form for the link table
    in that sub form you select the area(s) applicable to that card, and when you integrate that subform into the parent form you tell access that the link criteria is the employee ID (or cardID, whatever you have designed



    in all honesty the best thing in my view is to design a form for each table
    then mess around inserting sub forms into your main data capture form for child tables (ie where the employee is the parent element), and use list boxes or combo boxes where the employee is the child element). it snot a difficult thing top do, it is difficutl (at least for me) to explain.

  10. #10
    Join Date
    Nov 2007
    Posts
    43
    thanks you helped me alot with good advices, i do understand your concept.

    Now i would like you again to look at my data relation schema in my First post on this forum, and same me your opinion about organization of my database? do you think it is very well organized?

    your last opinion about my database is most important for me, i am waiting for your replies

    thanks in advance

  11. #11
    Join Date
    Nov 2007
    Posts
    43
    so what ppl do you think about my database schema? please take a look at my schema in my first post in this thread, and say what you think? is it good?

  12. #12
    Join Date
    Nov 2007
    Posts
    43
    well, i did solve my problem with ListBox appearing in database design tables (to be able to select area right immediately) i just used Field Indexing in the table builder of the r_rights.

    my next problem is this:
    For example, i want to list ALL the Employees from my database, who have access to the AreaID - 2, here is my SQL code for this query:

    Code:
    SELECT e_employees.FirstName, e_employees.LastName, e_employees.FathersName, d_divisions.DivisionName, t_titles.TitleName, c_cards.ExpireDate, a_areas.AreaName
    FROM t_titles INNER JOIN (d_divisions INNER JOIN ((d_departments INNER JOIN e_employees ON d_departments.DepartmentID = e_employees.DepartmentID) INNER JOIN (c_cards INNER JOIN (a_areas INNER JOIN r_rights ON a_areas.AreaID = r_rights.AreaID) ON c_cards.CardID = r_rights.CardID) ON e_employees.EmployeeID = c_cards.EmployeeID) ON d_divisions.DivisionID = d_departments.DivisionID) ON t_titles.TitleID = e_employees.TitleID
    WHERE r_rights.AreaID = 2
    ORDER BY e_employees.FirstName DESC , e_employees.LastName DESC , e_employees.FathersName DESC;
    problem is, what if i want to list ALL the Employees who have access to area: 2,3,4 and 7?

    i tried to edit my SQL like that:
    WHERE r_rights.AreaID = 2 AND 3 AND 4 AND 7

    but it didnt work... could you help me with this please?

    and please guys, look at my first post in this thread and say me what you think about my database? how well is it organized?

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    WHERE r_rights.AreaID = 2 AND r_rights.AreaID =3 AND r_rights.AreaID =4 AND r_rights.AreaID =7

  14. #14
    Join Date
    Nov 2007
    Posts
    43
    healdem, thanks, but it did not work either...

Posting Permissions

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