Help to Create Form

[kruephil]

I am relatively new to access and am learning a lot on my own. I work at a church and am currently building and attendance tracking database for our adults, youth group and children's programs. I have created an attendance history table which has the following fields: People ID, Full Name, and attendancedate, and present (which marks an x if the student was present). What I want to do is create a form that lists the people ID, Full Name present fields on a specific date. I have tried doing this using a Subform but have run into problems. This is how I want the form to be built: have a text box that shows the date with the ability to toggle between the dates in my table and a list box that shows the people present on the date that is being toggled. Is this possible if so can someone guide me on how to do it? Hopefully this makes sense. Thanks for any help offered.

[June7]

What do you mean by 'toggle'.

Basically, you are describing cascading (or dependent) combo/list boxes. Check this tutorial. http://www.datapigtechnologies.com/f...combobox2.html

You can have a combobox that lists the dates from the table (or a textbox and any date could be entered) then code would refresh the listbox when date selected.

[kruephil]

When I say "Toggle" I mean switching to other dates in the table and when I switch the date the List box shows the People ID, Full Name and Present Fields for that date. I'm not sure the above tutorial is much help. I tried doing it but did not have any luck. I might be doing something wrong with the code. Is there a less confusing way to accomplish this.

[June7]

Here is a small project to demo what I think you want. I will remove it after you respond or in a few days. EDIT: purpose served, file removed.

Recommendations:
Don't have a 'full name' field. Name parts should be in separate fields.
Create attendance record only if individual attended, so no need for the Present field.

[evander]

Maybe you can create a combo box whose list is populated by a table of dates.

Then, in the Change event of the combo box, you manipulate the list box's row source:

Private Sub cboDate_Change()
     lstPeople.RowSource = "SELECT * FROM tblAttendance WHERE AttendanceDate = #" & cboDate & "#;"
End Sub

Haven't really tested the code, but it could get close to what you want to achieve. Hope this helps, and good luck!

[kruephil]

This is essentially what I want to do. Can I create previous and next buttons that go through the dates as well as the combo box? This way I could navigate to recent dates quicker and for older dates use the combo box.

[June7]

Yes, you can have buttons that perform the Previous and Next record navigation.

Be aware that the code suggested by evander modifies the form's recordset so that it is filtered to only records that meet the date criteria. If you wanted to move to other records not matching this date criteria, they would not be available without setting the RecordSource property again.

Alternatives to changing the RecordSource are:
1. code that will GoTo a record, this does not modify the recordset
2. programatically setting the Filter and FilterOn properties, this is similar to setting the RecordSource in that it restricts the available records but the filter can be toggled on and off either with a custom button or the built-in filter controls

[kruephil]

June 7, I'm not using evander's method I'm using the example you gave me. I got the code to work for my database. As I said I am still learning. How would I get the previous and next buttons to go through the dates from the date combo box. I'm lost on this.

[June7]

May have misunderstood your question. The Previous and Next behavior would not go through the dates from the date combobox. Those controls would navigate the records of a form's RecordSource.
evander's suggested code is unrelated to the question about Previous and Next controls. It offers a way to find a record using a value entered into a combobox as filter criteria.

Describe the form you do this on. Is it for data entry? What is the form's RecordSource? What are the fields of the recordset? Have you bound controls to the fields?

Can you attach file to post?

[evander]

I answered your question in the context that you want to use a listbox to display the names of those people that were present on the date you specify. As they say, there are many ways to skin a cat. As for me, I could use a subform (not linked to its containing parent form) to display the names. I think June 7's solution works appropriate for your situation.

To close this thread, you can click "Mark this thread as solved" under Thread Tools. Cheers!

[kruephil]

June7 Here is my database so far. When you open it the form I want to work on is titled "Service Summary". I am using Access 2007. What I would like to do is take create buttons that go through dates in my date combo box (which is comb7) so that way I can just flip through the dates that way and see who was present rather then selecting a date via combo box. Is this possible if so how?

[June7]

The RowSource for combobox has an error. Should be: SELECT DISTINCT [Attendance Date] FROM Attendance;
The [] are missing from your statement. This is what happens when spaces, special characters (@ # $ % & *), punctuation (underscore is exception) are used in names. Must enclose in []. Same for names that are reserved words (Date, Name).

This will fix the behavior of the combobox.

Should not be duplicating child's name in the Attendance table. Just record the ChildID then retrieve the name by joining tables. This is a major principle of relational database - don't duplicate data. Actually I just noticed you have done this join in the listbox RowSource. Eliminate the name field from Attendance table.

Use of Previous and Next buttons would not be to cycle on the combobox but to navigate records of a form. This would replicate the intrinsic navigation bar found at bottom of the form. Modify form as follows:
RecordSource: SELECT DISTINCT [Attendance Date] FROM Attendance;
ListBox RowSource: SELECT [Last Name] & ", " & [First Name] AS KidsName
FROM Kids RIGHT JOIN Attendance ON Kids.ChildID = Attendance.ChildID
WHERE (((Attendance.[Attendance Date])=[Forms]![Service Summary]![Attendance Date]));
Add a textbox to display the date from RecordSource record.
Code in the Form OnCurrent event: Me.List5.Requery
Add buttons in form header - Previous and Next. In their Click event code (VBA or macro) to move to next or previous record.

[kruephil]

June7 You lost me on some of your tips. I've tried to do some of the stuff but still cannot get my form to work. The "text box" for the date is where I'm lost. I'm not sure where to put the recordsource here. Also in doing the form code. I don't know what to do here as well. I'm still pretty new to access so I get lost in all the code building and such. Thanks for all your help.

[kruephil]

June7 I did get it working now. I figured out the things you were talking about. I will mark this as solved and ask for help on further form questions. Thanks again so much!