Append Only Where The Record does not Exist (1 Viewer)

belly0fdesire

Registered User.
Local time
Today, 02:26
Joined
Dec 1, 2005
Messages
11
I am drawing a blank on how to get this to work.

I have a table called StraightTime with the fields ID (autonumber), UserName, Date, and Hours. I want an On Open Event for a Form to append to the table, and have the following as the VBA code behind the event:

Code:
Private Sub Form_Open(Cancel As Integer)
Dim SQL As String
Dim UName As String
Dim Dt As String
Dim Hrs As String
UName = UserNm
Dt = Date
Hrs = HoursLookup2()
SQL = "INSERT INTO StraightTime ([Username],[date],[hours]) values ('" & UName & "','" & Dt & "','" & Hrs & "')"
DoCmd.RunSQL SQL
End Sub
But I only want it to happen when there is not already an occurance of Username for that Date. Does this make sense? Every time someone opens the form, it puts in whatever time is set for that user, but I want it to only do so if there is not already a time entered for that date and user.
 
Last edited:

modest

Registered User.
Local time
Today, 05:26
Joined
Jan 4, 2005
Messages
1,220
This is more of a query question than a VBA, add this to the end:

"WHERE Not Exists (SELECT [date],[User Name] FROM StraightTime WHERE [date]='" & dt & "' and [User]='" & UName & "')"
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom