Forms + Subforms problems (1 Viewer)

Maria83 · Jul 22, 2014

Hi Guys,

I'm in the process of creating two forms, each with a subform, that will be used to populate my tables. I've attached a screen shot of the relationships that exist between my tables and I've also attached a screen shot of one of the Form/subform's I created.

The problem I'm having is when entering anew record. I can in put GrantName, Principle Investigator, and project title with ease, and can move through these using the Tab key.

What I'd like to do is tab from the ProjectTitle to the Institutions Subform. Then I'd like to either add a new institution or have the Institution i'm looking for self-propagate. Then I'd like to Tab to the InstitutionID field back on the Parent Form.

The problem I'm encountering now is that the Tab function only goes through the first four fields (ApplicantID, GrantName, Principle Investigator, and Project Title) and then I have to use my mouse to click into the InstitutionID field. I can then randomly select a number and hope it comes up in the Institution SubForm. This is problematic for a couple of reasons: 1. if users don't know which institution is related to record 238, for instance, they won't be able to fill out the form and populate the All Applicants Table. 2. If a new Institution needs to be added, I can do that below first, but I can't Tab to the subform without first filling out the parent form.

A second problem I'm encountering is that I cannot add a new entry using this form without indicating a GrantID, which I find odd, because in the Parent table (All Applicants Table) every record isn't associated with a GrantID, so I thought I should be able to add multiple records to the All Applicant Table without specifying a GrantID.
I do not want users to have to fill out the GrantID field in this form, rather I'd like them to have to fill that information out in the second form I create for Awarded Grants, once news of awarded grants is obtained.

If you guys have any suggestions as to how I can fix these issues I'd really appreciate it.

Maria

GinaWhipp · Jul 22, 2014

Hmm, I think you might be missing a Junction table or two. So, anyway you can upload the database (remove all personal information) so I don't have to start from scratch?

Maria83 · Jul 22, 2014

Hi Gina,

I already populated the entire database with private info, so can't upload the entire database. I was able to use the form wizard to create the form that I want, I've attached a new screen shot of the form. In form View I can select "new blank record" and it clears all fields, but it doesn't allow me to in put anything. I can't click inside each text box, my cursor doesn't show up, so I can't populate the tables using the form.

Using the form wizard I was able to select the fields from each table that I would like to populate using the form.

I think my problem may be due to the relationships I built. You said something about me missing Junction Tables? Could you elaborate, or maybe list the steps on how I should create those tables and how that will allow me to create the form?

GinaWhipp · Jul 22, 2014

A Junction table allows a many to many relationship. In order to explain that more clearly as it relates to your database, I need to understand a few things...

1. What is All Applicants?
2. Do Institutions compete for Grants?
3. Can Institutions apply for more than one Grant?
4. Can one Grant be awarded to more than one Institution?
5. Where is the table that holds all the Grants?

Maria83 · Jul 22, 2014

Okay, sorry about that, had help creating the databse in the "database design" group and can't link to that discussion, so I'll just answer your questions here. Fist, I've attached an excel file that holds some sample data, each sheet relates to a corresponding table in the database.

1. What is All Applicants?

The All Applicants Table holds data for every applicant that the organization has received a grant application for (the organization has several grants with different grant names).

2. Do Institutions compete for Grants?

Principle investigators compete for Grants and Principle Investigators are associated with their host Institutions.

3. Can Institutions apply for more than one Grant?

Principle Investigators can apply for more than one grant and so Institutions can be linked to more than one grant.

4. Can one Grant be awarded to more than one Institution?

Yes, one grant can be associated with more than one institution and this would require a Junction table in my relationships, which I plan to institute later on, but I can't see how this junction table effects why I cannot populate the revised form I posted in my last post.

5. Where is the table that holds all the Grants?

The table that holds all the grants is named: Awarded Grants (and is linked to the All Applicants through the GrantID primary key in that table).

Hopefully this clears things up. Do you know why I'm unable to populate the text boxes on my form?

Maria

GinaWhipp · Jul 22, 2014

It is the Record Source...

You are using a query and the query joins are making the query un-updateable and since it is the Record Source the Form won't allow data entry.

If you are not using a query then the relationships are causing no new records on the many side because of a missing matching record on the one side.

Having a Junction table would remove both issues from above. Allowing many-to-many means you don't need to worry about having matching records when trying to update the many side of one-to-many relationship.

In the meantime, I'm going to throw something together which I hope will explain visually how your Relationships should be set-up.

Side Note, I did find that discussion and I see now that the subject of Junction tables never came up.

Maria83 · Jul 22, 2014

Thanks Gina,

I don't know a lot about Junction tables, and no it didn't come up in the other group discussion. My only concern is that when users fill out the form they don't have to use GrantID or InstitutionID's. I want them to be able to input InstitutionName's and I want the database to accept new names but recognize names that already exist in the database.

I also need it to allow users to in put applicant information without a grant number. If you notice in the All Applicants Table, only some applicants have GrantID's associated with them, and this is because only some are successful. the form, as it is, doesn't recognize the empty fields, and I don't know how to change that. I'd like it to only update the GrantID field once the AwardedGrants Table is populated. But right now the all applicants form asks for that information with all new entries and doesn't allow users to proceed to enter data without filling in that field.

Will the junction tables fix those problems as well?

Thanks For your help, btw, I really appreciate it

GinaWhipp · Jul 22, 2014

Hmm, okay that was a lot to take in. I'm working on the Data Model (Tables) now.

GinaWhipp · Jul 22, 2014

Okay, getting closer to answering your questions in Post #7. What is ProjectTitle in the All Applicants table? Is it the name of the Project the Applicant is seeking a Grant for?

GinaWhipp · Jul 22, 2014

Nevermind, I got what ProjectTitle means from the spreadsheet, which I just now opened!

Maria83 · Jul 22, 2014

GinaWhipp said:
Okay, getting closer to answering your questions in Post #7. What is ProjectTitle in the All Applicants table? Is it the name of the Project the Applicant is seeking a Grant for?

lol yes it is, I think the excel file that you opened should help, I gave that to the guys in the other group discussion

GinaWhipp · Jul 22, 2014

I don't know a lot about Junction tables, and no it didn't come up in the other group discussion. My only concern is that when users fill out the form they don't have to use GrantID or InstitutionID's. I want them to be able to input InstitutionName's and I want the database to accept new names but recognize names that already exist in the database.

The way the Tables are set up now you can enter the entities separately or use Combo Boxes to search for existing entities.

I also need it to allow users to input applicant information without a grant number. If you notice in the All Applicants Table, only some applicants have GrantID's associated with them, and this is because only some are successful. the form, as it is, doesn't recognize the empty fields, and I don't know how to change that. I'd like it to only update the GrantID field once the AwardedGrants Table is populated. But right now the all applicants form asks for that information with all new entries and doesn't allow users to proceed to enter data without filling in that field.

This is where the Junction table comes in. By removing GrantID from the Applicants table they will be able to enter an Applicant without a Grant Number. The new table, tblApplicantsGrants, allows for many-to-many without the dependency of the ApplicantID or the GrantID. (You said you were planning to add the Junction table later, well, you don’t drop in a 2,000 square foot foundation and plan to build a 3,000 square foot house later. You have to build a database the same way... get the foundation (the tables) right at the very beginning.)
Now, when you create your Form you should have no trouble. Though, because an Applicant can have more than one Grant, you might want to consider using a Subform.
Side note: The TEXT fields are all set for 255; you probably want to change that. May I also suggest you populate with test data until you have the Forms set up and working. This will make the database available for upload should you require additional help (should you decide to use this Data Model).
I hope I have explained that clearly enough!

Maria83 · Jul 22, 2014

Okay, thank you so much! I do have some questions though.

So, in the meantime I did use a combo box with subform to solve one of my problems, but I am still having an issue with the grantID (as you noted). I've attached a word doc with the error message I get, which I'm sure you are aware of.

I noticed the new table, tblapplicantgrants and I see what you did there and kind of follow that, but there are three other tables below that I don't get. I've attached a scrren shot of that.

If I just add the table tblapplicantgrants with the relationships you've indicated without the three tables underneath should it all work? And with that one junction table, do I still need the combo box?

Thanks again

Maria83 · Jul 22, 2014

Also, why is ProjectTitle in the Junction Table (tblapplicantgrants)? I'm not sure if I indicated this anywhere, but All Applications will have ProjectTitles associated with them, but all those Projects may not be funded, and so only a portion of the Project titles end up associated with GrantID's. Should the Project title still be in the junction table in this case?

cruzinonline · Jul 22, 2014

Gina,
I think this is what I am looking for but I am having a hard time getting the picture. Hopefully you can help.
In this case I have one table with a list of topics and questions. Another table logs in the basic information and I have another table that I probably don't need.

There is a sample form of what I am trying to get at. Basically an employee goes into the field and observes a working crew. He then logs in his observations. There are 12-13 different types of observation topics with 8-25 questions per topic.

What I would like the person be able to do is enter on the form there name, date, comments, what they observed, and then pick from a drop down the topic. From there it would be nice if the subform autopopulated with a list of questions where the user could enter a number in the safe, ok, or unsafe column. Even if they could drop down a list of questions related to the topic would be nice.

From there, I would run a report based on a date range, and grouped by Topic with a summary of each persons observations or another report by date range that would group by person and then sort by topics. The reporting part should be a breeze, it's just collecting the data that is stumping me. This is a little more detailed that what I am used to and I am thinking the junction table would be the solution.

The form you currently look at lets me choose a topic and on refresh, loads the questions but I am not able to enter any data in the subform.

Hopefully the attached file is available for your review. Thanks in advance.

Ray

GinaWhipp · Jul 22, 2014

@cruzinonline

Umm, I'm cunfused as to why you are posting in Maria83's thread. Please start your own. You can copy/paste this into a new thread and then delete this one. We will all get confused mixing like this.

GinaWhipp · Jul 22, 2014

@Maria83,

First of, ignore those three tables, those are for Access and have nothing to do with your Tables.

You could try just adding and changing the relationships, remember this was to give you a way to get what you want. I do believe doing so should fix your issue with the Form.

Project Ttiles - But wouldn't those be attached to the different Grants. It seemed to me that belong attached to the Grants and not the Applicaant because it's many Project Titles to one Applicant.

Maria83 · Jul 22, 2014

"Project Ttiles - But wouldn't those be attached to the different Grants. It seemed to me that belong attached to the Grants and not the Applicaant because it's many Project Titles to one Applicant."

The only thing with Project titles is if I only have them listed with the successful grants then I can't track the project titles of applications that were not funded. Applications that were not funded do not end up in the table Awarded Grants.

With your help and the database sample you gave me, I did get the form to work now!

Thank you so MUCH!

I had to create another form for funding using the Awarded Grants table and the Fiscal Funding table, and was having problems as previous, so I built a junction table and I've attached a screen shot of the relationship. Was wondering if you could look it over to see if you notice anything incorrect. I will run some queries and reports and try to populate the tables using the form to be sure it works, but would really appreciate if you could look it over just to make sure.

I'm not sure if I need a junction table here, I created it because I initially had the Awarded Grant ID in the Awarded Grants table relating to "Awarded Grant ID" in the Fiscal Funding Table by a one-to-many relationship, and so when I created the form it was difficult for users to fill because they didn't know all the GrantID numbers. I changed to a Junction Table and added the GrantNumber field on the Fiscal Funding Table so that users can populate the Fiscal Funding Table using the GrantNumber (instead of the GrantID) and the Junction Table allows for the information from both tables to still be related.

Did I make the right move using a junction table?

GinaWhipp · Jul 22, 2014

Project Titles - Okay, well, that makes sense, I guess I didn't understand that.

Glad to know Form is now working!

New Junction table - Not sure I understand because you can look it up the the Grant Number in the Awarded Grants table. and since the Fiscal Funding is on the many side it would be required.

Maria83 · Jul 23, 2014

GinaWhipp said:
New Junction table - Not sure I understand because you can look it up the the Grant Number in the Awarded Grants table. and since the Fiscal Funding is on the many side it would be required.

Okay, sorry but your last reply may have a typo in it, did you mean to write "it would NOT be required" ? I think I'm going to remove the new junction table and see if everything still works.

Thanks again

Maria

Forms + Subforms problems (1 Viewer)

Registered User.

Attachments

AWF VIP

Registered User.

Attachments

AWF VIP

Registered User.

Attachments

AWF VIP

Registered User.

AWF VIP

AWF VIP

AWF VIP

Registered User.

AWF VIP

Attachments

Registered User.

Attachments

Registered User.

Registered User.

Attachments

AWF VIP

AWF VIP

Registered User.

Attachments

AWF VIP

Registered User.

Similar threads

Users who are viewing this thread