[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

Connect a subform to a datasheet subform

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
mof1
Asked:
mof1
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
VolibrawlCommented:
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
 
mof1Author Commented:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
VolibrawlCommented:
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
 
nico5038Commented:
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
 
NetminderCommented:
Per recommendation, force-accepted by
Netminder
CS Moderator
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now