From that explanation your tables aren't structured properly. Centr limit shouldn't be in that table. If its a limit on the entire table, you dont store the limit amount within every single record in the table. You need a new table to hold that value.
Then to answer JHB's question, this will have to be done with VBA in a form. You would set it up so people input data into an unbound form, then when they click the 'Submit' button your form first validates the data (it doesn't exceed the limit) then if its under limit INSERTS the data into your table, if its over, you show a message box saying so.
Again, a new table to hold the limit, then start reading up on how to build forms.
From that explanation your tables aren't structured properly. Centr limit shouldn't be in that table. If its a limit on the entire table, you dont store the limit amount within every single record in the table. You need a new table to hold that value.
Then to answer JHB's question, this will have to be done with VBA in a form. You would set it up so people input data into an unbound form, then when they click the 'Submit' button your form first validates the data (it doesn't exceed the limit) then if its under limit INSERTS the data into your table, if its over, you show a message box saying so.
Again, a new table to hold the limit, then start reading up on how to build forms.
Ok. Can't I apply the validation to the table instead of the form. I made a form for another table before so I have to learn VBA next.
What about a macro? I tried this in a macro:
Code:
if
Count([Universities]![Country] & [Universities]![Centralized]=True & [Universities]![Apply?]=True)<[Universities]![Cent Limit]
Then
SetValue
Item = [Universities]![Apply?]
Expression = False
When I run it, I get an error saying "Microsoft Access cannot find the name 'Universities' you entered in the expression," even though i entered it from the expression builder.
Also, I learned about data macros and tried to use it on my table but there was no Count() function.
No. Such a thing exists in other databases (triggers), but not in Access. You have to enforce this type of validation with a form. I'm sure you can create validation code in a macro that you attach to a form. But my advice is to do it with VBA. Macros are VBA lite, and your issue is sort of complex so you might as well go the extra eighth of a mile and just learn VBA instead of trying it via a macro.
No. Such a thing exists in other databases (triggers), but not in Access. You have to enforce this type of validation with a form. I'm sure you can create validation code in a macro that you attach to a form. But my advice is to do it with VBA. Macros are VBA lite, and your issue is sort of complex so you might as well go the extra eighth of a mile and just learn VBA instead of trying it via a macro.
Thanks for the info. I am now attempting to write the code. This is what I have so far:
Code:
Option Compare Database
Option Explicit
Sub MyCodeRoutine()
Dim count As Integer
count = DCount("*", "Universities", "Centralized = True And Apply = True")
MsgBox count
End Sub
I had to change the Apply? field to Apply because a '?' caused an error. What I need to do next is find a way to add the country condition. I need the records to have the same country field value in order to be counted. Can this be done by just adding an additional criteria in Dcount?
I am trying, instead, to construct a For statement but I'm struggling with the syntax. How to say:
for each country value in the country field (it is a combo box),
count = DCount("*", "Universities", "Country = the country value And Centralized = True And Apply = True")
Btw, I also intend to change my database design as you suggested before but I want to get this validation functionality to work first.
Yes. You already have 2 criteria, just keep adding ANDs to it to add more.
I don't understand what you are trying to do with your combo box. Instead of a looping DCount, you should use a query. Why are you looping through everything in the combo box? You should only be concerned with the one selected.
Yes. You already have 2 criteria, just keep adding ANDs to it to add more.
I don't understand what you are trying to do with your combo box. Instead of a looping DCount, you should use a query. Why are you looping through everything in the combo box? You should only be concerned with the one selected.
I wanted to make sure that each country available does not violate the rule ( number of records where both Centralized = True And Apply = True). I'm not sure who to do this by adding another AND.
If I can find out which record is modified in the table, then I can get the Country value (with Dlookup()) and assign it to a variable. Is this what you meant? Maybe this is where form events comes in.
I want the rules to hold if the records are modified in the table. I need to see all the data before setting the Apply for any record. The form should not have Apply on it.
You can implement the constraint at table level using a CHECK constraint. Something like the following will work:
Code:
ALTER TABLE tblData ADD
CONSTRAINT country_limit
CHECK (
NOT EXISTS
(
SELECT tblData.Country, Count(tblData.ID) AS CountOfID
FROM tblData
WHERE ((tblData.Centr)=True) AND (tblData.Apply)=True
GROUP BY tblData.Country
HAVING Count(tblData.ID)>=5
)
);
You can change the 5 to a lookup value (subquery or D function I guess).
There are a couple of notes worth mentioning:
You won't be able to see the CHECK constraint once you have implemented. So you will only know it is there by virtue of the fact you implemented it. And of course the fact that it does constrain.
You can DROP the constraint if you want to get rid of it
You need to set your database SQL mode to ANSI 92 when you create the database - at least for later versions of Access
Like other table level constraints, the error message is not terribly user friendly - something like "one or more rules are violated by the validation rule country_limit...". So even though you enter the table level constraint, you still might want to implement some code to make a nice front end experience for the user.
If this is a mission critical database where there are multiple people designing front ends then implementing the constraint at table level is a must. But if this is your first database then I would focus on doing this via the form (recognising that if someone gets direct access to the table then they can bypass the constraint).
You can implement the constraint at table level using a CHECK constraint. Something like the following will work:
Code:
ALTER TABLE tblData ADD
CONSTRAINT country_limit
CHECK (
NOT EXISTS
(
SELECT tblData.Country, Count(tblData.ID) AS CountOfID
FROM tblData
WHERE ((tblData.Centr)=True) AND (tblData.Apply)=True
GROUP BY tblData.Country
HAVING Count(tblData.ID)>=5
)
);
You can change the 5 to a lookup value (subquery or D function I guess).
There are a couple of notes worth mentioning:
You won't be able to see the CHECK constraint once you have implemented. So you will only know it is there by virtue of the fact you implemented it. And of course the fact that it does constrain.
You can DROP the constraint if you want to get rid of it
You need to set your database SQL mode to ANSI 92 when you create the database - at least for later versions of Access
Like other table level constraints, the error message is not terribly user friendly - something like "one or more rules are violated by the validation rule country_limit...". So even though you enter the table level constraint, you still might want to implement some code to make a nice front end experience for the user.
If this is a mission critical database where there are multiple people designing front ends then implementing the constraint at table level is a must. But if this is your first database then I would focus on doing this via the form (recognising that if someone gets direct access to the table then they can bypass the constraint).
No. Just open a new query and then switch to SQL view. Copy and paste into the SQL window. Save and run.
Obviously you need to change the code to reflect the name of the table you are using.
But as I said, if you are new to database design you maybe go down the form route. You may want to fully test this before being sure that this is for you.
Also research ANSI 92 in Access because it changes what is allowed in SQL syntax.
No. Just open a new query and then switch to SQL view. Copy and paste into the SQL window. Save and run.
Obviously you need to change the code to reflect the name of the table you are using.
But as I said, if you are new to database design you maybe go down the form route. You may want to fully test this before being sure that this is for you.
Also research ANSI 92 in Access because it changes what is allowed in SQL syntax.
ALTER TABLE Universities ADD
CONSTRAINT Cent_limit
CHECK (
NOT EXISTS
(
SELECT Universities.Country, Count(Universities.ID) AS CountOfID
FROM Universities
WHERE ((Universities.Centralized)=True) AND (Universities.Apply)=True
GROUP BY Universities.Country
HAVING Count(Universities.ID)>=5
)
);
But it is giving me this error:
Code:
Syntax error in CHECK CONSTRAINT clause. (Error 3765)
This error occurs when using SQL DLL to define a CHECK constraint. It occurs when the search condition (SELECT statement) in the CHECK clause references an invalid table or column name.
My table name is Universities and that is what I used in the code.I'm not sure about Universities.ID. I don't have a field called ID.
ID was just an example primary key field. You could try:
Code:
ALTER TABLE Universities ADD
CONSTRAINT Cent_limit
CHECK (
NOT EXISTS
(
SELECT Universities.Country, Count(Universities.[COLOR="Red"]Country) AS CountOfCountry [/COLOR]
FROM Universities
WHERE ((Universities.Centralized)=True) AND (Universities.Apply)=True
GROUP BY Universities.Country
HAVING Count(Universities.[COLOR="Red"]Country[/COLOR])>=5
)
);
ID was just an example primary key field. You could try:
Code:
ALTER TABLE Universities ADD
CONSTRAINT Cent_limit
CHECK (
NOT EXISTS
(
SELECT Universities.Country, Count(Universities.[COLOR=Red]Country) AS CountOfCountry [/COLOR]
FROM Universities
WHERE ((Universities.Centralized)=True) AND (Universities.Apply)=True
GROUP BY Universities.Country
HAVING Count(Universities.[COLOR=Red]Country[/COLOR])>=5
)
);
Ok, now it ran. But when I go to my table, it doesn't let me make changes or save it. Gives me an error every time. I tried deleting the query but that didn't stop it.
Now I need to replace the 5 with the cntr_limit field value stored in another table (called Constants) that corresponds to the same country. Should I use something like:
So now your constraint needs to look something like this:
Code:
ALTER TABLE Universities ADD
CONSTRAINT Cent_limit
CHECK (
NOT EXISTS
(
SELECT Universities.Country, Count(Universities.Country) AS CountOfCountry
FROM Universities
WHERE ((Universities.Centralized)=True) AND (Universities.Apply)=True
GROUP BY Universities.Country
HAVING Count(Universities.Country)>=
(
SELECT max(Constants.cntr_limit)
FROM Constants
WHERE Constants.Country=Universities.Country;
)
)
);
You will need the DROP the constraint then ADD it again.
So now your constraint needs to look something like this:
Code:
ALTER TABLE Universities ADD
CONSTRAINT Cent_limit
CHECK (
NOT EXISTS
(
SELECT Universities.Country, Count(Universities.Country) AS CountOfCountry
FROM Universities
WHERE ((Universities.Centralized)=True) AND (Universities.Apply)=True
GROUP BY Universities.Country
HAVING Count(Universities.Country)>=
(
SELECT max(Constants.cntr_limit)
FROM Constants
WHERE Constants.Country=Universities.Country;
)
)
);
You will need the DROP the constraint then ADD it again.
