Limit record creation

[PhoenixDon]

I wrote a little Access program to allow supervisors input the daily duty status of their employees. Managment wanted a report so everyday they knew how many people were at work.

I have two tables, one is all the people assigned, the other is their duty status. I created an AutoExec macro that runs the code below. My problem, each day a new instance of each person is supposed to be created. The first person to access the database runs the code below. If the date is already found, then it's supposed to skip it. But everyday, every record has duplicate dates. What did I do wrong? (BTW, system dates are set by the network and can not be changed by users.)

Any suggestions appreciated.

Thanks,
Don

[tt]
******************************************
Function StartNewDay()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Personnel.*, [Duty Status].[Status Date], [Duty Status].[Duty Status], [Duty Status].Comment " & _
"FROM Personnel " & _
"LEFT JOIN [Duty Status] ON Personnel.ID = [Duty Status].ID " & _
"WHERE [Duty Status].[Status Date]= #" & Date & "#"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
If rst.RecordCount = 0 Then
rst.Close
strSQL = "INSERT INTO [Duty Status] ( ID, [Status Date] ) " & _
"SELECT Personnel.ID, Date() AS TODAY " & _
"FROM Personnel LEFT JOIN [Duty Status] ON Personnel.ID = [Duty Status].ID"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "OK, everyone has been added for today!", vbExclamation

Else
'DO NOTHING
'MsgBox "Sorry, records for today have already been started!", vbCritical
End If
End Function
[/tt]

 

[PHV]

Have you tried this ?
strSQL = "INSERT INTO [Duty Status] ( ID, [Status Date] ) " & _
"SELECT Personnel.ID, Date() AS TODAY " & _
"FROM Personnel"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[scottian]

Surely its easier to set the default value of the field to Date()



"Its not who you are that defines you, its what you do..." - Bruce Wayne

 

[vacant]

Agree your append query should be as PHV writes

Your initial query seems a bit over the top as well, you don't need the Personnel table in there. You just need to look for an instance of today's date in the Duty Status table and append if there's no instance of it

Something like this ought to do...

If Not IsNull(DLookup("Status Date","Duty Status","Status Date=Date())) Then 'do append

 

[PhoenixDon]

PHV and Scottian,

Thanks for the replies. My question is, why am I getting duplicate records?

Thanks,
Don

 

[vacant]

Because the check query doesn't work, probably something to do with date formats. Try replacing the last line with:

"WHERE [Duty Status].[Status Date]=Date()"

That would bring in some consistency, since when you append you use Date()

 

[PhoenixDon]

Thanks for the response. Problem solved!