Sandra Smith
asked on
Query def return error a mismatch but still creates the query
I have the below procedure, but when it hits the query def, I get Run-time error 13, Type mismatch. But it does create the query properly. I do not understand why it would create the query, yet error out at the query def statement.
Public Sub CheckImportDates()
'checks to see if there are importID's for employee. If not, adds
'the ImportId information to the table
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rstTable As DAO.Recordset
Dim strDoesNotExist As String
Dim qdfDoesNotExist As QueryDef
strDoesNotExist = "SELECT DISTINCT tblTEMPImport.ImportID, tblTEMPImport.[Last Name], " & _
"tblTEMPImport.[First Name], tblTEMPImport.Project " & _
"FROM tblTEMPImport " & _
"WHERE (((Exists (SELECT ImportID, [Last Name], [FirstName], ProjectName " & _
"FROM tblImportedMonths " & _
"WHERE tblTEMPImport.ImportID= tblImportedMonths.ImportID AND " & _
"tblTEMPImport.[First Name] = tblImportedMonths.FirstNam e AND " & _
"tblTEMPImport.[Last Name] = tblImportedMonths.LastName AND " & _
"tblTEMPImport.Project = tblImportedMonths.ProjectN ame))=Fals e)) "
Set dbs = CurrentDb
Set rst = dbs.CreateQueryDef("qryDoe sNotExist" , strDoesNotExist) "ERRORS OUT HERE
Set rstTable = dbs.OpenRecordset("tblImpo rtedMonths ", dbOpenTable)
If rst.RecordCount > 0 Then
rst.MoveFirst
Do While Not rst.EOF
rstTable.AddNew
rstTable!ImportID = rst!ImportID
rstTable!LastName = rst![Last Name]
rstTable!FirstName = rst![First Name]
rstTable!Properties = rst!Project
rstTable.Update
rst.MoveNext
Loop
Else
'do Nothing
End If
rst.Close
rstTable.Close
Set rst = Nothing
Set rstTable = Nothing
Set dbs = Nothing
End Sub
Sandra
Public Sub CheckImportDates()
'checks to see if there are importID's for employee. If not, adds
'the ImportId information to the table
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rstTable As DAO.Recordset
Dim strDoesNotExist As String
Dim qdfDoesNotExist As QueryDef
strDoesNotExist = "SELECT DISTINCT tblTEMPImport.ImportID, tblTEMPImport.[Last Name], " & _
"tblTEMPImport.[First Name], tblTEMPImport.Project " & _
"FROM tblTEMPImport " & _
"WHERE (((Exists (SELECT ImportID, [Last Name], [FirstName], ProjectName " & _
"FROM tblImportedMonths " & _
"WHERE tblTEMPImport.ImportID= tblImportedMonths.ImportID
"tblTEMPImport.[First Name] = tblImportedMonths.FirstNam
"tblTEMPImport.[Last Name] = tblImportedMonths.LastName
"tblTEMPImport.Project = tblImportedMonths.ProjectN
Set dbs = CurrentDb
Set rst = dbs.CreateQueryDef("qryDoe
Set rstTable = dbs.OpenRecordset("tblImpo
If rst.RecordCount > 0 Then
rst.MoveFirst
Do While Not rst.EOF
rstTable.AddNew
rstTable!ImportID = rst!ImportID
rstTable!LastName = rst![Last Name]
rstTable!FirstName = rst![First Name]
rstTable!Properties = rst!Project
rstTable.Update
rst.MoveNext
Loop
Else
'do Nothing
End If
rst.Close
rstTable.Close
Set rst = Nothing
Set rstTable = Nothing
Set dbs = Nothing
End Sub
Sandra
ASKER
The below should then set the rst to qdfDoesnoteExist?
Set qdfDoesNotExist = dbs.CreateQueryDef("qryDoe sNotExist" , strDoesNotExist)
Set rst = qdfDoesNotExist
Set qdfDoesNotExist = dbs.CreateQueryDef("qryDoe
Set rst = qdfDoesNotExist
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. Got over that hurdle. Am posting another question as it now stops at the statement trying to open the table!
Set rst = dbs.CreateQueryDef("qryDoe
with
Set qdfDoesNotExist = dbs.CreateQueryDef("qryDoe