DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

Visual Basic 4 / 5 / 6 RSS

RunTime 3075, cant see the problem? And Values are as they should be...

•

Join Date: Oct 2006

Posts: 2

Reputation: snaged46 is an unknown quantity at this point

Solved Threads: 0

snaged46

Offline

Newbie Poster

RunTime 3075, cant see the problem? And Values are as they should be...

Oct 2nd, 2006

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:

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
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
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!

Last edited by Comatose; Oct 3rd, 2006 at 5:51 pm.