Access Attendance table help; Need to update table daily w/o copying table twice

[brikusi]

Please help,

I have a database whereby i am tracking attendance on a daily basis, however i may need to update the table more than once in the day as people enter the service later after attendance have already been captured. my current setup does not work. i have detailed it below. Please help me to understand how I can update the table without re-appending the entire table again.

tblATTENDANCE
AttendanceID (Autonumber)
PersonID (Number)
AttedanceDate - Date()
Comments (Short text)

tblPERSON
PersonID (Number (PK)
PersonName (Short text)
......
....
.....\
TempAttendance (Yes/No)
Comments (short text)

qryATTENDANCELOG (Append Query)
PersonID
TempAttendance 
Comments

append to tblATTENDANCE

frmATTENDANCELOG
PersonID
PersonName
TempAttendance
Comments

So I enter the information via the form and the form has a button which once pressed activates the append query to append the records to tblATTENDANCE. My problem is that:

A. when I open the form if marked, I can't get it to open clear with no check marks on the TempAttendance field, and no comments.

B. If i need to go back and update a record, or change a record i can't do that without the entire table being appended twice so I have rows repeating when i just needed to update a few records.

[twinnyfo]

brikusi,

Thanks for the clarification on this.

First, in tblPERSON, there is no need to have a TempAttendance field. Since this is a value that only indicates whether a person attended a particular session, this does not apply to every person at all times. So, it should not be in this table.

Second, also in tblPERSON, the field Comments mya be a bit confusing to me. When I think of the field Comments being in tblPERSON, I view it as comments about that person, that apply to that person alone--but that always apply to that person. However, you are copying that value to tblATTENDANCE. I see this as one of two things: 1) you are copying the general comments about the person (that applies to them at all times) to this table, which then is redundant data--so you shouldn't transfer this data, because it already exists; or 2) you are adding comments about the person concerning their particular attendance on a particular day. In this case, you would not use tblPERSON.Comments, but would simply use the Comments field of tblATTENDANCE, adding the comment when taking attendance.

So, these two issues must be resolved before you continue.

Concerning tracking attendance, rather than using an append query in the manner you have it built, I would use a Form with a text box to select the attendance date and two subforms. The first sub-form lists people who can attend; the second sub-form lists those who are marked as attending for a particular day. When you select a date in the text box, two things happen. You create a SQL string that you will use for your record source for the first sub-form. It will be a query that uses the selected date, JOINing tblPERSON with tblATTENDANCE based upon that attendance date, and it will only show people who have not attended on that date. It will also filter the second sub-form based upon the attendance date.

Of course this is neither more descriptive, nor does it include code to accomplish this. We'll take these steps slowly, because I would rather you understood what I am recommending before we create something incrrectly for your needs.

Standing by to provide more details as we move along.

Hope this hepps!

[brikusi]

Thanks for the comments, yes the comments in tblPerson relate only to the tempattendance record for that day.

So based on your reply i should remove Comments and TempAttendance from the tblPerson and only use the fields in tblAttendance.

I don't want to separate the people as I am not aware of who will attend late or not at all. This database is for a church. so people come and go throughout the service. Therefore I need to be able to update the table periodically throughout the day.

[twinnyfo]

brikusi:
So based on your reply i should remove Comments and TempAttendance from the tblPerson and only use the fields in tblAttendance.

Comments can move to tblATTENDANCE. TempAttendance is unnecessary.

brikusi:
I don't want to separate the people as I am not aware of who will attend late or not at all. This database is for a church. so people come and go throughout the service. Therefore I need to be able to update the table periodically throughout the day.

I think we are on the same page--I think you are just unsure of how this will work.

You will not be "separating people". However, envision this: You open your form. It defaults to today's date (because that is most likely the date that you will be taking attendance)--but you can select another date if you wish.

When the form is opened, because it has defaulted to today, the top of the form has a subform. It lists all people who are not currently listed as attending church that day (when you open the form for the first time on any given day, everyone will be listed as not attending). Next to each name, there is a small button "Attend".

At the bottom of the form is a list of everyone who is currently listed as attending the selected day's service. Again, when you first open it, there will be no one listed. It will list the person and comments.

When you click the "Attend" button, the person will be added to the bottom list and removed from the top list. As quickly as you can click through the names, they will be added.

Does this sound like a solution that you are willing to try?

[brikusi]

Ok yes I think I follow you now. It seems complicated however to build, I am new to access but I am willing to give it a try. Will reporting absentees be easy to do being that we will not be using button?

[twinnyfo]

I think you will find that this will be 1) less complicated than you initially might think and 2) a great learning experience!

Let's think about how we have to do this, by setting out some steps:

1. We must create a new main form (yes, you might have one already that does some good work for you, but let's just start from scratch).
2. We need to make sure that the date selection method works, to include being able to create a query that we will use as the record source for one of your sub-forms.
3. Create the "Person" sub-form, embed it on the main form and make sure it works.
4. Create the "Attendance" sub-form and make sure it works.
5. Finalize some minor details with the main form.

Concerning reporting absentees, think of it this way. Whenever you open the form and select a date, you have your list of asbentees! Remember, the ones in the "People" sub-form are those we have not identified as attending.

I am willing to walk you through these steps. Let me know when you would like to proceed....

[brikusi]

I am ready to start. I am hoping to start using this by this Sunday so as soon as I can get it started and working the better. so lets start.

I am keeping my tblAttendance table with the fields:
AttendanceID
PersonID
Attendance Date
Attended
Comments

right?

I am deleting the append query qryAttendanceLog
I am deleting my AttendanceLog Form
I am deleting the 2 fields [tempattendance and Comments] from the tblPerson table.

[twinnyfo]

I am keeping my tblAttendance table with the fields:
AttendanceID
PersonID
Attendance Date
Attended
Comments

The Field "Attended" is not necessary. Since this is a table called "tblAttendance" the implication is that anyone listed here has attended. So, having an attendance field is either superfluous or redundant.

I am deleting the append query qryAttendanceLog
I am deleting my AttendanceLog Form

For right now, let's not delete anything until we get everything working.

I am deleting the 2 fields [tempattendance and Comments] from the tblPerson table.

Correct.

Again, we will go through this step by step--methodically. We won't move to the next step until 1) I know you understand what we are doing and why and 2) that step works exactly as we want it to.

First step (a really easy one):
Create a Form. Make sure it is a pretty large form (it doesn't have to be huge--and you can always make adjustments later). I don't know how many members your church has, so I don't know if we are talking about 50 people or 5,000. Just remember that you will have two lists on this form. You can either have vertical lists and the two sub-forms are side by side or you can have them with one on the top and the other on the bottom. It is your choice and it really doesn't matter. Don't worry about colors and appearance for right now, because you can change all that later.

This form is unbound--so don't set its record source to anything.

On this form, add a text box. Set it's format to "Short Date". Make sure that the Show Date Picker property is set to "For Dates". If you want the form to default to today's date, in the Default Value Property, set it to "Date()" -- no quotes.

Save this form as "frmTrackAttendance". Open the form and make sure that when it opens you are able to click the Date Picker and select a date.

Let me know when you have accomplished this step.

[brikusi]

Wow thanks so much for the consideration and being willing to walk me through this step by step. I really appreciate this teaching.

I have completed the first steps as directed. I now have a new blank form called frmTrackAttendance with only a date textbox which defaults to today's date but has a date picker as well.

[twinnyfo]

Perfect!

Now, we need to make sure that your form will properly generate a list of people whom we have not identified as attending the date's service.

What we need to do is create a query, based upon the date of the Text Box. We want a list of everyone in your Person table who is not in the Attendance table. Make sense?

This can be done easily in SQL. But, here is our challenge: we will not be designing a Query and saving it (although we could). I prefer to build this type of query on the fly, because it uses a value from the form. Yes, you can use a form control as a means of establishing criteria in a query, but I also want to help you gain some confidence in working with VBA.

So, we need to create a Procedure that fires when you first open the form. From the Forms OnOpen Event, click the drop down box and select "[Event Procedure]". You will add the following:

Option Compare Database
Option Explicit
 
Private Sub Form_Open(Cancel As Integer)
 
    Call FindPeople
 
End Sub
 
Private Sub FindPeople()
    Dim strSQL As String
 
    strSQL = _
        "SELECT tblPerson.PersonID, " & _
        "tblPerson.F_Name, " & _
        "tblPerson.L_Name " & _
        "FROM tblPerson " & _
        "LEFT JOIN tblAttendance " & _
        "ON tblPerson.PersonID = tblAttendance.PersonID " & _
        "WHERE (tblAttendance.PersonID Is Null " & _
        "AND tblAttendance.AttendanceDate=#" & Me.txtDate & "#) " & _
        "OR tblAttendance.PersonID Is Null " & _
        "ORDER BY tblPerson.L_Name, tblPerson.F_Name;"
    Debug.Print strSQL
 
End Sub

We'll talk about this and then test it.

First notice that the second line is Option Explicit. It is a best practice to always have this line in your VBA modules. This can be set automatically in your VBA editor by going to Tools | Opitons | Editor and checking "Require Variable Declaration". This always ensures that you declare any variables that you use.

You will notice that there is a procedure called Form_Open. This is a built in event procedure that Access creates when you want to do something when a form opens. You will notice that there are only two words there: "Call FindPeople". This is because we are calling another procedure to fire when the form opens.

What? Why would we call another procedure when we just want to get a list of people? Well, think of it this way. Do we want to find people ONLY when the form opens? No. What happens if we select another date? Then we want to find a list of people based upon that new date. So, we create a separate procedure so we don't duplicate our code.

Now we have a procedure called FindPeople. This is where we build your SQL string. Look closely at what we are trying to do. We want the PersonID and the PersonName--that's all we need to make a list of potential attendees.

We join tblPerson with tblAttendance--but we use a LEFT JOIN, which means we want to display all the records in tblPerson, even if they don't exist in tblAttendance. We JOIN these two tables on the PersonID, because that is the mutual field by which the two tables are related.

But, then we add some weird criteria. First we say "WHERE tblAttendance.PersonID Is Null ". What's that all about? Remember how we said we want to show all the records in tblPerson even if they don't exist in tblAttendance? Well, this means that when there is no corresponding record in tblAttendance, that the field PersonID in tblAttendance will come back as Null. So, by saying we only want those records, we are saying we want all the people who don't exist in that table.

However, we must further limit the criteria, because we only want people who are not in attendance on a particular day--the day identified by your text box. So, we limit the records based on that criterion: "AND tblAttendance.AttendanceDate=#" & Me.txtDate & "#;". The pound signs (#itsapoundsign) identify the value as a date. Notice that I have named the text box "txtDate", so make sure yours corresponds appropriately.

Then, we merely print this string to the immediate window, to make sure we are designing a good string. In order to see your immediate window, type Ctrl-G.

Any questions--let me know. Go ahead and complete this and then test it by opening your form. Copy and paste your SQL string from your immediate window and we will see if your form works as designed.

You can also create a new query, switch to SQL view and then paste in your resultant text and run the query to see if it works.

We don't move forward until it works.

[brikusi]

Ok so I copied your script exact.
I made sure that mmy unbound textbox was named txtDate

I did have to change tblPerson.PersonName to tblPerson.F_Name, tblPerson.L_Name as "PersonName" doesn't exist in my table, I just used it earlier to shorthand.

When i repopen the form, its blank. Nothing has changed. When i try pasting just the sql part in the SQL view in a new query i get a syntax error. I tried to remove some of the "s but still get syntax error.

[twinnyfo]

Can you paste the SQL from the immeidate window here so we can take a look at it?

[twinnyfo]

BTW, I made that change to the code above and added Line 22, which sorts the query by Last Name, First Name.

[brikusi]

Sure.

I also took a screenshot of my error and tables but I can't seem to insert it on here.

SELECT tblPerson.PersonID, tblPerson.F_Name, tblPerson.L_Name
FROM tblPerson 
LEFT JOIN tblAttendance ON tblPerson.PersonID = tblAttendance.PersonID
WHERE tblAttendance.PersonID Is Null
AND tblAttendance.AttendanceDate=#" & Me.txtDate & "#;

Syntax error in date in query expression 'tblAttendance.PersonID Is Null and tblAttendanceDate=#"&Me.txtDate&'".

[twinnyfo]

You can upload pictures using the advanced button when you reply. You can also edit your response and insert again.

[twinnyfo]

So--what you have done is copied and pasted from the raw text. You should copy and paste from your immediate window. This will give you the true SQL string you want to work with (it should show a date instead of " & Me.txtDate & ".

Plus - update your code to my updated code in post #10.

[brikusi]

on on my form, on the "On.Open" field I selected the event procedure as you directed. I pressed ctrl G and got a new window, but there was no where to insert the code.

Was I to create a new module? The immediate Window option was greyed out so I went back to my form, and next to the event procedure were 3 dots (Ellipses) and a VBA window appeared where i put the code there.

Is that not correct?

Thanks for showing me how to add pictures, updated previous reply with images.

[twinnyfo]

Once you "Open" the form (NOT in design view) the form will create some text in that new window, which is called the Immediate Window.

[twinnyfo]

OK - since Access has a mind of its own--

I previously (like, yesterday), got this very same query to work without a hitch, but today, that very same query that worked perfectly, does not want to cooperate.

Soooooooooo..... Please recopy and paste the code lines 13-23 above into your VBA. For whatever reason, it wants to have this additional "OR" statement.

[brikusi]

I hope I'm not being difficult.
I've updated the code to reflect your changes

I opened the form (not in design view) and see nothing
I pressed CTRL G and see nothing. Images attached.

No errors; nothing.

Should anything be activated before using the code?
I wish i could send you a copy of the database. There's no data inside.

[twinnyfo]

The form should be creating a string and displaying it in the immediate window. Make sure your immediate window is displayed before you open the form and try it again. I’ll have to take a look tomorrow.

BTW, your form might be a little bit large—remember it just has to be able to display two lists. HOWEVER (and this is good) it looks exactly like it should look at this point.

And...... you are not being difficult at all. You are willing to go through this effort to learn, rather than demanding that I just do it for you. It took me about an hour to get things working right. But you are learning and it is worth MY effort to teach someone who wants to learn.

[brikusi]

i think Im still a bit confused on what I should be doing or seeing at this point.

i tried opening the immediate window 1st then the form. I still dont see a string of text.

can I debug this somehow to see why this isn't working?

[twinnyfo]

I think you are much closer than you think. Just confused because of newness.

Debugging:

Take a look at this:

See where the dark red dot is? Click your mouse in your VBA Editor in the left border right there. Now Open your Form. Your DB will then highlight that line of code, because it is a break point that the DB wants you to debug.

As you hit the F8 key, you will move line by line as the code executes.

If your DB never takes you there, then make sure that your form's property sheet looks like this:

If not, be sure to select "[Event Procedure]" in the on open event. Sometimes Access will clear this out, even though there is a procedure declared for that event.

We will get this working. One step at a time.

[brikusi]

Ok so i ran the debug, every section highlighted as I f8 through the code. no errors.

I did a screen record but I can't upload the video here. so I just made a ppt walking through the steps.

I also confirmed that the event procedure we in the On Open field in the property sheet

[twinnyfo]

And still nothing was sent to the Immediate Window?

[brikusi]

unless I am missing something, I didn't see it. Can you screenshot what it should look like?

[twinnyfo]

Take a look here. Notice the bottom pane.

[twinnyfo]

I still haven't seen any pictures of your immediate window, either. It will be a pane in your VBA Editor that simply says "Immediate" in the header bar.

[twinnyfo]

You can also take a look at this tutorial on VBA Debugging in your free time.

[brikusi]

OMG I AM SOOOO SORRY I HAVE WASTED YOUR TIME....

All this time I have seen immediate window at the bottom thinking it was indicating that the screen i was looking at was the immediate window. After viewing your screen I now see it was collapsed and I had to drag it up.

Once I did that I see the sql code there...

sorry sorry sorry...


Update: I copied the sql from there and put it in a new query and it generated results with the personID, first name, and last name.

[twinnyfo]

Perfect!!!!! No need to apologize. You have learned something today.

Now, on to the next step!

We need to create a subform to house the People. I will describe everything here, and you have to follow directions--but you have to TRUST me on this, because I will tell you to do some strange things that don't make sense.

Create a new form called "fsubPeople". Set its default view to "Continuous Forms". No navigation buttons, No Record Selectors, Vertical Only Scroll Bars, No Control Box, No Close Button, No Min Max Buttons, No Allow Additions.

Set its Record Source as tblPerson. (I'll explain later).

From the Form Design Tools, select Design, in the Tools Section, select Tools and click on Add Existing Fields. Drag and drop the F_Name and L_Name Fields over to the detail section of your form. Get these two text boxes nice and cozy all the way at the top of the detail section. You can delete the labels for these text boxes. Rename the text boxes to txtL_Name and txtF_Name.

Now, listen carefully. I can't explain why this happens (at least it does to me), but we need a way for this sub form to identify the Person ID, but we don't want to display it. Drag the PersonID field onto your form.

Then....

Delete it.

Add a Command button to the form and name it cmdAttend with caption "Attend". On Click event for that Command Button:

Option Compare Database
Option Explicit
 
Private Sub cmdAttend_Click()
    Dim strSQL  As String
    Dim db As DAO.Database
 
    strSQL = _
        "INSERT INTO tblAttendance " & _
        "( PersonID, AttendanceDate ) " & _
        "VALUES ( " & Me.PersonID & ", #" & _
            Me.Parent.Form.txtDate & "# );"
    Debug.Print strSQL
    Set db = CurrentDb()
    Call db.Execute(Query:=strSQL, _
                    Options:=dbFailOnError)
    Set db = Nothing
 
    Me.Requery
 
End Sub

In the end your tiny subform should look something like this:

Finally, go back to the record source for this form and delete the Record Source. The only reason we used the table as a record source was to establish certain field names and ensure we got everything correct (and it's just easier than designing unbound controls that have nothing to use as control sources).

Save that form and close it.

Open your Attendance form in design view, and drag the new subform onto your main form. Be sure to rename the sub form fsubPeople (if it didn't default to that name).

But, now, we need a way to update the list of people on the sub form. Since we know that your SQL creates a useable list of people...... We need to set that string to the record source of the new subform you made. Your FindPeople() procedure should now look like this:

Private Sub FindPeople()
    Dim strSQL As String
 
    strSQL = _
        "SELECT tblPerson.PersonID, " & _
        "tblPerson.F_Name, " & _
        "tblPerson.L_Name " & _
        "FROM tblPerson " & _
        "LEFT JOIN tblAttendance " & _
        "ON tblPerson.PersonID = tblAttendance.PersonID " & _
        "WHERE (tblAttendance.PersonID Is Null " & _
        "AND tblAttendance.AttendanceDate=#" & Me.txtDate & "#) " & _
        "OR tblAttendance.PersonID Is Null " & _
        "ORDER BY tblPerson.L_Name, tblPerson.F_Name;"
 
    With Me.fsubPeople
        .Form.RecordSource = strSQL
        .Requery
    End With
 
End Sub

Notice that we also deleted the Debug.Print strSQL (because we know that it works!).

Your main form should now look something similar to this:

But wait! There's one more thing we have to do. Whenever we change the value of your Date text box, we need to update everything, too. So, we add the following procedure in the text box's On Change Event:

Private Sub txtDate_Change()
 
    Me.Refresh
    Call FindPeople
 
End Sub

We're just refreshing the value of the text box to that its value can be used, and updating the people.

Save your form and close it.

Open your Form and see if there are any errors. At this point you should be able to select different dates, and if there is any attendance data already in tblAttendance, it should reflect in your list of names. If you click on "Attend" next to any name, that name "should" disappear from the list.

Let me know how you get along with this.

[twinnyfo]

Also, on your People sub-form, txtF_Name and txtL_Name should be Enabled=No and Locked=Yes.

[brikusi]

I made all the changes, form is looking great. I did get an error however when I ran It. I think its looking for the PersonID to be there as you mentioned above.

Can we add it there ant set its property to not visible? Just a thought if that is the cause of the error.

[twinnyfo]

Yep - that should work. I think I just try to trick Access when I do that. It makes me feel like I’ve got a leg up on the machine...... It should work fine after that.

[brikusi]

That did it! Works as expected.

[twinnyfo]

Let me know how you get along with your other tasks and we will finish up.

[brikusi]

I completed the new query and additional subform and added it to the master form. I noticed as I ran the form that as they are removed off 1 list they are added to another. This is pretty cool.

Quick questions as I'm starting to see whats going on:

1. Am i tracking only those who attended or absent in the 2nd form?
- Do I only mark those who are not present then provide comments about them in the 2nd sub form?

2. What if someone is marked by mistake? How would I bring them back to the 1st subform?

[twinnyfo]

You are so close! I'll answer your questions, then finish up what you need to do.

1. Am i tracking only those who attended or absent in the 2nd form?
- Do I only mark those who are not present then provide comments about them in the 2nd sub form?

This is a list of those who have attended. Those on the first form are those who are absent.

2. What if someone is marked by mistake? How would I bring them back to the 1st subform?

This can easily be built into the 2nd subform. Just add a button to delete the current record and refresh everything.

So, final steps:

For those who are wondering, during PM, the following instructions were given last night:

twinnyfo:
The next step is even more super easier than anything else so far. Hopefully you can understand this via e-mail. Create a query. Add tblAttendance and tblPerson. Make sure that the two tables are joined on PersonID. Add the fields AttendDate, F_Name, L_Name and Comments to your query. Note that the Name fields come from tblPerson, but because the tables are joined on PersonID, these will match the people who have attended. Sort the query by L_Name and F_Name. Save the query as qryAttendance.

Create another sub-form, called fsubAttendance. Form view is Continuous. Record source is qryAttendance. Same settings on all the properties for the other sub form. Add text boxes (with appropriate renaming) for F_Name, L_Name and Comments. The Name fields should be Enabled=No, Locked=Yes. Comments is enabled and unlocked. Make the text boxes all nice and pretty. Save the form and put it on the main form, just like you did for the other sub form.

First, we need to make an update to Post #31, first block of code in order to update the 2nd sub-form. Your procedure should now look like this:

Private Sub cmdAttend_Click()
    Dim strSQL  As String
    Dim db As DAO.Database
 
    strSQL = _
        "INSERT INTO tblAttendance " & _
        "( PersonID, AttendanceDate ) " & _
        "VALUES ( " & Me.PersonID & ", #" & _
            Me.Parent.Form.txtDate & "# );"
    Set db = CurrentDb()
    Call db.Execute(Query:=strSQL, _
                    Options:=dbFailOnError)
    Set db = Nothing
 
    Me.Requery
    Me.Parent.Form.fsubAttendance.Form.Requery
 
End Sub

Now, we need to make sure that the Attendance list is properly filtered by date. Same post, 2nd block of code, we've added the filtration code at the end of your procedure:

Private Sub FindPeople()
    Dim strSQL As String
 
    strSQL = _
        "SELECT tblPerson.PersonID, " & _
        "tblPerson.F_Name, " & _
        "tblPerson.L_Name " & _
        "FROM tblPerson " & _
        "LEFT JOIN tblAttendance " & _
        "ON tblPerson.PersonID = tblAttendance.PersonID " & _
        "WHERE (tblAttendance.PersonID Is Null " & _
        "AND tblAttendance.AttendanceDate=#" & Me.txtDate & "#) " & _
        "OR tblAttendance.PersonID Is Null " & _
        "ORDER BY tblPerson.L_Name, tblPerson.F_Name;"
 
    With Me.fsubPeople
        .Form.RecordSource = strSQL
        .Requery
    End With
 
    With Me.fsubAttendance.Form
        .Filter = "AttendanceDate = #" & Me.txtDate & "#"
        .FilterOn = True
    End With
 
End Sub

Aside from adding a close button to your main form and making things more beautiful-looking (and possibly adding a delete button to the 2nd sub-form), unless I am gravely mistaken, you are done!

You can take that to church this Sunday!

Let me know if you need anything else.

[brikusi]

Awesome sauce! I am going to apply the following steps above but I think I may have something backwards.

The first form doesn't have comments. if this form is for those absent (fsubPeople) I will need comments added there, so I can note rather their absence is due to trip, sick, etc.

Also this is the information that I will need to be tracking in a report. but the 2nd sub form (fsubAtttendance) is whats recorded in tblAttendance.

[twinnyfo]

OK - So your last point threw a gigantic wrench into things. However, It is not so much a wrench as a change in thought.

OK - if you need to track attendance for those who attend but keep notes on those who don't, then the first list now becomes your attendees and your second list becomes your absentees (which might make sense ultimately to change the name of the table to "tblAbsentees").

Here is your challenge either way: If you are only tracking absentees, then you are really looking for those people who are not there? Have you ever tried looking for something that, literally, is not present? This makes it difficult to manage the list, but from a database design perspective, things are easy.

If you want to still keep attendance, but also track absentees with comments, there is na easy way to do it. Simply copy all the records for all the people over to your attandance table every time you take attendance and add a check box for attendance and then the comments field is available for those who are absent.

Either way is possible. This just depends on what types of numbers you are talking about. If you have small numbers, then keeping track of those who are absent is a bit easier. However, as numbers grow, the second option becomes more feasible as a user.

If you use the second option, though, you will need a way to track attendance and maintain history. So, as people come and go, you can't simply delete the person from tblPerson. First, tblAttendance won't allow you to delete them. Second, you will lose the history for that person's attendance. So, you will need an "Active" field in tblPerson (Yes/No field) that simply indicates whether the person is an active member of the church. Then, each time you take attendance, you run this SQL:

INSERT INTO tblAttendance ( PersonID, AttendanceDate )
SELECT tblPerson.PersonID, Date() 
FROM tblPerson 
WHERE tblPerson.Active;

AND--you no longer need the two subforms, but just the attendance subform, filtered by date. You'll have to add that Attend Check Box to that subform (and the table, of course).

Please don't think that you have wasted your time (or mine) by this extended exercise, which we may not use for this function. Hopefully you have learned something new that you can use in the future.

Hope this hepps!

[brikusi]

You're losing me now a bit.
I like what we have built.

Can't I keep the same concept of what we have now and just switch the names around? So rather all those who are mark( click the button) are those considered absent?

they then go to the 2nd sub form and I can record comments about them there?

Is that not an option? versus having to redo the design and how everything works?

secondly I made the changes in the post prior to this one. the only thing i notice is that when I run the form for today. Those I marked yesterday are still gone.

[twinnyfo]

Can't I keep the same concept of what we have now and just switch the names around? So rather all those who are mark( click the button) are those considered absent?

Yes, that is my first comment exactly.

secondly I made the changes in the post prior to this one. the only thing i notice is that when I run the form for today. Those I marked yesterday are still gone.

Exactly what do you mean? Gone from the first list or the second list? Because it is based upon a date, those names marked from yesterday will not be on the second list today. Maybe I'm just confused.

[brikusi]

Ok great so I just changed the label of the sub form to Church List and the second form is Absentees, I kept everything else the same.

to the 2nd question. Let's say I logged someone yesterday as absent, from the 1st sub form with all the people listed. Today when I open the form to take attendance those who i marked as absent yesterday has disappeared from the church list (fsubPeople). So I can't mark them as absent today if i needed to.

I guess I'm saying, the church list (fsubPeople) needs to re-list everyone again for a new day. Does that make sense?

I am attaching two images.
Image 1 (Yesterday Attendance)3/14 you can see I marked Devonia, Barbara, and Carl as Absent

now If I change the date to 3/15, Devonia, Barbara, and Carl are not repopulated to the original list again.

[twinnyfo]

I understand completely.

So, this is my bone-headed mistake.

Change the designing of the SQL statement to this:

    strSQL = _
        "SELECT tblPerson.PersonID, " & _
        "tblPerson.F_Name, " & _
        "tblPerson.L_Name " & _
        "FROM tblPerson " & _
        "LEFT JOIN " & _
            "(SELECT PersonID " & _
            "FROM tblAttendance " & _
            "WHERE AttendanceDate=#" & Me.txtDate & "#) " & _
            "AS T1 " & _
        "ON tblPerson.PersonID = T1.PersonID " & _
        "WHERE tblAttendance.PersonID Is Null " & _
        "ORDER BY tblPerson.L_Name, tblPerson.F_Name;"

NOW it should work! :-)

[twinnyfo]

Notice how Goofy is scratching his head......

[brikusi]

LOL

Wow! Thank you thank you! thank you!
I can't thank you enough. You don't know how much I struggled with this thing and the fact that you dedicated so much individual time to assist me means the world to me. I wish I could repay you some how. I have learned so much through this experience. The VBA is a bit confusing so I will go back later and try to decipher through it to write comments to try and understand what different parts are doing exactly. But you have been great. You're great at what you do without a doubt. Thank You sincerely.

I'm not sure which part I should mark as best answer....I wish I could do all of it but I guess I'll mark the very beginning where the idea was proposed.

[twinnyfo]

I'm glad I could be of service. when work is a bit slower than normal, I can distract myself to help others.

So pleased that things worked out. If you have any questions on what hte code does and how it works, just shoot me a note.