Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    348

    2 subforms on mainform

    Experts:



    I need some assistance with modifying a current process which allows to only add new and/or unassigned records into a table.

    Please find attached two (2) databases + a Word document containing more detailed information about the existing process, potential errors, and what I'd like to achieve.

    If you have any specific recommendations for the **second** subform (again, details are in the Word documents as well as call out's in database form), I'd appreciate any suggestions.

    Thank you in advance,
    EEH
    Attached Files Attached Files

  2. #2
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,165
    IMHO it would be best to use the forms BeforeUpdate event to validate the data. If the proposed data is invalid then a message can be displayed to the user and the update can be cancelled.
    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)



  3. #3
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    348
    Bob:

    Thank you for the suggestion... what you suggested may be a good way forward. Not sure exactly as to how it would be implemented. Any particular ideas? Would love to see an example in the existing database.

    Again, thank you!
    EEH

  4. #4
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,165
    Quote Originally Posted by skydivetom View Post
    Bob:

    Thank you for the suggestion... what you suggested may be a good way forward. Not sure exactly as to how it would be implemented. Any particular ideas? Would love to see an example in the existing database.

    Again, thank you!
    EEH
    OK Hope the following helps.

    Not sure what the criteria should be for restricting new record but in the attached db you can not add a name that has already been used in the subform which is related to the main form record.
    For example: "Fonda" can not be selected again in the subform for the first record of the Main form.
    See the Subforms altered BeforeUpdate code and the new query "qryBobsExample".
    Post back if you have questions
    Attached Files Attached Files
    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)



  5. #5
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    348
    Bob:

    Thank you for providing the modified sample database. I think this is going in the right direction, but I'm not quite there yet.

    Allow me to recap the business rules which are very fundamental and follow the "common-sense rule":
    - The organization has multiple distinct offices.
    - The organization has multiple distinct billets (jobs).
    - Naturally, an employee can only exist once.
    - Whether an existing employee or a new employee, the employee can be linked to one and only one billet (regardless of the office location). So, basically, once Henry Fonda has been assigned to e.g., the COO position, he cannot be assigned to any other job period. So, it doesn't matter that he's currently working in the HQ, Fonda couldn't be assigned to any other function in any other office unless he'd be, e.g., reassigned/promoted from COO to CEO. **

    ** Note: The only exception to the above rule is for "Employee = Vacant". Ultimately, multiple positions could be unfilled, so I'm using the "dummy employee" vacant to create the record in the OBS.

    - All above (Henry Fonda) example also applies to billets/jobs. Every billet is unique (via job ID), so I should NOT have the same job in multiple office location. Naturally, I may have multiple marketing managers, but each of them would have a different job ID (thus they'd be distinct).

    To recap, I shouldn't be able to add the same job multiple times regardless of the office location nor should I be able to assign the same employee multiple times (to different jobs). Again, only exception is employee = Vacant.

    The above would cover the "first challenge"... ensuring a user is not accidently making data entry errors. The "second challenge" would be to address how to only view (maybe in second subform) only the new billets and new employees.

    If I had a solution to the first challenge first though, I'd be already very excited!

    Cheers,
    Tom

  6. #6
    Join Date
    Apr 2017
    Posts
    1,043
    You are trying to attach employees to offices and billets. Much easier way is to attach offices and billets to employees.

    You have the table of employees:
    tblEmployees: EmployeeID, FoereName, LastName, [EmployedAt], ...;

    You have a table of billets which can be attached to employees. There is a billet for "Vacant Billet" too.
    tblBillets: BilletID, BilletName, ...;

    You have a table of offices which can be attached to employees. There is a office for "Vacant Office" too.
    tblOffices:OfficeID, OfficeName, ...;

    To attach employees to billets and to offices, you need 2 linking tables:
    tblEmplBillet: EmplBilletID, EmployeeID, BilletID, ValidFrom;
    tblEmplOffice: EmplOfficeID, EmployeeID, OfficeID, ValidFrom;

    You create forms to register/manage Billets/Offices.

    You create a single form to register/manage employees. This form has 2 continous subforms based on tblEmplBillets and tblEmplOffices respectively. Both subforms are linked to employees form through EmployeeID. So whenever you select an employee, the full history of billets and offices this employee was attached to is displayed in subforms, Every new entry into either of subforms will be attached to employee active in main form.

    As optional feature, you can add a field into tblEmployees named e.g. CurrStatus, which has values 0 or 1. When CurrStatus = 1, the employee is active, when CurrStatus is 0, the employee is inactive/historical/not employed/... This field is recalculated whenever any record in tblEmplBillets or tblEmplOffices is changed/deleted/added. Whenever current (i.e. valid today) value for BilletID or OfficeID in blEmplBillets or tblEmplOffices is set to be "Vacant Billet" or "Vacant Office", the record for vacant satus is added into another linking table too, and CurrStatus for employee is set to 0.

    You can set the filter for employees form to only employees with CurrStatus = 1 to be displayed. On employees form, you may have a control which allows the user to change this filter, so user can select only currently employed employees, or only currently unemployed employees.

    You always can create reports to list e.g. all currently employed employees by offices, or by billets, e.t.c.

  7. #7
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,165
    Tom

    Take a look at the attached db.

    This has code in the BeforeUpdate event of the combo box used to assign an employee.
    This should give you some idea how you might invoke your other criteria.
    Please post back if you have questions.


    BTW
    The macro that is used by the combo box in the Header section, to find records doesn't work for me. Can't help with macros because I don't usually use them. I prefer to use code. Easy to convert it to code if you want to using the conversion wizard. It works once it's been changed to code.

    In your table "T01_StaffMembers":
    You have 2 or 3 fields ("All_Service", "All_Type" and possibly "All_RankTitle") which I would suggest using a lookup table for when you create a form for data entry. This would help to prevent typos users.
    Attached Files Attached Files
    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)



  8. #8
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    348
    Bob:

    Thank you for the modified version. Please allow me to follow up with another clarification/question.

    First, for testing purposes, I deleted all records from the OBS table. Then, when opening the form, I start "from scratch" with the assignment.

    Next, within Office_01 (Headquarters), I selected "Financial Manager" and linked Clint Eastwood to the billet.
    Then, I selected "Accountant" and purposefully selected "Clint Eastwood" again... this resulted in the error message preventing me to add him a 2nd time. Great!
    Next, I switched offices and picked "Office_02" (West Coast). I choose "Accountant" again as well as Clint Eastwood. Again, it prevented me from adding Clint a 2nd time. Awesome!

    The only thing that's missing though is preventing adding the same billet multiple Times. In the actual system, I have many dozens of Accountants. However, I'd then have also dozens of Accounting positions (e.g., 5 - Accountant; 22 - Accountant; 47 - Accoutant, etc.). Right now, however, I can add "5 - Accoutant" multiple times and, thus, assign different employee to the same job.

    My question: How would I have to modify the VBA that I also cannot select the same position (in any offices) once that the billet ID has been added to the OBS table?

    Thanks,
    Tom

    P.S. Yes, in the actual database, I have lookup table for Services, Type, and Rank. I just tried to keep this example db at the most basic level w/o adding too many objects.

  9. #9
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    348
    ArviLaanemets - thanks for chiming in. I appreciate the feedback; however, I'd like to stick w/ the current form (i.e., assigning billets/employees to orgs). If you have an example of your recommendations, I may reconsider. Thank you!

  10. #10
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,165
    Tom

    Try this code in the BeforeUpdate event of the combo box control called "cbo_billet".

    Code:
        If DCount("BilletIDfk", "T00_JunctionTable_OBS", "BilletIDfk=" & Me.cbo_billet) > 0 Then        MsgBox "Please select a different Billet.", vbCritical, "Billet already used"
            Cancel = True
            Me.Undo
        End If
    Is the combo box in the Header Section working for you?
    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)



  11. #11
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    348
    Bob:

    Amazing!!! This validation process works GREAT!! Thank you for your patience and offering a solution which will mitigate data entry errors by the user.

    This most definitely addresses "Challenge #1". At this time, if all possible, I would welcome any recommendations WRT "Challenge #2".

    Additional information about "Challenge #2":
    1. In the reposted database, I deleted all records from the OBS table (for testing purposes only)
    2. I deleted all but five billets: CEO, CFO, COO, Financial Manager, and Accountant
    3. I also deleted all but five employees: Day, Fonda, Freeman, Redford, and Washington. I also kept dummy employee = [Vacant]
    4. Then, I only linked 3 employees to 3 billets. That is, I assigned Fonda as CEO, Day as CFO, Freeman as COO.
    5. Step # 4 created 3 records in the OBS table.

    Now, given that I only assigned 3 (out of 5) employees to 3 (out of 5) billets, the following must be recognized:

    - If I were to open the form at this time, the only available billets (w/o throwing an error message) are: Financial Manager and Accountant
    - Likewise, the only two employees left -- [Vacant] doesn't count -- which would NOT throw an error are: Redford and Washington
    - Obviously, given the extremely number of small records, this is very easy to identify/track. However, in the actual system, I have thousands of billets and employees.
    - Thus, given the actual database, it is not easy to readiliy identify those billets and employees which have NOT been linked in the OBS table (and are still available for "assignments").

    So, if all possible, I'd like to achieve the following:
    - The current subform, allows me link employees to jobs. What if this subform's properties, however, would be modified so that I can only view the current assignments but cannot make changes whatsoever.
    - And then, would it be feasible to include the same subform a 2nd time? In this case, the properties would me to add employees to billets (just like in the current version).
    - However, the 2nd subform would only show 2 billets and 2 employees:
    - So, when clicking on the cboBillets, I'd only see Financial Manager and Accountant; likewise, when clicking on cboLastName, I'd only see Redford and Washington
    - Then, let's say once I assigned Redford to the Financial Manager position, two actions would happen: First, Financial Manager and Redford would disappear from the combos in the 2nd subform AND their record combination is
    is now (automatically) shown the read-only subform (section) #1.

    Is such process doable? Not sure if two (2) subforms can/should be used, but this concept illustrates the conceptual process.

    Thousand thanks in advance!!

    Tom
    Attached Files Attached Files

  12. #12
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,165
    Tom
    Off to bed shortly and then working for two days but will be happy to look at this again on Wednesday
    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)



  13. #13
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    348
    Bob -- copy that... have fun @ work. Looking fwd to any feedback you could provide in the coming days.

    Cheers,
    Tom

  14. #14
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    348
    Bob:

    Thanks to your excellent help, I think I've come up w/ the perfect solution. Please find attached the updated database. It includes your form (w/ the VBA as you recommended) and now two listboxes with show a) unassigned/vacant billets and b) unassigned staff members. I will discuss this w/ the team tomorrow. I feel very comfortable they're gonna love the new process (i.e., validation/auto-removal of duplicate entries).

    Thousand thanks again!!!
    Tom
    Attached Files Attached Files

  15. #15
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,165
    Tom

    Just before you settle on your solution take a look a the attached db. It has a different way of handling the selection of staff from the combo box which I think you may prefer.
    Attached Files Attached Files
    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)



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

Similar Threads

  1. Search subform from mainform
    By ittechguy in forum Programming
    Replies: 70
    Last Post: 10-04-2015, 06:08 AM
  2. Link between subform and mainform
    By crowegreg in forum Forms
    Replies: 2
    Last Post: 08-24-2011, 07:05 AM
  3. Replies: 3
    Last Post: 06-20-2011, 09:30 AM
  4. MainForm/SubForm question
    By 10 Gauge in forum Forms
    Replies: 5
    Last Post: 03-04-2011, 07:46 AM
  5. Replies: 0
    Last Post: 03-14-2009, 12:33 AM

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