Results 1 to 9 of 9
  1. #1
    student_2744 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    5

    Question Command Button creating new form with a new table as control source... is it possible?

    Hello,



    Before I get to my question, let me give a background first:


    I am an individual working with Microsoft Access 2016.

    I am designing an access database for a 'school student management system'. This means that I have created tables and forms related to students (name, class, subjects,...) and teachers (name, subject(s) taught). The database currently has the ability to enable users to view, edit, or add new student/teacher records.

    The next step I need to achieve is to expand my system so that the main user and administrator can create report cards for the students and fill in their marks. Then the report cards get saved and can be opened to be viewed or edited anytime. The users of my system are not familiar with ms access and so the only way they can work with the database is by forms.

    I understand that access is a database meaning it stores data and is not designed to act as a software for generating documents such as report cards. However, I do not have the knowledge to code this management system so I saw no other choice than to work with Microsoft Access. All I know is an intermediate level of VB.

    Now, my plan is to create a form called "new report cards" where the administrator can choose certain criteria such as the students who they want to create a report card for and etc.. Afterwards, I want the "new report cards" form to have a command button that, when clicked, applies the criteria to a NEW form and creates a NEW table (with the criteria as fields) as the control source. From there, the administrator can type and save the records to the new table from the new form.


    I know that this is possible to do from the ribbon of ms access, but as I said, the users are comfortable with working with forms and the objects inside forms. So, to get to my questions:

    1) Is it possible to have a command button open a new form with a new table as its control source?
    2) If yes, can the table be created with specified criteria (specified in a form)?
    3) If no to both, is there any other way to achieve this (other softwares that do not require heavy coding)?


    Thank you in advance for all the help and suggestions. I have searched but have not been able to find the answer to my questions.

    Also asked:
    https://community.spiceworks.com/top...#entry-8560881

  2. #2
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    This should be no problem to create a Report Card for any or all students, you can create a Report and use a query to generate the grades.

    I understand that access is a database meaning it stores data and is not designed to act as a software for generating documents such as report cards.
    Dave

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    NEW form and creates a NEW table (with the criteria as fields)
    You're going about this in a hard way.
    Why a new form? You can reuse a given form by specifying the criteria for its recordsource.
    And a new table? A new table for each report card? Don't you want to store ALL student grades in an existing table, and pull that data for a given student's report card? The report card would be an Access report.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Some general thoughts that I don't think have been addressed. The belief that "access is a database meaning it stores data and is not designed to act as a software for generating documents such as report cards" is not accurate. Access is actually 2 products, the "database" (ACE/JET) and interface design. You can create very comprehensive forms and reports with the design component, and either use ACE/JET as the database or SQL Server, Oracle, etc.

    While possible, it would be highly unusual to create new forms and tables at runtime. In your situation, you would provide a form for the user to add classes for a student, which would not be a new table but new records in a table that held student classes. A report on that table could serve as your report card. Presumably the example database will demonstrate that.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Geez, slow fingers today.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    If you can post a zipped copy of the database we'll be happy to assist and provide an example report.

    Dave

  8. #8
    student_2744 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    5
    Hello everyone,

    Thank you for the wonderful help! I understand now that I'm trying to go about this project in a not-so-clever way. Perhaps it stems from my inexperience with MS access.

    I will follow all the advice given here, and if I feel like I am stuck again, I will post a zip copy of the db as Dave14867 suggests.

    Thank you.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Orange was kind enough to mention my School Data Analyser application.
    Although its a commercial app, there is a DEMO version available with staff, student and whole school data for a fictitious school.
    See http://www.mendipdatasystems.co.uk/s...ser/4584605482
    Its an ACCDE file so code isn't accessible but it may give you some ideas.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 1
    Last Post: 02-27-2015, 10:01 AM
  2. Replies: 4
    Last Post: 11-22-2014, 11:43 PM
  3. Replies: 1
    Last Post: 09-12-2014, 06:09 AM
  4. Replies: 1
    Last Post: 11-30-2013, 03:43 AM
  5. Replies: 3
    Last Post: 03-07-2012, 09:49 AM

Tags for this Thread

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