Solved

Error 3078

Posted on 2006-11-28
13
787 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:LenaWood
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 18031705
Post the code that attempts to execute strDeptApp.
0
 
LVL 10

Author Comment

by:LenaWood
ID: 18031727
   Set rsApp = db.OpenRecordset(sqlDeptApp)
0
 
LVL 10

Author Comment

by:LenaWood
ID: 18031760
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 34

Expert Comment

by:jefftwilley
ID: 18031772
this is a numeric?   [Forms]![frmEditQuestions]![FunctionQuestionRecID]
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 18031773
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
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 18031778
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
 
LVL 34

Assisted Solution

by:jefftwilley
jefftwilley earned 250 total points
ID: 18031823
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
 
LVL 10

Author Comment

by:LenaWood
ID: 18031844
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18031864
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18031900
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
 
LVL 10

Author Comment

by:LenaWood
ID: 18031985
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
 
LVL 10

Author Comment

by:LenaWood
ID: 18032194
I am wondering if my problem was that I didn't include the ; in my code.  Oh well...it works.  THANK YOU!

Lena
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 18032232
>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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question