Solved

Error 3078 can't find table

Posted on 2010-08-24
22
856 Views
Last Modified: 2013-11-29
When I run the code below I get a 3078 error that it can't find the table or query.  It is happening at this line:
                strSQL = "INSERT INTO tbl_HISTORY( CLIENT_ID ) VALUES (Me.CLIENT_ID)"
                CurrentDb.Execute strSQL1, dbFailOnError

If the answer to the msgbox is yes, than I want it to create a new record in tbl_HISTORY and then display CLI_HISTORY_subform using that new record.  Can anyone tell me why I'm getting this error?
Dim strSQL As String

 

 DoCmd.Hourglass True

 Application.Echo False, "Processing ..."

 With Me

   Select Case tabClients

     Case 0    ' First TabPage (General)

       ' Do nothing. SourceObject loaded.



     Case 1     ' Second TabPage (Academic)

        'Load source objects on Academic tab

        If .stuSchedule_subform.SourceObject <> "frm_STU_SCHEDULE" Then

          .stuSchedule_subform.SourceObject = "frm_STU_SCHEDULE"

        End If

        If .academics_subform.SourceObject <> "frm_STU_ACAD" Then

          .academics_subform.SourceObject = "frm_STU_ACAD"

        End If



     Case 2    ' Third TabPage (History)

        'Find client history.

        If DLookup("CLIENT_ID", "tbl_HISTORY", "CLIENT_ID = " & Me.CLIENT_ID) Then

           .CLI_HISTORY_subform.SourceObject = "frm_CLI_HISTORY"

        Else

           'If no history is available, prompt user to add client history.

           If MsgBox("No client history available.  Would you like to add it now?", vbYesNo) = vbYes Then

                'Insert a new record into in tbl_CLI_HISTORY

                strSQL = "INSERT INTO tbl_HISTORY( CLIENT_ID ) VALUES (Me.CLIENT_ID)"

                CurrentDb.Execute strSQL1, dbFailOnError

                'Find new record and display as subform

                If DLookup("CLIENT_ID", "tbl_HISTORY", "CLIENT_ID = " & Me.CLIENT_ID) Then

                   .CLI_HISTORY_subform.SourceObject = "frm_CLI_HISTORY"

                End If

           End If

        End If

     Case 3

         ' ...

     Case 4

         ' ...

         ' ...

   End Select

 End With

 DoCmd.Hourglass False

Open in new window

0
Comment
Question by:mkelly2384
  • 9
  • 7
  • 3
  • +2
22 Comments
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 50 total points
ID: 33510777
For one thing, you will need to alter the SQL statement builder:strSQL = "INSERT INTO tbl_HISTORY (CLIENT_ID) VALUES (" & Me.CLIENT_ID & ")"
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 33510778
if client_ID is Number

strSQL = "INSERT INTO tbl_HISTORY( CLIENT_ID ) VALUES (" & Me.CLIENT_ID & ")"


if client_ID is Text

strSQL = "INSERT INTO tbl_HISTORY( CLIENT_ID ) VALUES ('" & Me.CLIENT_ID & "')"
0
 
LVL 61

Expert Comment

by:mbizup
ID: 33510784
Assuming a numeric ID, Give this a try:

strSQL = "INSERT INTO tbl_HISTORY( CLIENT_ID ) VALUES (" & Me.CLIENT_ID & ")"
                CurrentDb.Execute strSQL1, dbFailOnError
0
 

Author Comment

by:mkelly2384
ID: 33510795
I corrected the syntax of the INSERT INTO statement, but I still get the same error message.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 33510797
and make sure that the name of table and field names are correct ( do they really have underscore on the name?)

if not you have to enclosed them in square brackets []
0
 

Author Comment

by:mkelly2384
ID: 33510826
Table name and field names are correct.

It says it can't find the input table ".  It's like it's not even reading the table name for some reason.
0
 
LVL 28

Expert Comment

by:omgang
ID: 33510827
Your comment suggests you have the table name wrong in the SQL statement

                'Insert a new record into in tbl_CLI_HISTORY
                strSQL = "INSERT INTO tbl_HISTORY( CLIENT_ID ) VALUES (Me.CLIENT_ID)"

OM Gang
0
 

Author Comment

by:mkelly2384
ID: 33510844
The comment is wrong--I've quadruple-checked the table name in the code. "tbl_HISTORY" is correct.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33510845
mkelly2384,As cap1 mentioned, the table/column names must match exactly.  For example, a stray leading or trailing space in a name can muck things up.Also, please post the exact syntax you tried to use, and confirm whether CLIENT_ID is numeric or text.  It would not shock me if the lack of a space between your table name and the open parenthesis for your column list upsets Access, which is why I included a space there.  (SQL Server would not have "cared" about that.)Patrick
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 33510876
do a compact and repair
tools > database utilities > compact and repair database

then, from VBA window do
Debug > Compile

correct any errors raised

post back the result
0
 

Author Comment

by:mkelly2384
ID: 33510900
I've checked the names of the table and fields 5 times now...

Shouldn't it say "Cannot find the input table "tbl_HISTORY""  instead of Cannot find the input table "  (with one set of quotes only)

I did a compact and repair, but still getting the same error.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33510922
mkelly2384,As I asked in my last previous post:>>Also, please post the exact syntax you tried to use, and confirm whether CLIENT_ID is numeric or text.Patrick
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 33510929
how about the DEBUG > CoMPILE?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 33510942
the CLIENT_ID is number from the codes above

       If DLookup("CLIENT_ID", "tbl_HISTORY", "CLIENT_ID = " & Me.CLIENT_ID) Then
           .CLI_HISTORY_subform.SourceObject = "frm_CLI_HISTORY"
        Else
0
 

Author Comment

by:mkelly2384
ID: 33510943
Oh sorry...CLIENT_ID is numeric.

Is there a better way to do this?
Private Sub tabClients_Change()



On Error GoTo tabClients_Change_Err

 Dim strSQL As String

 

 DoCmd.Hourglass True

 Application.Echo False, "Processing ..."

 With Me

   Select Case tabClients

     Case 0    ' First TabPage (General)

       ' Do nothing. SourceObject loaded.



     Case 1     ' Second TabPage (Academic)

        'Load source objects on Academic tab

        If .stuSchedule_subform.SourceObject <> "frm_STU_SCHEDULE" Then

          .stuSchedule_subform.SourceObject = "frm_STU_SCHEDULE"

        End If

        If .academics_subform.SourceObject <> "frm_STU_ACAD" Then

          .academics_subform.SourceObject = "frm_STU_ACAD"

        End If



     Case 2    ' Third TabPage (History)

        'Find client history.

        If DLookup("CLIENT_ID", "tbl_HISTORY", "CLIENT_ID = " & Me.CLIENT_ID) Then

           .CLI_HISTORY_subform.SourceObject = "frm_CLI_HISTORY"

        Else

           'If no history is available, prompt user to add client history.

           If MsgBox("No client history available.  Would you like to add it now?", vbYesNo) = vbYes Then

                'Insert a new record into in tbl_HISTORY

                strSQL = "INSERT INTO tbl_HISTORY (CLIENT_ID) VALUES (" & Me.CLIENT_ID & ")"

                CurrentDb.Execute strSQL1, dbFailOnError

                'Find new record and display as subform

                If DLookup("CLIENT_ID", "tbl_HISTORY", "CLIENT_ID = " & Me.CLIENT_ID) Then

                   .CLI_HISTORY_subform.SourceObject = "frm_CLI_HISTORY"

                End If

           End If

        End If

     Case 3

         ' ...

     Case 4

         ' ...

         ' ...

   End Select

 End With

 DoCmd.Hourglass False

Open in new window

0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 400 total points
ID: 33510998
try this



rivate Sub tabClients_Change()



On Error GoTo tabClients_Change_Err

 Dim strSQL As String

 

 DoCmd.Hourglass True

 Application.Echo False, "Processing ..."

 With Me

   Select Case tabClients

     Case 0    ' First TabPage (General)

       ' Do nothing. SourceObject loaded.



     Case 1     ' Second TabPage (Academic)

        'Load source objects on Academic tab

        If .stuSchedule_subform.SourceObject <> "frm_STU_SCHEDULE" Then

          .stuSchedule_subform.SourceObject = "frm_STU_SCHEDULE"

        End If

        If .academics_subform.SourceObject <> "frm_STU_ACAD" Then

          .academics_subform.SourceObject = "frm_STU_ACAD"

        End If



     Case 2    ' Third TabPage (History)

        'Find client history.

        If DLookup("CLIENT_ID", "tbl_HISTORY", "CLIENT_ID = " & Me.CLIENT_ID) Then

           .CLI_HISTORY_subform.SourceObject = "frm_CLI_HISTORY"

        Else

           'If no history is available, prompt user to add client history.

           If MsgBox("No client history available.  Would you like to add it now?", vbYesNo) = vbYes Then

                'Insert a new record into in tbl_HISTORY

             

               with currentdb.openrecordset("tbl_History")

                    .addnew

                    !Client_ID=Me.Client_ID

                    .update

               end with



               ' strSQL = "INSERT INTO tbl_HISTORY (CLIENT_ID) VALUES (" & Me.CLIENT_ID & ")"

               ' CurrentDb.Execute strSQL1, dbFailOnError

                'Find new record and display as subform

                If DLookup("CLIENT_ID", "tbl_HISTORY", "CLIENT_ID = " & Me.CLIENT_ID) Then

                   .CLI_HISTORY_subform.SourceObject = "frm_CLI_HISTORY"

                End If

           End If

        End If

     Case 3

         ' ...

     Case 4

         ' ...

         ' ...

   End Select

 End With

 DoCmd.Hourglass False

Open in new window

0
 

Author Comment

by:mkelly2384
ID: 33510999
Decompile shows no errors.

Rather than inserting, I first tried to do something like this:
DoCmd.SelectObject "CLI_HISTORY_subform"
DoCmd.GoToRecord acActiveDataObject, , acNewRec

but that didn't work.  Didn't know if I could create a new record in a subform anyway.  Would that be better than inserting if it was done right?
0
 

Author Comment

by:mkelly2384
ID: 33511022
Yay--capricorn1--you're a genius!  Thanks so much--worked perfectly.
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 50 total points
ID: 33511058
>>               CurrentDb.Execute strSQL1, dbFailOnError
I believe this is incorrect.

You need to make the syntactical adjustments suggested to the insert statement and change your Exectute statement to this to be consistent:

CurrentDb.Execute strSQL, dbFailOnError


(It looks like you are setting strSQL, but attempting to run strSQL1)

Also place the following line at the top under Option Compare Database of your code module if you don't have it already:

Option Explicit

That will trap any undeclared variables as compile errors,  which is very helpful in debugging things.
0
 

Author Comment

by:mkelly2384
ID: 33511076
oh, yeah, I didn't catch that strSQL1 issue.  Thanks!
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 33511535


             
               with currentdb.openrecordset("tbl_History")
                    .addnew
                    !Client_ID=Me.Client_ID
                    .update
                   
                    .close  'Add this line

               end with
0
 

Author Comment

by:mkelly2384
ID: 33511606
done. thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now