?
Solved

Count records in subform

Posted on 2003-11-13
3
Medium Priority
?
411 Views
Last Modified: 2010-05-18
I have a form that contains a textbox called "RejectNum". This form is bound to a table called Reject Main.  I have a subform bound to this form called RejectSub, that is used to enter multiple rejects and their various criteria.  The datasource for the subform is a table called RejectSub, and there is a one to many relationship between the two tables based on a key called UID. (probably useless info, but maybe worth adding).

Each reject has a serial number, and it is entered in a textbox called "SerialNum".  I would like to force a piece of logic to prompt a msgbox when the number of rejects entered on the main form ("RejectNum") does not equal the count of serial numbers entered on the subform.  In other words, if the number in the RejectNum box is 20, users should have to enter 20 serial numbers in the subform, and until they do so, they should not be able to add a record.  Can someone help me with the logic here?

Thanks in advance for your help.  Please let me know if the explanation has been confusing or otherwise inadequate.

IPT
0
Comment
Question by:iptrader
[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
3 Comments
 
LVL 7

Accepted Solution

by:
ala_frosty earned 2000 total points
ID: 9776732
I don't think this is the best way to go about getting what you want.

Here's what I would do:

* Delete the field in the RejectMain that has the count of the records in RejectSub. It's redundant.

* in the textbox RejectNum, change the source to
= [sfmRejectSub].form.recordcount

* If you want, before saving the record, prompt the user to verify that s/he there were [sfmRejectSub].form.recordcount Rejects. Users tend to quickly tire of this sort of relentless asking for verification.

* go into the Database Relationships, and set up a cascaded enforced relationship between tblRejectMain and tblRejectSub

The advantage to doing it this way is that in the event of something crashing Access (and who ever heard of that) you have the data you entered in both the Main and the Sub.

There is only so much you can do to prevent the users from making mistakes. Yes, don't make it easy, but the way you were suggesting going about it seems painful to me.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 10025714
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: ala_frosty {http:#9776732}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

stevbe
EE Cleanup Volunteer
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

777 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