By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,804 Members | 1,681 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,804 IT Pros & Developers. It's quick & easy.

list box validation

P: n/a
Hi

I am creating a relational database for school work and have got stuck on a
simple problem

i have a table called organisation and one of its fields is organsiation id
(primary key)

in another table, delegates, i have organisation id again, and have a list box
so the user can only select organisation id's which already exist.

the problem is i dont know how to create the validation rule that goes along
with this, so the user can only select those existing organisation id's

thanx in advance for any help, donovan
Nov 12 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
"DangerD321" <da********@aol.com> wrote in message
news:20***************************@mb-m16.aol.com...
Hi

I am creating a relational database for school work and have got stuck on a simple problem

i have a table called organisation and one of its fields is organsiation id (primary key)

in another table, delegates, i have organisation id again, and have a list box so the user can only select organisation id's which already exist.

the problem is i dont know how to create the validation rule that goes along with this, so the user can only select those existing organisation id's

Do you think that, without validation rules, users might inadvertantly
select non-existant organisation id's?
One wonders how.

Nov 12 '05 #2

P: n/a
well the thing is they could select one from the menu and accidently hit and
extra key or change the numbers quite easily and it not be correct, but still
work...

my work says i need to ensure the delegate is connected to an existing
organisation, making the validation rules necessary, so please do not talk down
to me just becuase i am in school.

i would like to know how to do this anyway because i need it for another
problem

i hope 'one' can understand my predicament.....
Nov 12 '05 #3

P: n/a
As Tom indicates elsewhere in this thread, using a list box the user can
only select what is already in the list, so no validation is required. You
should, of course, also enforce referential integrity on the relationship
between the tables.
--
Brendan Reynolds

"DangerD321" <da********@aol.com> wrote in message
news:20***************************@mb-m16.aol.com...
Hi

I am creating a relational database for school work and have got stuck on a simple problem

i have a table called organisation and one of its fields is organsiation id (primary key)

in another table, delegates, i have organisation id again, and have a list box so the user can only select organisation id's which already exist.

the problem is i dont know how to create the validation rule that goes along with this, so the user can only select those existing organisation id's

thanx in advance for any help, donovan

Nov 12 '05 #4

P: n/a
but if the user types in instead of using the list they can type things that
aren't specified in the pull down list, which is why i need validation

thanx for your help, donovan
Nov 12 '05 #5

P: n/a
"DangerD321" <da********@aol.com> wrote in message
news:20***************************@mb-m16.aol.com...
well the thing is they could select one from the menu and accidently hit and extra key or change the numbers quite easily and it not be correct, but still work...

my work says i need to ensure the delegate is connected to an existing
organisation, making the validation rules necessary, so please do not talk down to me just becuase i am in school.

i would like to know how to do this anyway because i need it for another
problem

i hope 'one' can understand my predicament.....

The response was not an attempt to condescend, but the question is genuinely
a bit puzzling and there seem to be a couple of issues going on. The basis
of the database design is the tables and I can see you have at least 2.
These should be similar to:

tblOrgs:
OrgID = Autonumber & primary key
OrgName = Text
etc

tblDelegates:
DelID = Autonumber & primary key
DelOrgID = Long integer. This is the ID of the Org to which the delegate
belongs
DelFirstName
DelLastName
etc

You should enforce referential integrity so that DelOrgID must exist in the
tblOrgs. But at this point you have some design decisions to make. Is
DelOrgID required? That is, can you have a delegate who does not belong to
any organisation, or does your data model say that each delegate must belong
to one? Then what about cascading deletes, for example? Would you want to
automatically delete all related delegates if you deleted the org, or should
org deletion be impossible until all related delegates have first been
deleted?
Once these things have been sorted, we come back to the listbox. If you
have said that not all delegates belong to orgs and you only want to be able
to select those which do, then this is simple. You base you listbox on a
query with an inner join

SELECT * FROM tblOrgs INNER JOIN tblDelegates ON tblOrgs.OrgID =
tblDelegates.DelOrgID
Does any of that make sense?
Tom
Nov 12 '05 #6

P: n/a
yeh mostly except some of the last bit...what i have for the list box in the
delegates table is:

SELECT [Organisation].[Organisation ID] FROM Organisation;

because the delegate HAS to be part of an existing organisation. this works
fine, the list box contains only those organisations already created in the
organisation table, but the problem is although the user can choose the values
in the list biox and it work, if they were to type any old ID in it would
accept even though it's not part of the list...like i have another list box
with the days in it, but i still need to do a validation rule with Like "Mon"
Or "Tue" etc...

sorry dont mean to turn u into my teacher but its half term!

oh and sorry i thought you were being rude to me earlier!

Thanx, Donovan
Nov 12 '05 #7

P: n/a
"DangerD321" <da********@aol.com> wrote in message
news:20***************************@mb-m16.aol.com...
yeh mostly except some of the last bit...what i have for the list box in the delegates table is:

SELECT [Organisation].[Organisation ID] FROM Organisation;

because the delegate HAS to be part of an existing organisation. this works fine, the list box contains only those organisations already created in the organisation table, but the problem is although the user can choose the values in the list biox and it work, if they were to type any old ID in it would
accept even though it's not part of the list...like i have another list box with the days in it, but i still need to do a validation rule with Like "Mon" Or "Tue" etc...

sorry dont mean to turn u into my teacher but its half term!

oh and sorry i thought you were being rude to me earlier!

Thanx, Donovan

If that is your actual e-mail address, I'll e-mail you a sample. Give me 10
minutes.
Nov 12 '05 #8

P: n/a
A user can not type into an Access list box. Perhaps you're not using a list
box, but a combo box? A user can type into a combo box, but if the Limit to
List property is set to 'Yes' it will still reject any value that is not in
the list. You still don't need any validation to prevent users entering a
value that is not in the list - though you may want to use the NotInList
event procedure to display a more user-friendly message when a value is
rejected.

If you *did* need to validate that a user-entered value existed in a table,
you would query the table for that value. In the BeforeUpdate event
procedure of the control ...

Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim boolFound As Boolean

'Assumes a numeric value. Delimit date values with # and string values with
quotes.
strSQL = "SELECT Count(*) AS TheCount FROM TheTable WHERE TheField = " &
Me!TheControl
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
boolFound = rst.Fields("TheCount") <> 0
rst.Close
If Not boolFound Then
MsgBox "No such record"
Cancel = True
End If

--
Brendan Reynolds

"DangerD321" <da********@aol.com> wrote in message
news:20***************************@mb-m16.aol.com...
but if the user types in instead of using the list they can type things that aren't specified in the pull down list, which is why i need validation

thanx for your help, donovan

Nov 12 '05 #9

P: n/a
Thanx a lot, thats exactly what i needed. I did mean combo box, i was just
lookin at the the table design under lookup where it says 'list box'
Nov 12 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.