"too many records selected"

[SueBK]

We have a table with fields (eg) "project id" and "issue" and "report". The table has several dozen projects, all with a number of issues. The reporting rules are that the top three issues are reported each month, but there needs to be flexibility to allow more issues to be reported if necessary.

The form for user entry is filtered against a specific project, so a user only sees information about one project at a time.

We would like a warning message box to pop up when a user selects the fourth issue that says something like "you have selected more than 3 issues. do you wish to proceed?"

We need the code therefore to say something like "if filtered 'project id' has more than 3 'not null' values in 'report' show message box. if 'yes' allow selection, if 'no' delete field selection". Assuming it can be done how would that be written?

 

[HiTechCoach]

On first read of your post, I got the feeling this was a table design issue. I still think that is probably true.

After reading your post a few more time, I am unclear on ...

... has more than 3 'not null' values in 'report' ...

How can a single field have three values?

Are you using a multi-value field in Access 2007?

Or do you have multiple "report" fields in a single record? If yes, then you probably have a data normalization issues.

What about "issues"? Is it the same way?

 

[SueBK]

Okay; thought it might be clear as mud.

I have a table with say 4 projects (field: ProjectID - 1, 2, 3, 4). I have a 2nd table "issues" that shows that ProjectID 1 has 6 issues (issue 1, 2, etc); Project ID 2 has 3 issues; etc etc.

In the issues table there is field (yes/no) that the user ticks to say whether they want to include an issue in their monthly report. They can put as many issues as they like into the DB, but the "strict" rules say they can only include 3 issues each month in their report. Reality says sometimes there may be more that really really should be included in the report.

We want the user to be able to include as many issues as they like in their report, but we want to warn them they've included more than the required 3.

We've worked out how to set the error message using DCount to warn if more than 3 records are selected BUT not how to filter it against project ID. At the moment, if more than 3 records in the entire issues table are selected it fires the message. What we want is for it only fire if more than 3 for any individual project are selected.

SO - if a user is working "Project 1", which has 6 issues, and ticks four - he gets a warning message.

Later if he's working on "Project 2", and ticks 3 issues, he shouldn't get a warning message. BUT at the moment he does, 'cause the count code is saying "You've selected four agianst Project 1 and now another 3." We want it to say "You've selected "x#" records against this project id therefore warning/no warning."

Any clearer?

 

[HiTechCoach]

A little better.

I still do not understand the table structure.

 

[jayanth1808]

http://www.access-programmers.co.uk/forums/forumdisplay.php?f=9

 

[SueBK]

Table structure (the basics):

Eg: table 1 (projectid, project number, project name)
1...32...Smith Smith Road Centre
2...54...Alpha Alpha Centre

Eg: table 2
(issue id, projectid, issue, report yes/no)
1...1...Roof leaking...............yes
2...1...Floors need polishing....no
3...1...Accident by shopper....yes
1...2...Lease due..................yes

17/6/09
I tried the link - it just took me a list of posts on the forum, rather than specific post.

Still grappling with this one.

The deal is: project manager opens his project (ie the form is filtered against his project), he looks at the list of issues for his project and he choose three to report this month. (ie the database holds all the issues, but only the really important ones need to be included in the printed report that goes to management each month).

Sometimes, however, he may think that four are sufficient important to report. We want for him to be able to tick as many he likes, but for a warning message to happen if/when he ticks number 4, 5, 6 etc.

Code we're trying to use at the moment is:

[COLOR=#333333][FONT=Trebuchet MS]Private Sub Reporting_Click()[/FONT][/COLOR]
[COLOR=#333333][FONT=Trebuchet MS]If DCount("[IssueID]", [tblIssuesRegister], Me.Filter & " AND [Reporting] = TRUE") > 3 Then MsgBox ("help")[/FONT][/COLOR]
[COLOR=#333333][FONT=Trebuchet MS]End Sub[/FONT][/COLOR]

But there seems to be an issue with the syntax.

 

[SueBK]

Bump. Still need input.