Results 1 to 10 of 10
  1. #1
    DaQwyetOne is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    4

    Split form adds new record but populates main form with previous record

    This is my first time trying to create a database in Access, so excuse the 'ignorance.'


    I have a cascading combo box where a manager selects their name [Project Manager], and it filters the second combo box to just their projects [Project Name]. Then, they select an employee [Team Member] (from a list of employees on a [Employee Data table]) and enter 'scores'. When the [Project Name] combo box is filtered to a project, the split form below shows the current employees attached to that project along with their scores.

    What's happening is when I select a project, and there's already employees listed on the split form....a new record is created on the split form, but the main form will populate with the first employees information on the split form. Here's the coding that I have so far on my form:

    Private Sub Project_AfterUpdate()
    Me.Filter = "[Project Name] = '" & Me.Project & "'"
    Me.FilterOn = True
    End Sub


    Private Sub Project_Manager_AfterUpdate()
    Project.Requery
    End Sub

    I've tried to add a New Record button, but the same issue happens. Any help you can provide is greatly appreciated.

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum...

    Disclaimer: I have read a little (very little) about split forms, but have never used one. I would use a different design.

    A word about "Best Practices" : you should not use spaces in any object name - be it field, table, query, form or report. It will cause you headaches...

    So, you have told us HOW you are doing things, but WHAT you are doing (what you want to accomplish). Maybe explain it like I am a ten year old.
    This is not clear:
    Quote Originally Posted by DaQwyetOne View Post
    I've tried to add a New Record button,
    A new record for [ProjectManager], [ProjectName] or [TeamMember]?

    It would help to see your dB - would you post it?

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    A split form looks like a main form with a subform, but it's 2 views of the same information. It's not for the 1 side (main form) of a many side (subform) relationship. If you choose a field in either form half and the corresponding field is also selected in the other half then for sure you have a split form. Given the nature of the problem you describe, then yes, filtering a form and then trying to add a record will pose a problem in that you'll likely end up editing records that are already there. You don't have to have the same fields in both halves if you intend to just look at data (e.g. it may be worthwhile to filter with a combo) but I would not use this form type for adding records. For that type of view and record adding, use a subform arrangement. My experience with split forms is in dealing with issues from posters like you, so I'm limited as well. I don't see the point in having 2 views of the same data so it's not likely I'd ever create one for my own use.
    Last edited by Micron; 01-28-2020 at 04:41 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    DaQwyetOne is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    4
    First of all, thank you for the advice re: spacing.

    I have a group of projects managers, that have a number of project assigned to them. For each project, I want them to go into the database and input who [Employee Name] is on a specific project and give a monthly score that corresponds to how much time that person will dedicate to that particular project. What I'm hoping to accomplish: Project Manager finds their name, the Project combo box lists their currently assigned projects. When a specific project is selected, then the split form will populate with only that projects team members (entered previously by the project manager) and each team members scores. So, if I pull up my name and a specific project...I want to be able to see in the split form which team members I've already entered and their scores. I will need the ability to add more team members and/or update previously added team members scores. The database works perfectly when I'm entering information for the first time, but if I go back to a project and want to add more employees, the split form adds a new record but the main form populates with a previous record.



    Here's a screenshot of my main form:
    Click image for larger version. 

Name:	Mainform.PNG 
Views:	18 
Size:	10.6 KB 
ID:	40766

    My apologies for not posting my actual database, but it contains employee data.

    The New Record Button is to add an additional [Team Member].

    Not sure if I did a good job of explaining, or just made it more confusing.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    A split form is a specific thing and the picture leads me to think you're not aware of that. What you show doesn't look like a split form to me, and it raises other red flags about the design of your db. I think you might have underlying problems there but I will have to leave it to others to flesh that out. I've got a table to make and have to devote less time to forums. The main issue with your pic is that it suggests you will have another form for next year - not good. It also suggests that tables are not correctly designed because you have several controls that relate to the same thing - what probably ought to be a single date field yet I suspect you have several. Given the potential for so many underlying design issues I'm afraid I have to bow out of this one. Don't worry, ssanfu is great at this sort of thing.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    DaQwyetOne is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    4
    Here's what the db looks like the main form and the split form below.
    I'm open to better "design" options and/or suggestions. As I stated previously, I'm coming with no background knowledge of Access.
    Click image for larger version. 

Name:	Mainform.jpg 
Views:	18 
Size:	52.5 KB 
ID:	40769

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    How about making a copy of the dB, then changing the names and sensitive data. Only need a few records to see how the dB operates.

    Your table design does have a problem - you have the year in the field name.
    What are you going to do in 2021? You will have to edit at least one table (to change the field names) and possibly/probably the queries, forms, reports and some code. The year should be in a separate field.

    Also, there are spaces and special characters in field names.
    As far as object names, best practices for object names are:
    1) Object names should be letters and numbers.
    2) Do not begin an object name with a number.
    3) NO spaces, punctuation or special characters (exception is the underscore) in object names.

    I would suggest using a main form and a sub form. You could select the Project Manager and the Project Name with cascading combo boxes and use the sub form to add/edit the employees. But without seeing the table designs and relationships, I can't suggest anything else.

  8. #8
    DaQwyetOne is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    4
    Attached is the db with the employee data removed and I've just added sample projects/employees.
    After reading through all the comments, I think I will take the advice of moving the month/year to the subform.

    ssanfu, thank you for your help and patience.
    Attached Files Attached Files

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Since you are new to Access, I would recommend that you check out this page by orange - there are several/many articles in this link related to Database Planning and Design that may be helpful.
    Then I recommend you work through the tutorials from Roger's Access Library mentioned in the link. You can work through a tutorial in about 30-45 minutes and what you learn/experience/refresh can be used with any database.
    You should be able to tell everyone (in simple terms - no jargon) about the things involved (your subject matter and eventual tables) and what relates these things.
    Finally, you should use paper/pencil, a whiteboard, a window, anything to write on to design your tables/relationships BEFORE jumping into Access.

    Questions:
    1: Are supervisors different than project managers?
    2: Can more than one project manager be assigned to a project? Can Betty Jones AND Amy Williams be assigned to Project 4?


    After looking at your dB, at this point I see at least 7 tables:
    tblProjects
    tblManagers
    tblSupervisors
    tblEmployees
    jnctResourceUtilization
    tblProjectStatuses
    tblJobTitles


    Something like this (first pass)
    Click image for larger version. 

Name:	Relationship1.png 
Views:	10 
Size:	95.1 KB 
ID:	40774

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Post #9 was moderated - posting to trigger email notification.

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

Similar Threads

  1. Replies: 4
    Last Post: 07-21-2015, 02:50 PM
  2. Replies: 1
    Last Post: 04-11-2015, 10:31 AM
  3. Replies: 15
    Last Post: 05-24-2012, 02:36 AM
  4. Clicking on Form Background Adds New Record?
    By swimmermx in forum Forms
    Replies: 3
    Last Post: 08-03-2010, 12:14 PM
  5. Replies: 3
    Last Post: 09-26-2009, 01:57 PM

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