Displaying all records in multiple items form based on combobox value

[mroberts]

Hello,


I have tried searching for my specific problem but have been unsuccessful. I am doing a basic student attendance form for me and a couple other teachers. I have a multiple items form with an unbound, no luck binding, combo box in the header, which will be the class period attendance is being taken for. All student records are currently being displayed. I was just going to add the periods manually to the combo box. I was then going to somehow return ALL records where Student.Period = the value of the combo box. I want to be able to do attendance for every student in class on one form rather than one at a time. That part is taken care of with my multiple items form. I have tried a macro to filter the records based on combo box value but running into problems there as well. I am asking for the quickest, easiest way to do this. I am inserting a screen shot of what I have.

Thanks so much for any assistance!

Hope it shows up larger.

[June7]

One way to filter form records is with basing the from RecordSource on a parameterized query. It requires the least code. Review: http://datapigtechnologies.com/flash...tomfilter.html

Controls used to input filter criteria should be unbound.

You already have the student attendance records for each day created and just need to mark if not present?

[mroberts]

Thank you for your reply!! I tried using a query as the video suggests but it lists the period of every student record in the combo box. So I may have 5 1's, 4 2's, etc. in the combo box. If I do it this way, I would need somehow to only show unique values in the box. I only want 1, 2, 3, 4, 5 to be in the combo box since those are the periods a student can be in. That is why I was going to just enter those values manually in the properties. When I select one of the 5 period options, I want every record in the student table whose period matches the value in the combo box to show up on the form. Just like in the image I attached except only those students whose period matches the combo box value. Then I can select the attendance status for each of the students for the given period and then submit the attendance. As of now, I am using an append query into a student attendance table with the student id, date, and attendance status (present, absent, etc.) when I hit the submit attendance button. Attendance will be taken every period so I need to submit attendance for those students that are in the period selected. I did the attendance portion first and that is working fine. Thanks for any suggestions.

[June7]

Try using DISTINCT in the RowSource query:

SELECT DISTINCT period FROM tablename;

[mroberts]

I tried the distinct command but no luck. I actually got it to work by hard coding the periods in the combo box. The issue I am having now is that when the student records are displayed for the selected period and I click the submit attendance button to run the append query, it states that 20 records are being appended. 20 is the total number of test records I have while only a subset of that number is being displayed on the form. That was great when I first set it up but now that I am limiting the number of student records displayed based on period selected, I only want to append the number of records displayed for selected period. I imagine it would have something to do with getting the subset of records into memory before being appended but that is only a guess on my part. If I should post this elsewhere, please let me know. Thanks again for your time.

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[mroberts]

I deleted the records in the student attendance table so you can run it from scratch. Just a reminder of the issue I would like to resolve. This pertains to the "Go To Daily Attendance" option on the splash screen. When the period is selected from the combo box and the subset of student records are returned and the Submit Attendance button is clicked, the append query is run. The message I receive is that 20 records (total # of students records) will be appended rather than the number of records returned based on the specific period.

At the risk of pushing my luck, I want to ask one more question. Because it runs an append query, it can't do updates of records. If a user clicks Submit Attendance a second time with a date that has already been submitted, ACCESS gives warning messages about key violations, etc. I understand why this occurs. Question is, how can I do a check to see whether records already exist for the date and, if so, give user a warning and stop the macro. I can prob handle the message and stop macro part. How do I go about finding whether a record already exists. I see an action that has FindRecord. If that is what I use, how do I do the conditional aspect? If a record exists then message user to use update option (yet to be added to student attendance form) and stop macro else continue with running the append query.

Thanks a million!!

[June7]

I don't use macros, only VBA.

However, macro can have an If Then Else structure. Can use a DLookup() function in the macro to check if value(s) already in table and act accordingly. In VBA, can have error handler code, don't know about that in macro.

One approach to attendance is to only have records for those who attend, as for a conference/meeting. However, I guess student attendance is different. I spent some time in a high school admin office processing attendance forms (feeding into automated scanner). I believe a record was generated for each student for each period the student was assigned to of each day. If student absent, a reason code for each period would be entered.

If you want to keep attendance by period, need period field in Student Attendance.

Set the Period combobox RowSource to SELECT DISTINCT Period FROM Student; and the RowSourceType to table/query.

Student table records should be unique for student name and ID. Need a related child table of records for periods student is assigned to, assuming not all students are in all periods. That child table would have to be the source of the APPEND sql.

I am confused. Can't find any code (macro or VBA) associated with the buttons (which are actually label controls) on SplashScreen. How are these opening the forms?

BTW, recommend no spaces or special characters/punctuation (underscore is exception) in names. Also, mixed case is easier to read than all caps. Like:
StudentID or Student_ID

Search the forum with
Keywords: Student Attendance
UserName: June7

There are at least 8 threads on the topic that might interest you. Here is one https://www.accessforums.net/access/...nce-20899.html

And Google: Access database student attendance
http://office.microsoft.com/en-us/ac...010353349.aspx
I don't know if this accommodates multiple classes or if it is an elementary school model.

[mroberts]

Thanks. I started off trying to use the students dB that can be downloaded but it didn't give me what I need. Specifically the multiple items form option. It would take up too much class time to cycle through each student to input attendance. Hence me trying my hand at my own. Access kept telling me that I can't use the multiple items form when trying to display data from the student and student attendance tables simultaneously. Kept making it single record form. I saw a tutorial that recommended adding the attendance status field to the student table to act as a temp holder and then taking that value and assigning it to the attendance table along with the current date. Works fine even though not necessarily the best way.

As for the splash screen, I used a hyperlink or subhyperlink in properties window to go to specific forms.

I do understand that my dB was not set up the way it would normally be as far as having a separate period table, etc. I was going for the quick and dirty route. lol My logic is that as long as period is in the student table, I can always find out what period the student was in based on their attendance records since they are related by student id.

Also, I will clean up my naming conventions.

I have it the way I can live with except figuring out why the append query is adding all records rather than just those limited by the period option. I am confused as to why it is returning the proper records based on period selected but will not just append those records. If I'm doing a requery onChange of the period combobox and it is returning the right records, wouldn't that mean those records would be in memory for when the append query is run? I am wondering as a work around if I could create a temp table based on the records returned based on period and then move those records to the attendance table with append query. If I can get that accomplished, I will do some reports and be done with it.

Thanks again for all your help. Just so you know, I've been trying to find solutions and not just trying to rely on your expertise.

[June7]

Okay, hyperlinks for opening forms - never used.

The form's filtered dataset has nothing to do with the APPEND selection of records. Both objects are pulling records directly from the same unfiltered source - the Student table.

The APPEND sql needs WHERE clause in the SELECT subquery.

WHERE Period=Forms!StudentAttendance!ClassPeriod

If no period selected, no records will append.

[mroberts]

DUH!!! Worked like a charm. I messed with the criteria earlier but just couldn't get it right I guess. Thank you so much!!!