Full Version: force subform to create record
UtterAccess Forums > Microsoft® Access > Access Forms
matulga
(My apologies in advance if I use incorrect terminology. I think I'm getting it right but I don't know for sure.)
My database is setup such that there is a great deal of information stored for individual staff members but it is split amongst many tables (i.e. names, application information, salary information, contact information, etc.). Each table is joined with a 1-to-1 relationship on the primary key for each table, StaffID (autonumber). I have a parent form based on the names table, and within that I used the tab control to allow me to view subforms for every other table.
On one of the subforms, it is mostly checkboxes representing the employment paperwork they have returned and other such things.The problem I'm running into is that when I'm inputting information on that person, first by typing her first name, last name, etc., then by using the tabs to enter info on each subform, records aren't necessarily created in each table. If I don't actually change anything on a given subform there is no record created in the table it is bound to. For some things this doesn't matter, but on employment paperwork subform I refer to above, a box that isn't checked is as significant as one that is. I can always check a box and then uncheck it again, but I'm hoping there is a more elegant solution. What I'd like to have happen is when I create a new record in the Names table (using the parent form), if there isn't already a record for that person in any of the tables bound to subforms, it will create one with the default values in each of those tables. Is this possible?
(I'm guessing there may be a reason it doesn't already do this, so please tell me if this is a dumb idea.)
Thanks in advance!
ace
I don't see why you want a record created when there was no user input.
o record means no paper work has been returned for that employee.
Thats eactly the correct information.
matulga
The problem is that in some of the subforms, the default values are significant. Let's say one of the checkboxes is NeedsToCompleteW4Form and the default value is True. If I view that subform without changing any of the data, a record isn't created for that person in the corresponding table. When I then view a report on who needs to complete a W-4 form, that person isn't listed because that record was never created. Does that make sense?
All of the data I'm keeping could belong to a single table, and that would solve the problem, but I'd much prefer to leave it split into several tables with one-to-one relationships to keep things a little cleaner and prevent having a GIANT table.
Rainlover
I believe what Ace said is correct. When you create the Report and in the underling query you should join the Employee table to the Details table. Show all employees and only those that match in the details table. This will give the result you require.
matulga
Here's a perfect example:
I have a subform (and corresponding table) with a checkbox for if a person has logged into our website for the first time. I just went through and checked all who have done so. I then created a report based on the name fields in one table and the logged in field in the other (one-to-one relationship). I set it to group by logged in value so I would have a list of those who have logged in and those who haven't. However, since there is no record in the table where logged in resides for the people who haven't, there is nothing to group on so I ended up only with a list of those who had logged in. To test what was going on I went back to one of the people who hadn't logged on, clicked the logged in box, then unclicked it. That forced a record to be created for them, and now that grouping level shows up with that person's name under it. Does that clarify the issue? Any ideas to fix it??
Rainlover
If you use a one to one relationship you will get the same results. Try one to many.
Just an idea.
ace
If you want to make sure records are created in each of the related one to one
tables then create a record in each one in the AfterUpdate event of the parent
form when a new record is created.
omething like
CODE
CurrentDB.Execute ("INSERT INTO RelatedTable (MemberID) " _
                  & "Values ("  &  Me.MemberID & ")"

Is all you need.
matulga
Perfect! That did it. Thank you! frown.gif
Rainlover
This design may help in future development. There are two tables. One has information Two has some info. As you will see by looking at the query, all information from Table one is displayed regardless of what is in table two.
Cheers.
kellydor
Rainlover, can you create this for Acc2000?
Rainlover
Here it is
kellydor
Thank you frown.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.