Error 3078

What is wrong with this SQL statement:

    strDeptApp = "SELECT tblDepartApplicability.FunctionQuestionRecID, " _
      & "luDepartments.DepartmentName FROM tblDepartApplicability INNER JOIN " _
      & "luDepartments ON tblDepartApplicability.DepartRecID = " _
      & "luDepartments.DepartRecID WHERE tblDepartApplicability.FunctionQuestionRecID = " _
      & [Forms]![frmEditQuestions]![FunctionQuestionRecID] _
      & " ORDER BY tblDepartApplicability.FunctionQuestionRecID, " _
      & "luDepartments.DepartmentName"

I am getting runtime error 3078.

The Microsoft Jeft database engine cannot find the input table or query ".

Thanks for your assistance!
Lena
LVL 10
LenaWoodAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Post the code that attempts to execute strDeptApp.
0
 
LenaWoodAuthor Commented:
   Set rsApp = db.OpenRecordset(sqlDeptApp)
0
 
LenaWoodAuthor Commented:
This is the complete code behind the button:

Private Sub cmdAdd_Click()

    Dim db As DAO.Database
    Dim rsApp As DAO.Recordset
    Dim strDeptApp As String
    Dim sqlDeptApp As String
   
    Set db = CurrentDb
   
    strDeptApp = "SELECT tblDepartApplicability.FunctionQuestionRecID, " _
      & "luDepartments.DepartmentName FROM tblDepartApplicability INNER JOIN " _
      & "luDepartments ON tblDepartApplicability.DepartRecID = " _
      & "luDepartments.DepartRecID WHERE tblDepartApplicability.FunctionQuestionRecID = " _
      & [Forms]![frmEditQuestions]![FunctionQuestionRecID] _
      & " ORDER BY tblDepartApplicability.FunctionQuestionRecID, " _
      & "luDepartments.DepartmentName"
   
    Set rsApp = db.OpenRecordset(sqlDeptApp)
   
    Debug.Print rsApp.RecordCount
   
    If Not IsNull(Me.FunctionQuestionRecID) Then
        If Not IsNull(Me.FunctionalAreaID) And Me.FunctionQuestionRecID <> "" Then
            If Not IsNull(Me.FunctionalQuestion) Then
               
                MyTrackChanges "New Question Added", Me.FunctionQuestionRecID, Null, _
                  Null, "A new question has been added." & vbCrLf & vbCrLf _
                  & "Functional Area:  " & Me.FunctionalAreaID.Column(1) & vbCrLf _
                  & "Functional Category:  " & Nz(Me.FunctionalAreaCategoryID.Column(1), Null) & vbCrLf _
                  & "Reference(s):  " & Me.FunctionalReference, True

                DoCmd.Close acForm, "frmEditQuestions"
                DoCmd.OpenForm "frmMainMenu"
            Else
                MsgBox "You can not save the question before entering a question."
                Exit Sub
            End If
        Else
            MsgBox "Your question must be assigned to a Functional Area before " _
              & "it can be saved"
            Me.FunctionalAreaID.SetFocus
            Exit Sub
        End If
    Else
        DoCmd.Close acForm, "frmEditQuestions"
        DoCmd.OpenForm "frmMainMenu"
    End If

End Sub

What I am trying to do eventually is create a list of departments that the Question has been assigned too.  I will loop through the records found (if any) and put together a list of departments seperated by commas to include in MyTrackChanges.

Thanks!
Lena
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
jefftwilleyCommented:
this is a numeric?   [Forms]![frmEditQuestions]![FunctionQuestionRecID]
0
 
rockiroadsCommented:
well looking at the error msg, does the two tables u are using both exist?

tblDepartApplicability
luDepartments

?

dump the sql

debug.print sqlDeptApp

then look in immediate window and try running the sql, it will help u pick up on any errors
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Dim db as Database
Set db = CurrentDb()

Dim rsApp as DAO.Recordset
Dim strDeptApp as string

strDeptApp = "SELECT da.FunctionQuestionRecID, ld.DepartmentName "
strDeptApp = strDeptApp & "FROM tblDepartApplicability da "
strDeptApp = strDeptApp & "INNER JOIN luDepartments ld ON da.DepartRecID = ld.DepartRecID "
strDeptApp = strDeptApp & "WHERE da.FunctionQuestionRecID = "   & [Forms]![frmEditQuestions]![FunctionQuestionRecID] & " " 
strDeptApp = strDeptApp & "ORDER BY da.FunctionQuestionRecID, ld.DepartmentName;"

Set rsApp = db.OpenRecordset strDeptApp

This assumes that the form you mention is open, and it has a numeric value, and da.FunctionQuestionRecID is numeric
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
jefftwilleyCommented:
since you're on that form...use me.?

strDeptApp = "SELECT tblDepartApplicability.FunctionQuestionRecID," & _
      " luDepartments.DepartmentName FROM tblDepartApplicability INNER JOIN" & _
      " luDepartments ON tblDepartApplicability.DepartRecID =" & _
      " luDepartments.DepartRecID WHERE" & _
      " tblDepartApplicability.FunctionQuestionRecID = " & Me.FunctionQuestionRecID & " ORDER BY" & _
      " tblDepartApplicability.FunctionQuestionRecID, luDepartments.DepartmentName;"
0
 
LenaWoodAuthor Commented:
Jeff - Yes it is a number.

Rocky - Yes, the tables exist.  Here is what I get when I debug.print the sqlDeptApp:

SELECT tblDepartApplicability.FunctionQuestionRecID, luDepartments.DepartmentName FROM tblDepartApplicability INNER JOIN luDepartments ON tblDepartApplicability.DepartRecID = luDepartments.DepartRecID WHERE tblDepartApplicability.FunctionQuestionRecID = 3251 ORDER BY tblDepartApplicability.FunctionQuestionRecID, luDepartments.DepartmentName

The sql runs fine in a query.

Jim I will give your suggestion a try.

Lena

 
0
 
jefftwilleyCommented:
this might serve if the value is a null?

Dim MyFQID As Long
MyFQID = Nz(Me.functionQuestionRecID.Value, 0)

 strDeptApp = "SELECT tblDepartApplicability.FunctionQuestionRecID," & _
      " luDepartments.DepartmentName FROM tblDepartApplicability INNER JOIN" & _
      " luDepartments ON tblDepartApplicability.DepartRecID =" & _
      " luDepartments.DepartRecID WHERE" & _
      " tblDepartApplicability.FunctionQuestionRecID = " & Me.functionQuestionRecID & " ORDER BY" & _
      " tblDepartApplicability.FunctionQuestionRecID, luDepartments.DepartmentName;"
0
 
jefftwilleyCommented:
Sorry Lena, I keep getting called away...and I think Jim's suggestion already accounts for null but...

Dim MyFQID As Long
MyFQID = Nz(Me.functionQuestionRecID.Value, 0)

 strDeptApp = "SELECT tblDepartApplicability.FunctionQuestionRecID," & _
      " luDepartments.DepartmentName FROM tblDepartApplicability INNER JOIN" & _
      " luDepartments ON tblDepartApplicability.DepartRecID =" & _
      " luDepartments.DepartRecID WHERE" & _
      " tblDepartApplicability.FunctionQuestionRecID = " & MyFQID & " ORDER BY" & _
      " tblDepartApplicability.FunctionQuestionRecID, luDepartments.DepartmentName;"
0
 
LenaWoodAuthor Commented:
Jim...your solution seems to work although I am not sure what is different other than you gave the tables aliases.  Am I missing something that you changed?  I have been fighting this code for so long my eyes are blurry.

Lena
0
 
LenaWoodAuthor Commented:
I am wondering if my problem was that I didn't include the ; in my code.  Oh well...it works.  THANK YOU!

Lena
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Jim...your solution seems to work although I am not sure what is different other than you gave the tables aliases.
Er, actually nothing was different, other than my assumptions, and a possible smart-alec comment on my end like 'readability would help'.  
I was kinda hoping that maybe you mis-spelled something, and that my edit would resolve it.

Thanks for the grade, good luck with your project.  -Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.