Solved

Connect a subform to a datasheet subform

Posted on 2001-08-30
6
338 Views
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?

Thanks
Michael

0
Comment
Question by:mof1
6 Comments
 
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()

Jim.
0
 
LVL 9

Accepted Solution

by:
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.
0
 

Author Comment

by:mof1
ID: 6449219
Thanks Volibrawl,

The query that runs the vacancy answer form is "Q vacancy answer" with fields
"courseid"
"classmax"
"totup"
"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".

Cheers

Michael
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 9

Expert Comment

by:Volibrawl
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.
0
 
LVL 54

Expert Comment

by:nico5038
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.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Expert Comment

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

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

856 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