list box validation

[DangerD321]

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

[Tom Travolta]

Re: list box validation

"DangerD321 " <dangerd321@aol .com> wrote in message
news:2004021808 5223.24788.0000 2126@mb-m16.aol.com...[color=blue]
> Hi
>
> I am creating a relational database for school work and have got stuck on[/color]
a[color=blue]
> simple problem
>
> i have a table called organisation and one of its fields is organsiation[/color]
id[color=blue]
> (primary key)
>
> in another table, delegates, i have organisation id again, and have a list[/color]
box[color=blue]
> 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[/color]
along[color=blue]
> with this, so the user can only select those existing organisation id's[/color]


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

[DangerD321]

Re: list box validation

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.... .

[Brendan Reynolds]

Re: list box validation

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 " <dangerd321@aol .com> wrote in message
news:2004021808 5223.24788.0000 2126@mb-m16.aol.com...[color=blue]
> Hi
>
> I am creating a relational database for school work and have got stuck on[/color]
a[color=blue]
> simple problem
>
> i have a table called organisation and one of its fields is organsiation[/color]
id[color=blue]
> (primary key)
>
> in another table, delegates, i have organisation id again, and have a list[/color]
box[color=blue]
> 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[/color]
along[color=blue]
> with this, so the user can only select those existing organisation id's
>
> thanx in advance for any help, donovan[/color]

[DangerD321]

Re: list box validation

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

[Tom Travolta]

Re: list box validation

"DangerD321 " <dangerd321@aol .com> wrote in message
news:2004021809 4008.24788.0000 2127@mb-m16.aol.com...[color=blue]
> well the thing is they could select one from the menu and accidently hit[/color]
and[color=blue]
> extra key or change the numbers quite easily and it not be correct, but[/color]
still[color=blue]
> 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[/color]
down[color=blue]
> 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.... .[/color]


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.De lOrgID


Does any of that make sense?


Tom

[DangerD321]

Re: list box validation

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

[Tom Travolta]

Re: list box validation

"DangerD321 " <dangerd321@aol .com> wrote in message
news:2004021810 3442.24788.0000 2130@mb-m16.aol.com...[color=blue]
> yeh mostly except some of the last bit...what i have for the list box in[/color]
the[color=blue]
> delegates table is:
>
> SELECT [Organisation].[Organisation ID] FROM Organisation;
>
> because the delegate HAS to be part of an existing organisation. this[/color]
works[color=blue]
> fine, the list box contains only those organisations already created in[/color]
the[color=blue]
> organisation table, but the problem is although the user can choose the[/color]
values[color=blue]
> 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[/color]
box[color=blue]
> with the days in it, but i still need to do a validation rule with Like[/color]
"Mon"[color=blue]
> 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[/color]


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

[Brendan Reynolds]

Re: list box validation

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.OpenRecordse t(strSQL)
boolFound = rst.Fields("The Count") <> 0
rst.Close
If Not boolFound Then
MsgBox "No such record"
Cancel = True
End If

--
Brendan Reynolds

"DangerD321 " <dangerd321@aol .com> wrote in message
news:2004021810 0418.24788.0000 2129@mb-m16.aol.com...[color=blue]
> but if the user types in instead of using the list they can type things[/color]
that[color=blue]
> aren't specified in the pull down list, which is why i need validation
>
> thanx for your help, donovan[/color]

[DangerD321]

Re: list box validation

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'