Results 1 to 6 of 6
  1. #1
    skidawgs is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    26

    Question Bound Forms - Input Validation Between Main & Subform

    Hi All,



    I currently have a bound form which is used for Inserting/ updating data for a Position and the Employee(s) staffing that position. My users would like me to incorporate some fool-proof input validation between the mainform (Position) and its subform (Employee). For example, if the status of the Position = 'Staffed', Then there must be an employee currently in the subform and vice versa. There are about a dozen or so other Input Validation rules they would like me to implement, however I am running into a couple problems.

    Since Bound forms typically automatically update the table data, I have needed to implement vba in the _BeforeUpdate() in order to prompt the user to Save before Entering the Subform or Closing the Form. This Holds off on updating the data in the table unless the user explicitly tells the application to. This also occurs before Entering the Subform.

    Since the position information must be completed before the employee information, is it possible to implement the input validation they are looking for on this bound form? In the example I gave above, when the [PositionStatus] = 'Staffed' there must always be a person in that position, however since the save event occurs at a different interval for the Position and the Employee, I don't see how I can achieve this while maintaining the nature of not updating table data until 'Saved'.

    If I put the Input Validation in the Subform, then there would be no Input validation on the other side. So, if A person is listed in the subform and everything is just dandy, then the user changes the [Status] on the mainform, there would be nothing stopping them.

    Any insight is greatly appreciated! Hopefully I am just missing something entirely.

    Thank you,
    Skid

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It reads like a catch-22 situation, but I have to admit it's late and I'm not sure I follow exactly. However, this may be one of those cases where using temp tables will be easiest. You allow user to fill out either form and do validation whenever you see fit. When all is OK, you copy the record(s) from these tables to the primary tables then erase the temp records. This has the added advantage that if for any reason the process needs to be cancelled, it is no problem regardless of what stage any form completion is at. You simply delete the temp records.

    Note that I am not meaning tables that you repeatedly create and delete when I say temp tables. It is the data that is temporary, not the tables.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Depends how hard you want your validation rule to be applied, this is not the sort of rule that can be applied at table level.

    Don't know your table structure but a control with something like this as a control source

    =iif(dcount("*","tblEmployees","PositionFK=" & [PositionPK]),"Staffed","Vacant")

    will update once you have assigned an employee (might just need to requery the main form from the subform in the subform afterupdate event - code would be me.parent.requery)

    then perhaps your control can have some conditional formatting to show the control background red if it is displaying 'Vacant' and you have a 'must be staffed' checkbox set to true.

    Your validation rule would then be applied to prevent the form from closing or moving to another record (updates are OK) until either an employee has been assigned or the 'must be staffed' checkbox is unticked. However still leaves an issue if the user experiences a power failure or similar event when the main record has been saved, but not the employee record.



  4. #4
    skidawgs is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    26
    Thanks Ajax,

    I really like the idea of using a separate control to identify if there are records in the subform. I may end up needing multiple controls in order to implement each of the validation rules they are looking for, but this seems like a sound way to solve my problem, thank you! Also, it shouldn't be too problematic if the validation is not enforced during a power failure. I'll be sure to inform the user of this shortcoming as well.

    -Skid

  5. #5
    skidawgs is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    26
    Thanks for your response, Micron!

    I am not too familiar with using temp tables, however am going to do some more research to see how I can use them to solve my problem.

    Thanks!
    -Skid

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    A separate control is not essential, but makes it clearer to the user. The main point is to apply validation on the form close event or move to another mainform record (which would need to be handled with a button click)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 56
    Last Post: 05-28-2017, 05:56 PM
  2. How to filter bound subform from unbound main form?
    By ittechguy in forum Programming
    Replies: 3
    Last Post: 10-25-2015, 09:12 PM
  3. Validation in main and subform
    By crxftw in forum Forms
    Replies: 3
    Last Post: 09-03-2011, 07:01 AM
  4. Replies: 2
    Last Post: 06-17-2010, 04:15 PM
  5. Replies: 1
    Last Post: 06-14-2010, 02:31 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
  •  
Other Forums: Microsoft Office Forums