?
Solved

Connect a subform to a datasheet subform

Posted on 2001-08-30
6
Medium Priority
?
341 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 58
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 800 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

762 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