Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Hit a wall - Can’t get multi table form to work

  1. #1
    Wfoley82 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    7

    Hit a wall - Can’t get multi table form to work

    I am making a database to digitize our HR, Personnel, and Training files. I have made all of my tables and all of my data input forms.



    Then I tried to make an Employee Record form that has multiple tabs. Each tab has the “sub form” of a form for that particular data. For example the first tab is Status and uses the employee status form as the sub form on the tab. This should show name hire date termination date etc. another tab is training completions and so on and so forth.

    The best I got out of this is the first tab showing the information of the last person entered (2 entered for testing) but the other tabs show blank information or oddly the same thing you see on the first tab.

    I just want a simple way for the end user (supervisors) to enter an employee’s name and be able to see all the information on that one person (all data associated with the name in all the tables). This way they can check training information, attendance issues, discipline, etc.

    Please help I have been banging my head on this for days and it’s the last piece I need before loading the information and rolling it out.

  2. #2
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    351
    What connects them all together? Is there a combo box on the main Form? What is the record source for those subforms? How do they know what record is on the first tab?
    Gina Whipp
    Microsoft MVP (Access 2010-2015)
    https://www.access-diva.com/tips.html

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,231
    How are tables related - what are primary and foreign keys? Is main form bound to employees table? Are subform Master/Child Links properties set?

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Wfoley82 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    7
    No combo box. I made a blank form, put in tabs, on the first tab put “sub form” and chose it to be form-employee status, then just worked my way along making other tabs.

    All tables have the same primary key “Name” which is Last, First. They also all have last name and first name separate.

  5. #5
    Wfoley82 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    7
    G4S PRH Personnel - Copy.zip
    Quote Originally Posted by June7 View Post
    How are tables related - what are primary and foreign keys? Is main form bound to employees table? Are subform Master/Child Links properties set?

    If you want to provide db for analysis, follow instructions at bottom of my post.

    Any help is appreciated.

  6. #6
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,099
    All tables have the same primary key “Name” which is Last, First. They also all have last name and first name separate.
    Doesn't sound like an appropriate Primary Key. How do you handle a situation where you have more than one person with the same name?
    B.T.W.
    "Name" is a 'reserved' word and should not be used as the name of any object.
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  7. #7
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,050
    See the example attachment. I have the employee form with 2 working tabs. It should be looked at as a guide on how to accomplish your goal.
    The Name, FirstName, LastName should not be repeated in all those tables.
    I combined the employee demographics table and employee status tables.
    Good practice dictates that object names should have no spaces, special characters except for underline. I eliminated some.
    Primary keys were all wrong. I setup logical primary keys and foreign keys for the example.
    You have lookup fields in tables. These should be eliminated.
    You have a lookup table with only 2 records, Yes and No. This should probably be a listvalue choice combobox instead. There are other lookup tables that might be eliminated in this fashion also.
    Personally I would not use the switchboard wizard. I would set up a custom MainForm with buttons.
    G4S PRH Personnel -davegri-v01.zip

  8. #8
    Wfoley82 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    7
    Thank you. It appears what I thought I had figured out of Access was not much in reality. I had used a one that I found (a predecessor started and never finished) to tinker and learn. So I am going to ask a few questions off of above.

    Some of the tables were made so that end users (line supervisors) could use forms to input specific things through forms and not the tables. But if I don't use "Name" or "Last Name" & "First Name" in each table how do i connect the information to the employee all together? I thought I had to have something (key) that appeared in the tables to link information together.

    So is the Key supposed to be something like an employee# ? I'm not sure I was really able to see what you were using as the Primary Key and which was the foreign key.

    I did tables (ie counseling level, shifts, etc) so that on other tables the "lookup" would limit the end user on what they could put in as a list to choose. Is the a better way to accomplish this in the tables to eliminate the lookup fields?

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,231
    This is what autonumber type field is supposed to be used for - a unique record identifier. Autonumber would be primary key in 'master' table and saved as foreign key in 'dependent' table.

    Dave's sample db uses an autonumber field for all primary keys. For instance, Employee_PK in table EmployeeDemographics then this value is saved as foreign key in dependent tables. Queries include related tables by linking on PK and FK fields.

    Open Relationships builder and this structure should be apparent.

    Advise not to build lookup fields in tables. Build comboboxes and listboxes on forms.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,050
    I didn't fix all the tables, just the ones necessary to show the employee form and its subforms on the tab control.
    All the primary keys are clearly suffixed with _PK, and all the foreign keys suffixed with _FK.
    I left the keys visible in the forms for your sake, to help understand what was happening. Normally, when a project is distributed to users, those fields are made invisible.

    But if I don't use "Name" or "Last Name" & "First Name" in each table how do i connect the information to the employee all together?
    You cannot trust the supervisors or anyone else to spell the names exactly right across all those forms. Having the names ONCE in the employeedemographics with everything else pointing there via the foreign key guarantees that it will be the same every time. That's basic RDB. For each of the z tables there needs to be a foreign key in the employeedemographics table. Data entry for the z tables would need a mainform/subform setup with the employeedemographics table as main and a z table as subform. Use a combobox on the main form to lookup the employee record.

    I did tables (ie counseling level, shifts, etc) so that on other tables the "lookup" would limit the end user on what they could put in as a list to choose. Is the a better way to accomplish this in the tables to eliminate the lookup fields?
    There's a vast difference between lookup FIELDS and lookup TABLES. Too much to explain here. Google it.

    There's a lot to cover here and some research on your part is vital to understanding the workings of autonumber primary keys, and having your DB work in a coherent fashion.
    Keep at it. You have a big project and designing and coding it correctly will pay big dividends in future performance and maintenance.

  11. #11
    Wfoley82 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    7
    Replying to: June7 and Davegri

    Ok so when I opened the relationships I then grasped what your were saying about the keys. I think where I ran into the biggest problem was on the demo one I couldn 't get forms to work well for me even when I used youtube videos and tips and all. So then when I started building one from scratch I just made tables for everything I needed forms to do. In the demo one I was using as a test bed I couldn't get the combo box with the options I wanted to store the data in the table. So I saw in the demo that there was a table with some options that were referenced elsewhere that's where I got the idea for all of those.

    So seeing how you did the relationships gives me a clear way to do the table. I'll just have to keep trying on the forms. Plus figure out how to allow a supervisor to open this, search for an employee, and see all the data accrued on that person (ie disciplines, sick calls, tardy's, training, etc)

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,231
    You might want form/subform arrangments.

    As for searching/filtering, use intrinsic tools or review http://allenbrowne.com/ser-62.html

    The more 'user-friendly', the more code.

    The db uses old-style Switchboard, which I never liked, but it works.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Wfoley82 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    7
    Quote Originally Posted by June7 View Post
    You might want form/subform arrangments.

    As for searching/filtering, use intrinsic tools or review http://allenbrowne.com/ser-62.html

    The more 'user-friendly', the more code.

    The db uses old-style Switchboard, which I never liked, but it works.

    Ok I have been able to accomplish everything I was intending to do except two items (looking at the relationship and PK 7 FK helped greatly):

    So the database gets built by adding employees. Is there a way to put an easy search for on the main form that appears?
    Also I was using a youtube video to try and have on the sub form the list view. For example on the main form you have the sub form Call Out where you can add a call out for that employee. But what I was trying to accomplish is below the fields a list showing all the entries for call outs matching that person.

    That is all that is standing between me and rolling out the database.

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,231
    I provided a link to a tutorial for a 'search' form tool. Did you review?

    The list you describe sounds like a cascading combobox/listbox. A very common topic.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,445
    Maybe you would post your current dB so we can see where you are at now?


    In both your original dB and davegri's modified version I saw a lot of object names that should be fixed......


    Some suggestions on naming objects:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not begin an object name with a number.
    Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 10-15-2014, 02:24 PM
  2. Replies: 2
    Last Post: 07-15-2014, 10:39 AM
  3. Replies: 2
    Last Post: 05-22-2012, 03:29 PM
  4. Hit Statement & Syntax Wall
    By Soule in forum Programming
    Replies: 5
    Last Post: 01-17-2012, 03:48 PM
  5. Help banging my head against a wall working on this
    By Brian Foshee in forum Programming
    Replies: 1
    Last Post: 06-04-2010, 08:30 AM

Tags for this Thread

Posting Permissions

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