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

Table Validation Rules Ok ?

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

Validation Rule - Table

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

Table Field Validation

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

Validation In Table Field

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

Access Validation Table

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

Table-Level Validation

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

Data Validation In A Table

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

Tables :: Validation Within A Table?

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

Table Validation Texts Problem

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

Validation Rule On Field In A Table

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

Modules & VBA :: Validation Check Against Another Table

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

Validation Rule In Table For A Field

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

Table Validation Rule Doesn't Work...

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

Validation Rule Based On Data In Table

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

Table Validation Rule - Field Accepts Only A-Z

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

Validation Rules In CREATE TABLE Statement

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

Tables :: Table Validation Rule - IIF Statement

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

Validation Rule By Relating Data With Table

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

Using Table Data Query For Entry Validation Rule

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

Require Field Entry To Be One Of Entries In A Table? Validation?)

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

Queries :: Data Validation Of A Field In Existing Table

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

General :: Multiple Validation Rules In One Column Of Table?

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

Forms :: Text Box Data Validation Against A Field In Another Table?

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

Create A Table In MS Access - Name In First And Validation Dates In Second Column

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

Reports :: Validation - Null Table Field Will Not Include In Report

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







Copyrights 2005-15 www.BigResource.com, All rights reserved