You are here:

Using MS Access/Access 2010: main/sub forms - queries

Advertisement


Question
Screen 2
Screen 2  
Screen 1
Screen 1  
QUESTION: Hi Scott,

I have a main/sub form that I need assistance with.  Based on the nature of the database and the end users that will eventually be using it, I wanted the Subform to be in SplitForm view.  However, as you can see from the attached screenshots, the existing data in the tables/queries that connects displays, but it will not allow me to add any additional records, and the subform will not display when adding a new student data (screen shot 2).  

Is there a way to work with the splitform view in a subform so that the end user can use the database in this manner?

ANSWER: First, I'm going to say I'm not a big fan of the Split Form. I have used it on some rare occasions where it fit, but I've found its not much of an advantage.

Looking at your screen shots, I don't quite understand one why you think its a good idea and whether you are actually using one. A split form places a datasheet view of all records in the Recordsource at the bottom of the form with a set of controls in columnar format at the top. The top part reflects the record currently selected in the bottom.

But if you are using a subform, generally the main form selects the records to be displayed in the subform, either in a continuous form/datasheet view or a single form view. All I see in your screen shot is a single form view for the subform.

Is this app solely for tracking transfer credits or is part of a larger app that tracks many things about students. If the former, you need at least 2 tables. A Students table and a transferred credits table. In your case, if you want to use a continuous form/datasheet view for the subform then I would use a popup form to add/edit the data.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA



---------- FOLLOW-UP ----------

QUESTION: Hi Scott,

Thank you for your response.  I have never used Split Forms, but after working on this for the past week, I am going to agree that I am not a fan either.

Also, I have never created a popup form, but from what I am reading, I am liking the idea and would like to attempt to set this up in my database.

The app is solely to track transfer credits, and I do have at least 2 tables (one for student and one for transfered credits).  So far, by creating the popup form, I have created a command button, but I cannot figure out the code in order for the subform to connect with the main form.  Do I setup a macro or a class event?  

Would you be able to assist with the coding?

Thank you
Kelly

ANSWER: Ok, before we go with the popup form, I'm more included to use a regular main/subform. I would create your mainform based on the Students table with a subform based on the transfer credits table linked on StudentID. On the mainform I would use the combobox wizard to add a search (3rd option) combo in the form header to select a student.

So when you select the student, the subform displays the transfer credits for that student.

Form here you have a choice. You can set the subform up in continuous form mode (tabular) so you can see most (if not all) of the transfer credits in a scrollable list. However, such a form might not be wide enough to display all the fields. Or you use a form view to show a form that you can navigate between records. If a continuous for is a problem its where the popup form comes into play. You can put code in the form's Double Click event like so:

DoCmd.Openform "frmTransferCreditsPU",,,"[TransferCreditID} = " & Me.TransferCreditID

This assumes each transfer credit record has a PK (in number datatype) named TransferCreditID and you have a control on your subform named TransferCreditID.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA


---------- FOLLOW-UP ----------

QUESTION: Hi Scott

Thank you very much for all of your assistance.  You are fantastic at what you do, and I appreciate the assistance.

I returned to the basics of my DB and started again with the tables and to set it up as per your instructions.  I have three tables (1 for student information, 1 for Transfer Credit Information, and 1 for the course codes/names).  They are joined properly in the relationship database using PK (autonumbers).  

What I am trying to do is to create a query where it joins the Transfer Credit tbl and the course codes/names tbl and have the query complete dLookup so it automatically generates some fields.  We are currently in the process of changing course codes, so I am looking to enter in the old code and have it automatically populate the new code with the course name.  In the query, I have it working, but it will not allow me to add/edit records, which then does not allow me to add new records to the forms.  The query is not setup with GroupBy as I know this would turn the query into a Read Only query.  

I have setup my form as per your suggestion.  The main form is based on the Student Information tbl and the subform displays the transfer credits for that student.  I also was able to setup the subform view as form view, and it works great.  The only issue now is that the query will not allow me to add records.

Any suggestions?  
Thank you
Kelly

Answer
Any time you have a multi-table query, there is the risk it will be an uneditable query, which is what it sounds like you have.

Can I assume that currently the Transfer Credits table has a course code field that links to the Course code table? If so, what you can do is use the Relationships Window and formalize the relationship, turn on Referential Integrity and make sure you have Cascade Updates set. If you do this and you change the Course Code the FK in the Transfer Credits table should automatically update.

I'm not clear on what you meant by: "complete dLookup so it automatically generates some fields". One purpose of a query should be to join multiple tables so as to pull fields from different tables. The subform  should only be bound to the Transfer Credits table. If you need to display fields from a related table, see my blog on Displaying data from related tables.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

P.S. This site limits follow ups, so there may be a point where you won't be able to post a followup. If that happens you might want to continue this on AskMeHelpDesk.com. I also monitor the Access forum there and there are no limitations on followups.  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.