View only specific records in form view

[drnld]

Hi - I have attached a student database that I created using the student database template in Access and have added additional tables, queries, etc. to fit our needs. One of the issues I'm having is users who are not very computer literate don't always remember if they have entered the right dates for attendance. I had the idea of creating a "sample" student for each corresponding cycle with 100% attendance so they can compare what they have entered to what they "should" have entered. The reports are based on a query, so I know I can have the sample student show up with the correct information there. However, in the STUDENT DETAILS FORM, there is a pre-made code for the student's names to appear in the drop down and when you forward to the next record.

Can I have that altered to only show the ACTIVE students? This way the termed students would also not be a part of this drop down. I should still be able to go into the table itself and put in the sample student information when I create a new cycle, correct? Is there a better way to do what I'm asking?



Thanks for any help.

[razors1968]

Hi drnld, do you want the change for the drop down to be permanent? The information for the drop down (Combo box) is controlled by the RowSource property. It is easy enough to change this so that the drop down will filter out any students who do not have 'Active' status in the enrolment column?

Let me know if I have got that right and I will explain how to make the changes to the underlying query.

Razors

[drnld]

Actually, yes, that is exactly right. I only want active to show up in that drop down list.

[razors1968]

OK, to change the underlying query.

1. Open the form in design view
2. Select the combo box
3. To the right of the screen you should see the 'Property Sheet', select the 'Data' tab
4. Find 'Row Source' in the left hand column and place the cursor in the right hand column (This begins 'SELECT [ID].....
5 An Elipsis (three dots) button should appear on the far right, when you click this the query builder will appear.
6 You should see 4 fields in the query already {ID], [Student Name],[EMAil Address], [Home Phone]
7 We need to add [Enrollment] as an extra column. Click in the first free column next to [Home Phone]
8 Click the drop down arrow and select 'Enrollment'
9 In the Criteria Row (fifth row down) type "Active"
10 Hit save and close the query builder, you may be asked if you want to update the Row Source Property, answer 'yes'
11. Save the form and then view in form view.

In your sample database none of the students had anything other than and 'active' status so they will all still be returned.

What we have effectively done is applied an additional filter to the underlying dataset for the combo box.

Please let me know how you get on.
Good luck.

Cheers

ray

[razors1968]

MA Day Program Attendance- Audrey - Copy1.zip
I have tried to attach the modified file. I am new to this site so not sure if I have managed to attach or not??

Cheers

Ray

[drnld]

Works AWESOME! Thanks Ray and by the way... your file did indeed attach. Thanks so much for your help!