Solved

Connect a subform to a datasheet subform

Posted on 2001-08-30
6
339 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

749 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