Attendance Register - VBA to ensure dates not duplicated

[sistemalan]

Hi all and thanks for taking the time to read,

I have built a database for monitoring attendance of children at one of our charity's music projects. I have a form which lists all children on the register and allows me to tick a box for each child in attendance on a certain day. The form is bound to a temporary attendance table. I can type the correct date in an unbound field in the form footer and click a button which runs an update query, transferring the information from the temporary form into a table called "AttendanceTable New".

I'd like to avoid the possibility of forgetting to update the date and put in two lots of data for the same date. Can someone help me with the VBA code to do the following:

When button is pressed check to see if any records in the date collum of the Master Attendance Table match the date in the unbound text box on the form.
If they do, show a message and do not run the update query
If not, run the update query.

The VBA for the button currently looks like this.

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click

    Dim stDocName As String

    Me.Refresh
    
    stDocName = "Attendance Append Query"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    
    stDocName = "Attendance Reset Query"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

    MsgBox "Attendance Updated. Hoozah!"
    
    Me.Refresh

Exit_Command10_Click:
    Exit Sub

Err_Command10_Click:
    MsgBox Err.Description
    Resume Exit_Command10_Click
    
End Sub

Many thanks for any help, I think it might take a while for me to figure this out on my own, but I suspect it's pretty easy for one with knowledge.

Alan

 

[namliam]

1) Please dont leave default control names like "Command10", in two months you will not remember what button that is... Instead use meaning full names like 'cmdSave" or something

2) To sovle your dilemma I think you can use a DCount to check if the value doesnt exist already, if it does... Give an error message.
Look up DCount and if you need it "IF" in the access help and see how far you get.

Good luck

 

[sistemalan]

Hi and cheers for responding.

I reckon I can figure out the IF statement bit, but I've been having trouble getting the syntax right for DCount. So far this is what I've got:

formDate = [Forms]![Attendance Form New]![AttendanceDate]

dcountthing = DCount("*", "[AttendanceNew Table]", "[Date] = " & formDate & "")

MsgBox dcountthing

I'm just trying to see if I can get it to count the number of times a certain date appears in the table, however when I put in a date that I know has records attached to it in the table the number I'm getting out is still 0. I suspect the problem lies in the syntax of the string formDate.

Help!

 

[pbaldy]

A handy reference on the syntax needed:

http://www.mvps.org/access/general/gen0018.htm

 

[sistemalan]

Thanks,

OK I've now got the following:

Private Sub Count_Click()

dcountthing = DCount("*", "[AttendanceNew Table]", "[Date] = #" & [Forms]![Attendance Form New]![AttendanceDate] & "#")

MsgBox dcountthing

End Sub

But the result is the same. Zero every time.

Any ideas?

 

[pbaldy]

It might be confused by the field name. "Date" is a reserved word, since there's a built in function of the same name. Can you post the db?

The spaces in your names aren't a good idea either, by the way. They force you to bracket the names, and they're just more trouble than they're worth in the long run.

 

[namliam]

Is your date field a date/time field ??
If so does it have a time in it??
If not, this is probably an issue of a date conversion error where your using euro format in your form (DD/MM/YYYY) instead of the US Format (MM/DD/YYYY) that the ## is using/converting to...

 

[DKO]

Another possible solution is to set the table's primary key to the student's name + the date.

Then it won't let you add the same student's record more than once with the same date.

 

[sistemalan]

Thanks for many useful comments.

In the end I used DKO's idea of two primary keys. It does what I need it to do. I was a bit out of my depth with the VBA code but it's been an interesting introduction to this next level of Access. Thanks to the person who offered to look at the database. Unfortunately I can't post the file as it contains sensitive information about children and their families.

Thanks again,

Alan