Validation from another table

[kriscs]

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

 

[ByteMyzer]

To answer the specific question you asked, yes, it is possible. To provide specifics, however, might be in conflict with your teacher's stipulation for you to complete this project on your own for a legitimately earned grade.

 

[kriscs]

To answer the specific question you asked, yes, it is possible.

Thank you, I appreciate your concern. It's great to know that it is possible.

To provide specifics, however, might be in conflict with your teacher's stipulation for you to complete this project on your own for a legitimately earned grade

However, that is not the case at all. The only reason my teacher is not telling me himself is because, in all honesty, he does not have a clue how to do it himself, as he has told me. Stupidly, I decided that this would be one of my requirements in my project without thinking how difficult it would be to implement and now, unfortunately, it is too late to change it.
Others in the class have set much simpiler tasks for themselves that I in fact could accomplish on my own and would still gain the same number of marks on completion. I thought however that I would go for something more... complicated.
So any specifics would be gratefully recieved if anyone is willing.
Again, thank you for your time.
Kris

 

[pbaldy]

As ByteMyzer has said, it's possible and fairly simple. You can use a DLookup to find the number of positions available for the selected competition, and compare that to what was entered. A recordset would be slightly more efficient, but the DLookup is easier for some people to understand.

 

[kriscs]

Hi, thanks for the reply. On trying Dlookup I get the error:
"unknown function 'Dlookup'"
I'm using Microsoft Access 2002.

<=DLookUp("[Places Available]","Competitions","[Compeition Number] =[Competition Number]")

Any ideas please?

 

[pbaldy]

DLookup is certainly available in 2002. I note a structure problem and what looks like a spelling problem. More info on structure here:

http://www.mvps.org/access/general/gen0018.htm

Where exactly are you trying to use it?

 

[kriscs]

Ok
3 tables:
tbl_Members
tbl_Competitions (Contains fields 'Competition Number' and 'Places Available')
tbl_Competitors (Contains fields 'Competition Number' and 'Position')

I put the Dlookup function in the validation rule textbox for the field 'Position' in tbl_Competitors.

I'll try this:

<=DLookUp("Places Available","tbl_Competitions","[Competition Number]=[Competition Number]")

at school tomorrow but I have no clue why it said unknown function.

 

[pbaldy]

In a brief test, the DLookup can be used in the validation rule of a textbox on a form or obviously in code, but I did get that same error when trying to make it the validation rule directly in the table.

 

[kriscs]

Ah ok thanks.
When typing
<=DLookUp("Places Available","tbl_Competitions","[Competition Number]=[Competition Number]")

into the validation rule of the text box in the form and typing something into the textbox I get the following message:

There is a 'Syntax error (missing operator) in query expression 'Places Available' in the form control's ValidationRule property.

Sorry, I must seem thick but we've really had no teaching of Microsoft Access. I really do appreciate the help and am keen to learn more.

 

[pbaldy]

Because you've got a space in there (bad idea), you'll need to bracket it:

..."[Places Available]"...