Results 1 to 11 of 11
  1. #1
    mroberts is offline Novice
    Windows Vista Access 2007
    Join Date
    Jul 2013
    Posts
    10

    Displaying all records in multiple items form based on combobox value

    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!



    Click image for larger version. 

Name:	Screenshot.gif 
Views:	18 
Size:	30.6 KB 
ID:	13157 Hope it shows up larger.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,609
    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?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    mroberts is offline Novice
    Windows Vista Access 2007
    Join Date
    Jul 2013
    Posts
    10
    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.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,609
    Try using DISTINCT in the RowSource query:

    SELECT DISTINCT period FROM tablename;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    mroberts is offline Novice
    Windows Vista Access 2007
    Join Date
    Jul 2013
    Posts
    10
    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.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,609
    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    mroberts is offline Novice
    Windows Vista Access 2007
    Join Date
    Jul 2013
    Posts
    10

    Attaching copy of dB

    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!!
    Attached Files Attached Files

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,609
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    mroberts is offline Novice
    Windows Vista Access 2007
    Join Date
    Jul 2013
    Posts
    10
    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.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,609
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    mroberts is offline Novice
    Windows Vista Access 2007
    Join Date
    Jul 2013
    Posts
    10
    DUH!!! Worked like a charm. I messed with the criteria earlier but just couldn't get it right I guess. Thank you so much!!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 04-19-2012, 10:13 AM
  2. Replies: 5
    Last Post: 03-12-2012, 02:58 AM
  3. Replies: 18
    Last Post: 01-27-2012, 12:53 PM
  4. Displaying multiple records on one form
    By Juicejam in forum Forms
    Replies: 12
    Last Post: 12-12-2011, 07:02 PM
  5. Replies: 2
    Last Post: 07-10-2011, 07:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums