Connect a subform to a datasheet subform

Posted on 2001-08-30
Last Modified: 2008-03-06
I am using Access 97

I have a form called ?student & courses specific form? and a subform called ?student & course subform? (yes there are gaps!).

The main form shows students personal details and the subform shows the courses they are doing this year.

The subform has a field called ?courseid?.

I can record the courses they are doing using the main form.

I can also record the course(s) that students are doing through another form called ?courses & participants form? which has a couple of subforms, one of which (called ?Q vacancy answer form?) calculates the number of vacancies in a course from the maximum set and the number of enrolments in that class. This form works fine.

Although I  can enrol students in a class through the ?student & courses specific form? form this doesn?t show the number of places left in a class. I don?t want to accept students in a class that is full.

I tried attaching the form called ?Q vacancy answer form? form as a subform on the ?student & courses specific form? form but Access won?t allow a subform on another form where that form (ie ?student & course subform?) is displayed as a datasheet. I do need it shown as a datasheet.

How do I get the ?student & courses specific form? to show me how many vacancies are in a class before I enrol a student?


Question by:mof1
LVL 57
ID: 6442479
You can use DCount with a criteria on the class identifier to get a count of those already in the class.  You can bind a control to this for display on a form or update it as need be.

See the on-line help for DCount()


Accepted Solution

Volibrawl earned 200 total points
ID: 6444541
You will need to figure out a way to get the "vacancy" data into the underlying query so that you can display it in your datasheet.  Since we don't know how/where/what the calculation you are using to determine the "maximum allowed" and what exactly is the query (presently) that feeds your datasheet, we can't be precise.

Author Comment

ID: 6449219
Thanks Volibrawl,

The query that runs the vacancy answer form is "Q vacancy answer" with fields
"vacancies" - (classmax-sum[totup])

This query is based on another query called "Q vacancies on courses" that creates the "totup" field which is a count of "studentid".


Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


Expert Comment

ID: 6449297
It would appear that you could just add the "q Vacancy Answer" query in the source query for your subform.  Relate the queries on the course ID, and include "vacancies" field.  You should then be able to have the vacancies on your datasheeet as well as the other data.  Hope this helps, I'm leaving for a few days so I can't "follow up" but surely someone will if you have troubles.
LVL 54

Expert Comment

ID: 6989129
for mof1

It's time to clean up this TA, so I will leave a recommendation in Community Support that this question is:
 - Answered by: Volibrawl  
Please leave any comments here within the
next seven days.



Expert Comment

ID: 7002800
Per recommendation, force-accepted by
CS Moderator

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now