Results 1 to 7 of 7
  1. #1
    smhst57 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2013
    Posts
    4

    Form/subforms from multiple tables- how to link fields properly?

    I am creating a database that will track student information, including expected graduation dates by term (e.g. Spring 2014). Each term has an official university code, e.g. spring 2014 = 2144. I know these codes and prefer to use them when tracking students (rather than the season year combo), but students generally do not. Therefore, I want to create a detail form in which students will enter their graduation date using the season and year, but it will be stored in the students table as the term code. Ideally, the student will see 2 controls: 1) a drop down menu with "fall" "spring" and "summer" as the options, 2) a text box control in which they would type in the year of expected graduation.

    Currently, I have 3 tables relevant to this question:
    1) tblStudents:
    Relevant Fields = Student # (PK), Grad_term (joined to tblTerms.Term_code)
    2) tblTerms:
    Fields = Term_code (PK), Season (look up field to tblSeasons.season), Year


    3) tblSeasons:
    Fields = Season (contains only values "fall", "spring", "summer")

    I currently have a master form based on tblStudents with a subform, based on tblTerms, with linked master field (Grad_term) and child field (Term_code). The subform displays the season and year. However, when I enter the season and year, it does not record the correct term, and sometimes it just simply displays nothing.

    I am new to access and I feel like I am missing something very basic here. I would be very grateful for any insight. Thanks!

  2. #2
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742

    One Easy Approach

    1) Best to avoid reserved words in naming fields in Access. I've changed "Year" to "Term_Year" to save you some grief later.

    2) Back up your database before attempting the following. Build the habit of backing up before any coding, doing your coding in a test database until you find something that will work, and then coding only the working version into the real database.

    3) For what you've mentioned, a subform is not needed. Since there are only four years and three seasons, a single combo box will be effective for the purpose of selecting between twelve items in sequence. Try building a combo box using the following specifications:

    Control Source will be Unbound, or Grad_term, depending on how you want to use it. More notes about that at the end.

    Row Source will be
    Code:
    SELECT TT.Term_code, TT.Term_Year & " " & TS.SeasonName 
    FROM tblTerms AS TT 
    INNER JOIN tblSeasons AS TS
    ON TT.SeasonID = TS.SeasonID;
    You don't need the carriage returns, they are there just so you can read the SQL easily. You didn't give me the name of the PK/FK fields for Seasons, so I made them up, obviously.

    Two more properties to set on your combobox.

    Bound Column will be 0 (Which means Term_Code will be stored)
    Column Widths will be 0; 2" (Which means the Term_Code won't be visible)

    NOTES ABOUT BOUND/UNBOUND

    I'm not going to go into all the security aspects of having students able to modify data in your database. I assume you are cutting down their authority to their own ID, somehow. You have two options when using a combo box like this -

    (1) Bind the combo box to the field in the database, and have it automatically update when updated.

    (2) Have the combo box unbound, and have its AfterUpdate event validate the selection, then set the value of ANOTHER field on the form (this one bound), to the validated value.

    Either of those methods has its place.

    Hopefully this helps.

    Dal

  3. #3
    smhst57 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2013
    Posts
    4
    Thanks so much for your response!

    I actually did call the one field Term_Year. I just mistyped it here.

    The problem with the single combo box is that there are many more than 4 years. Currently, I have 48 records in the tblTerms and plan to add more as I hope to enter data on alumni going back many years and to use this for quite a few years in the future.

    As for security concerns - this is a local database stored on my computer designed to track the progress of 25-35 students a year in a certificate program. We currently have a paper application form that students fill out by hand. Since they almost always fill out the form while sitting in my office, I was hoping to simply create an Access form that they could type the information into themselves instead of writing it out and giving it to me to type up. Perhaps this is a bad idea?

  4. #4
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Not a bad idea as long as you establish controls on it, physical or systemic. Basically, you set up a form that doesn't allow any access besides the current student, and that requires a password or other non-obvious action to change. Then you sit the person in front of that one form.

    Hmmm. I'd just make a separate front-end that links to your database and has only the minimum forms necessary for whatever they are entering. That way, there isn't any sensitive data to be looked at. You might even link it to an "input' table in the backend, where you can review the records before allowing them to be moved to the real table. Your choice. Don't build the Sistine Chapel when you really need a bungalow.

    About Years - Remember, your database can have many years in it without those being reasonable choices for a current student. A doctoral student will need 5-6 years out, but most of your students only need the next few years. So, for that form, you could use it as I described above, with a calculated limit on years - current year to year + 5 or something.

    Code:
    SELECT TT.Term_code, TT.Term_Year & " " & TS.SeasonName 
    FROM tblTerms AS TT 
    INNER JOIN tblSeasons AS TS
    ON TT.SeasonID = TS.SeasonID
    WHERE TT.Term_Year BETWEEN (Year(Date) - 1) AND (Year(Date) + 5);
    Not sure whether there should be some & around the Year terms.

    The SQL could also be modified by other controls on the form. You could put in a text box where they can enter the year, and have the AfterUpdate event of the text box set the SQL for the combo box. That's the general concept. Doesn't much matter if it's a checkbox that says "include all years" or a text box for some other bell or whistle.

    If you have two or more controls that are changing the SQL, then I highly recommend coding a procedure to set up the SQL, and have it read the various controls to figure out what kind of SQL to build. This way, each control can call the same procedure and the resulting SQL will be correct.

  5. #5
    smhst57 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2013
    Posts
    4
    Yes - that is what I was planning to do with the form. And I will definitely think about the suggestion to make a separate front-end.

    And thanks for the suggestion on the years - I'll try that. I have been trying to do this whole thing without using code, but I'm realizing that if this is going to be a useful database, I'm going to have to learn to use at least a minimal amount!

    Thanks again for the help!

  6. #6
    smhst57 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2013
    Posts
    4
    Yes - that is what I was planning to do with the form. And I will definitely think about the suggestion to make a separate front-end.

    And thanks for the suggestion on the years - I'll try that. I have been trying to do this whole thing without using code, but I'm realizing that if this is going to be a useful database, I'm going to have to learn to use at least a minimal amount!

    Thanks again for the help!

  7. #7
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    You're welcome.

    Code's not that tough. Just learn one thing at a time, enough to do the job in front of you. The rest will come in time.

    If that's all you need, please mark the thread "solved". Top of page, under "thread tools".

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

Similar Threads

  1. Replies: 10
    Last Post: 05-29-2013, 11:39 AM
  2. Replies: 12
    Last Post: 12-14-2012, 06:25 PM
  3. Replies: 1
    Last Post: 10-28-2012, 07:23 PM
  4. Link Multiple Tables to One Form
    By kristyspdx in forum Forms
    Replies: 2
    Last Post: 04-02-2012, 05:04 PM
  5. Multiple tables served by one link
    By htchandler in forum Import/Export Data
    Replies: 3
    Last Post: 01-13-2011, 01:49 AM

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