Hello everyone,
This is my first post on this board, I have a problem that I cant find any answers for on the web. I have a Private sub on a form, and I have sql code that draws the information that I want, but does not work when I try to set up a definition because I get the run Time error 3075. How Come? It gives me back the line of SQL code that is in the WHERE clause, [TableStudyHallHoursDetail].[Name] In (SELECT [Name] FROM [TableStudyHallHoursDetail] As Tmp GROUP BY [Name],[Date],[InTime] HAVING Count(*)>1 [Name]='" & Me!TextName & "' And [Date] ='" & Format(Me.TextDate, "\#mm\/dd\/yy\#") & "' And [InTime] ='" & Me!TextInTime & "')
Here is my code in VBA:
Private Sub TextInTime_AfterUpdate()
Dim db As Database
Dim rst As Recordset
Dim strSQL As String
Dim qdf As QueryDef
Set db = CurrentDb()
strSQL = "SELECT TableStudyHallHoursDetail.[Name], TableStudyHallHoursDetail.[Date], TableStudyHallHoursDetail.[InTime], TableStudyHallHoursDetail.[HourID], TableStudyHallHoursDetail.[OutTime], TableStudyHallHoursDetail.[AuditTime]" & _
"FROM [TableStudyHallHoursDetail]" & _
"WHERE [TableStudyHallHoursDetail].[Name] In (SELECT [Name] FROM [TableStudyHallHoursDetail] As Tmp GROUP BY [Name],[Date],[InTime] HAVING Count(*)>1 [Name]='" & Me!TextName & "' And [Date] ='" & Format(Me.TextDate, "\#mm\/dd\/yy\#") & "' And [InTime] ='" & Me!TextInTime & "')" & _
"ORDER BY TableStudyHallHoursDetail.Name, TableStudyHallHoursDetail.Date, TableStudyHallHoursDetail.InTime;"
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
With db
Set qdf = .CreateQueryDef("tmpDuplicates", sSQL)
DoCmd.OpenQuery "tmpDuplicates"
.QueryDefs.Delete "tmpDuplicates"
End With
db.Close
qdf.Close
End Sub
Once, I ran a debug.printSQL statement and it came back as
SELECT TableStudyHallHoursDetail.[Name], TableStudyHallHoursDetail.[Date], TableStudyHallHoursDetail.[InTime], TableStudyHallHoursDetail.[HourID], TableStudyHallHoursDetail.[OutTime], TableStudyHallHoursDetail.[AuditTime]FROM TableStudyHallHoursDetailWHERE (((TableStudyHallHoursDetail.Name) In (SELECT [Name] FROM [TableStudyHallHoursDetail] As Tmp GROUP BY [Name],[Date],[InTime] HAVING Count(*)>1 [Name]='Smith, Bill' And [Date] =#9/27/2006# And [InTime] =6:00:00 PM)))ORDER BY TableStudyHallHoursDetail.Name, TableStudyHallHoursDetail.Date, TableStudyHallHoursDetail.InTime;
So it seems like the information is being drawn from the table! ARRRGH
Any help would be greatly appreciated!
Thanks!