Validation From Another Table
Oct 2, 2006
Hello,
I'm doing a project for school.
Currently I have a database containing 3 tables. One table contains a list of people. Another table contains a list of competitions and the number of positions available in that competition. The last table contains competitors and the ability to select what position they came in a specific competition.
I would like to know if it is possible, once the user and selected a person and competition for a record, for the validation to check the number of places available in that specific competition they played in and to not allow the user to enter a position that is greater than the number of places available.
I don't have much hope on this being possible but I would love any help.
Thanks very much
Kris
View Replies
ADVERTISEMENT
Oct 25, 2005
Question I am looking at some annoying Access database for a charity and got stuck on trying to make something work that they believe used to
Two Tables
Venues
pK Venue ID
Venue Name
Venue Capacity
Booking
Venue
Name
Addr1
Addr2
Addr3
Ect
They have a query call capacity check which does the following
Count Occurrences of Venues then takes this away from venue capacity
Essentially they have a standard access form of the Booking table with a drop down box and want it to only validate if seats are still free at a venue eg if the current count for venue is less than the venue capacity.
I would love to perform this validation rule on the table
Any ideas ?
View 1 Replies
View Related
Jan 29, 2005
I have field (SupplierID) set as Text and the field limited to 5.
I want the users to enter the first three digits as text e.g "ABC" and the last 2 digits as numerics e.g "01", thus the input would be "ABC01", I can validate it to 5 digits but cannot get the validation working on the numerics, e.g users can enter "ABCDE", Am i correct in thinking its because my field is set as text ?, is there a simple way to do this, thankyou.
View 2 Replies
View Related
Apr 20, 2005
hi,
instead of providing validation at the client side or in the server script....
cant i provide validation in the tables fields...
I have a field named YearID, datatype text..
But I want to allow only text like this ==>
e.g. 'F-00'
'F-01'
Do I have to put any format property...
please do help...
thank u.
View 1 Replies
View Related
Aug 12, 2005
Table field (primary key) data type:Number
How can I validate / symbol as in 05/01 representing year and 1st record, etc.
Many thanks in anticipation.
View 2 Replies
View Related
Feb 2, 2006
I have an Orders table which needs to be able to calculate if the subtotal is less than £10.00 then enter £3.00 otherwise, enter £0.00. I have tried...
=IF [Subtotal_2] <£10.00 THEN £3.00 IF [Subtotal_2] >£10.00 THEN £0.00
Why won't it work!? :confused: :eek:
Help me please!
View 3 Replies
View Related
Jun 12, 2006
I have 2 tables: Components and Lot Codes
In the Components table I have the following fields:
ModelType
Component
In the Lot Codes table I have the following fields:
Serial Number
Model
Component
LotCode Number
I want to restrict the data in the Lot Code Table so that when a model number is entered the only possible values for the Component field is one where ModelType and Component match in the Component Table.
This sounds confusing. . .basically selecting one item drills down the options for the next.
Can anyone help?
View 2 Replies
View Related
May 9, 2007
I am trying to validate data in a table.
I have two fields, status and completion date.
What I want is the following:
If [status] field = "Complete" then [completion date] cannot be null.
I don't know if doing this in a table of the form is best using the forms Before Update Event, but I am having trouble figuring how to write it using the expression builder.
Can anyone help or offer suggestions?
View 8 Replies
View Related
Nov 7, 2012
I am trying to validate attendance at an event. If there is a no show indicator set to Yes, then attendance must be zero. If there is a no show indicator set to No, then attendance must be greater than zero. No show indicator is in same table with attendance.
View 14 Replies
View Related
Sep 21, 2005
Hello, I'm new in the Access World. I have a small question, but a bigger problem:
I have a big application developed in Access 97 with more then 200 tables. I wan't to move all the validation rules and texts from back-end to front-end (mean that I wan't to make the validation on the form controls and not directly on the tables like is now). This is needed because in future, I wan't to migrate the application to MSSQL. I wan't to write a piece of code that goes through every form, and for each control in the form, upon the control datasource, get's from database automatically the validation rules and texts and sets them to the control properties.
This is a nice thought, but I'm stuck at the part where I have to get the validation rules and texts for a specific column from a specific table.
Can anyone help me?
Thanks,
Mihai.
View 6 Replies
View Related
Apr 10, 2007
Hi
I have a form which people fill in (made up of fields from Table1) to record when sick days.
On the form, they have to enter the "week commencing" (which is set at Medium Date format); then they have to enter in a first day (which is set at Short Date format).
The First Day should be no more than 7 days from the date that they enter in the Week commencing field on the form.
I want an error message to pop up if they do this, for example:
Week commencing: 1st April 2007
First Day: 10th April 2007
(because the 10th April 2007 is more than 7 days from the 1st April). But it would be ok if they did this:
Week commencing: 1st April 2007
First Day: 8th April 2007
Help???
thanks!
Maria
View 9 Replies
View Related
Jul 26, 2013
I have a lookup which selects a 'Recipient'.
I need to add a check to see if this 'Recipient' (user selected) already exists within another table.
If so, I need a pop-up to appear.
If not, I want the user to continue unimpeded.
View 8 Replies
View Related
Mar 5, 2012
This is pretty simple but I need to place a validation rule in my table for a field so that users can only enter data that starts with APB. What would the rule be?
View 1 Replies
View Related
Jan 28, 2005
Hello, all.
I have a table that contains a field (list) for categories [Inquiry Type], and a field that tracks category of any follow-ups [FU Inquiry Type].
The rule is: If [Inquiry Type]="F", than [FU Inquiry Type] Is not null.
For some reason, when I'm entering data, the validation rule does not take any effect....does not notice when [FU Inquiry Type] - which is also a list box - remains empty when [Inquiry Type] is an F.
I have entered the rule this way in the table property validation rule field:
(([Inquiry Type]="F" AND [FU Inquiry Type] Is Not Null) OR ([FU Inquiry Type] Is Null)
I already have data in this table, but have ensured that it conforms to the above rule. Can anyone help?
View 4 Replies
View Related
Mar 8, 2005
Hi,
Does anyone know if its possible to base a validation rule on the data that is already in the field in a table?
e.g. there are 10 records in a table each with an ID number starting at 1 and going up to 10. When new record added i need a validation rule that will prompt the user to enter a number greater than the previous number entered for the ID.
so they will not be able to enter 1 - 10.
hope thats clear
cheers.
View 1 Replies
View Related
Feb 9, 2006
I am wanting a text field in a table to accept only chars A - Z
In my validation rule I use
Like "a" Or "b" Or "c" Or "d" Or "e" Or "f" Or "g" Or "h" Or "i" Or "j" Or "k" Or "l" Or "m" Or "n" Or "o" Or "p" Or "q" Or "r" Or "s" Or "t" Or "u" Or "v" Or "w" Or "x" Or "y" Or "z"
Which works :D
Then validation text I use
Enter Alphabetical Letters only
Also works :D
Is there any short way to write Like A.....thorugh to Z without the long statement?
View 2 Replies
View Related
Oct 9, 2007
Hi, I'm fairly new to Access and I'm trying to figure out how to create a validation rule via SQL. I searched through the forums but didn't find anything. Hope someone can point me in the right direction. For simplicity say I have the following extremely simple CREATE STATEMENT.
CREATE TABLE GAMES (
Name TEXT PRIMARY KEY NOT NULL,
Amount CURRENCY NOT NULL,
);
How could I rewrite the above query to specify that the only valid amounts allowed are say between 1 and 10?
I'm familiar with the Constraint statement and the Check clause but I can't get these to work in Access.
View 2 Replies
View Related
Feb 27, 2014
I cant figure out a fairly simple IIF statement I need for a table validation rule. I'll try to explain:
I have 2 fields in a table that are controlled through a form, which sets the fields either true (-1) or false (0). Lets call them Field1 and Field2. These fields need a validation rule which is dependent on FieldX and FieldY.
When FieldX = FieldY, I need Field1 to be either 0 or -1
if Field1 is 0 -> Field2 is -1 and the other way around.
if FieldX <> FieldY -> Field1 = 0 and Field2 = 0
View 1 Replies
View Related
Dec 18, 2013
I have tried several ways to validate my data with existing table I've developed.
Actually, I want the access to validate the data (Cellphone number) I've entered in the textbox by looking it in the existing table. So, if the data is there in the table, the message will pop up such as.. "Please continue". But if not, the message should be "this number was not registered yet.." and the textbox will be empty and ready for the new number...
View 14 Replies
View Related
Aug 15, 2007
I'm new to access so I thought this would be easier than it is. I have a list of cities in a simple table . I want use this list (or a query of it) to create a validation rule for a field in a second table "City". How, either with VBA or some other method do I use the Cities database to validate entry. These tables are both in the same project.
Any help is appreciated.
View 2 Replies
View Related
Oct 29, 2004
I am using Access 2002 database with ASP.NET(VB). I usually try to get something working in the access 2002 GUI 1st and I am having trouble with creating the code to require that a field entry be forced to be another exisiting entry in another table. For Example:
UNIT_TABLE has fields UNIT_ID(key), and UNIT_NAME having values of:
1 Tigers
2 Bears
3 Sparrows
BATTLES_TABLE has fields BATTLES_ID(key), ATTACKER_MAIN, and DEFENDER_MAIN
I want the ATTACKER_MAIN and DEFENDER_MAIN choices to ONLY be members from UNIT_TABLE.UNIT_NAME
I set up a combobox and lookup in BATTLES_TABLE as follows:
SELECT UNIT_TABLE.UNIT_NAME FROM UNIT_TABLE ORDER BY UNIT_TABLE.UNIT_NAME;
this OFFERS the CHOICE of any of the 3 clans (ie Tigers, Bears, Sparrows)
but I cannot not FORCE the USE of these....because the end user can option to enter in anything else.(which is not wanted).
How do I add SQL to the LOOKUP such that... the user can ONLY choose to enter text that EXACTLY matches an Already existing entry in the UNIT_NAME field?
I tried to enter a validation rule:
[ATTACKER_MAIN] In ([UNIT_TABLE.UNIT_NAME])
I get an error that the field 'UNIT_TABLE.UNIT_NAME' does not exist.
Thanks for any help with this validation problem.
HKP_MACK
View 3 Replies
View Related
Aug 13, 2015
I'm trying to write a query that will validate a field in an existing table. I want the query to show where any of the 412,000 records fail this strict validation:
AAnnnnnnA
...where A is any letter A-Z and n is a digit 0-9
Clearly I can use the LEN function to check for invalid lengths (anything <>9), but I'm looking for a simple neat expression I can use that will check the format. Does an existing function already exist before I resort to writing my own function in VBA (again)?
View 7 Replies
View Related
Apr 13, 2013
Is there a way to have multiple validation rules in one column in a table. In one column, I need the values to be in all lower case and the first three characters need to be three letters.
View 1 Replies
View Related
Jun 15, 2013
I am working on a form that registers new training attendees. One of the fields in my form asks for dealer number.
I have a separate table where all the dealer information is stored. One of the fields is a Customer Number field
I want to ensure that entries in the Dealer Number textbox on my New Registration form matches a valid Customer Number in the Dealer Database Table
I would like this verification to occur either upon a Click event with a button or on an After Update event.
View 6 Replies
View Related
Feb 24, 2014
How to create a table in MS Office. Current table has name in first column and start & finish dates in other two columns. It is necessary to create a table in MS Access which has name in the first column and validation dates in the second column. For Example:
currently
1. White 16 xxxx19
2. Black 1 xxxx 5
required
1. White 17
1. White 18
2. Black 2
2. Black 3
2. Black 4
View 1 Replies
View Related
Mar 30, 2013
I need to validate if a Field in my table is NULL, details of which will not be included in my report.
I attached sample report wherein the data is sorted by EXPIRATION DATE, first rows displayed the data of NULL EXPIRATION DATE FIELD ...
View 3 Replies
View Related
