Stupid Access Form Question

uean

I have a pretty basic Access database here. Just houses students, some financial records for each students, and basic info on everything.

Right now I use a form to navigate through all the students and show the financial records, educational history, etc for each student. What I want to do is somehow "graduate" a student out of the databases main records, but still keep the information. Essentially, if there were three students in the database, and I scrolled through the records I could see three. Then, upon graduation, I want to only see the two current students, but still have the graduated student in the database.

I'm thinking there has to be an easy way to do this. Like have a field like ISCURRENT that is either yes or no, and the form only displays students with a YES status. Then, if I want to view graduates, I can somehow switch the form to scroll through those with a NO status.

I'm not too experienced with this as I am self taught with access. Can anyone help?

cmsamo

What version of Access are you using? 2003/2007?

1. Create a new field in your students table - call it "status"
2. Make it a "lookup" field, and add two values to the lookup "attending" / "graduated"
3. Create a query, call it "attending", and select all students from your student table where "Status = attending"
4. Populate the form's contents (via data sources option) to the "attending" query

viola - your form will show only students that are attending.

Copy the form - call it "Graduated Form", write a query called "Graduated_Query", and then set the forms data source to that query, and you have a form for your graduated students as well.

The above is a simple way to do it. You could also put a combo box on your form, and use it as a parameter to a smart query which changes based on what student status you want to see in your form. If you want help, send me a PM and I'll try and get back to you (I'm at work atm!) - I've got a sample project I've just thrown together in 2003 that does what you want.

uean

cmsamo wrote: »

What version of Access are you using? 2003/2007?

1. Create a new field in your students table - call it "status"
2. Make it a "lookup" field, and add two values to the lookup "attending" / "graduated"
3. Create a query, call it "attending", and select all students from your student table where "Status = attending"
4. Populate the form's contents (via data sources option) to the "attending" query

viola - your form will show only students that are attending.

Copy the form - call it "Graduated Form", write a query called "Graduated_Query", and then set the forms data source to that query, and you have a form for your graduated students as well.

The above is a simple way to do it. You could also put a combo box on your form, and use it as a parameter to a smart query which changes based on what student status you want to see in your form. If you want help, send me a PM and I'll try and get back to you (I'm at work atm!) - I've got a sample project I've just thrown together in 2003 that does what you want.

I'm using 2007.

I'll give what you're saying a whirl. I don't mind having two forms, but its a bit of a headache (each form contains quite a few subforms and tabs). This'll probably take me a day because I'll want to learn the ins and outs of it, but I'll report back if I get it working Thanks man

edit - question before I start - if I am populating a form based off of a query, will the form be able to update records in the related tables, or is everything going to be read-only because its pulling from a query and not a table? I guess I'll find this out myself, but like I said I'm new, and was fist-pumping when I actually had a form that could display one student and ten financial records at once (lame, I know) so Im not sure how this is going to work mentally. PM on the way for the database.

cmsamo

if I am populating a form based off of a query, will the form be able to update records in the related tables, or is everything going to be read-only because its pulling from a query and not a table?

The query returns your actual records. They are displayed in the form. Therefore, if you change the details in each form field, the underlying data will change as well.

Try it in the example I sent. Change one of the people's names, and inspect the "table" object to see if the data has changed. It should have.

uean

biiiiiiiiiiiiiiig thanks to cmsamo for helping me out through email and rewriting my database for me! Awesome guy :thumbup: