Table Entries (Access 2000)

[Neumann1]

Hello
We set up a form in Front Page which the students will fill out for career day. We will have 6 different sessions in which different classes will go to the web page to enter their choices. This information will then filter back to an Access database. What I need help with is, for example, for the career of Doctor, we only want 20 students to be able choose it per session. Then, for the 21st student, we want a message to pop up saying something like "You must choose another career. The limit for this choice have been reached". My question is, how do I set up the limit for each choice the students will choose?
Any help you may give me will be greatly appreciated.

Thanks
Wendy

[GARYPSWANSON]

You said <This information will then filter back to an Access database.>

How will the data be stored in Access. Do you plan on a table storing the persons name and their selection (i.e, Lastname, First Name, Career) or some other method?

[Neumann1]

Yes, the information will be stored in a table. A field for the Lastname, FirstName, which session they are in and one for Career. I don't know if it will matter but fields Session and Career will be drop down fields where they will select which session they are in and which career they would like to see.

thanks
Wendy

[GARYPSWANSON]

One last question, how is the data getting into the MS Access table?

[Neumann1]

Hi Gary
The form was created in Front Page and the data will go directly into an Access table when the student fills out the information on the form on the school's website.

Thanks
Wendy

[GARYPSWANSON]

As I am not a Front Page user, I can only tell you what I would do in Access.

In access, if you set up a form to collect the data (I would suggest using Pulldown Menu's) after all of the data is entered, you could press a command button to update the data tables. The first step would be to run a count on the combination of the value of Session and Career to see if thecount exceeds 20. If so, the routine would provide an error message that the maximum number of Careers for this session has been exceeded, please select another career. If not, the table could be updated.

I don't see why you couldn't do this in Front Page but I am unsure if Front Page can do this.

Let me know if you need additional details.

[Neumann1]

Hi Gary

Do you have an easy way for me to add this to my form? I am also not a Front Page user so do you think Front Page would look for the form instead of the table in Access?

Thanks
Wendy

[GARYPSWANSON]

I am sure that front page has a validation area that may be able to be linked to the access table. However, will need to re-post the question as I can't get the validation on the table to work when the number of careers within a session exceeds 20.

[GARYPSWANSON]

Wendy,

I know what to do, however, can't get it to work properly. Please re-post your question to start a new thread and see what results you get. I will be watching as well.

[Neumann1]

Gary

I will repost under Access. I have it posted under Front Page already.

Thanks for the help!
Wendy

[GARYPSWANSON]

I was watching the Front Page thread as well. I was pretty close to solving your problem but ran into some syntax problems. There is another post in Acces that is similar to the first post you did.

Ultimately, I set up a form that used combo boxes to input values directly into a table. The combo boxes are last name, first name, session and career. On the change event property on the career, I launched some code to calculate the number of unique values for the session and career for the current data being entered (this is where I had a syntax problem). I could generate a separate query properly using Count(*) that calculated correctly, but could not get the Dcount function to work. The calculated value of the session and career was then added to the validation on career and would not allow the data to be entered if the total exceeded 20. I will be watching to see what happens as well.

[charlotte]

No, please DON'T post another thread on the same question! We actively discourage posts of the same question over and over. You don't get any better answers that way than if you post an additional clarification as a response to your original post.