Access - Error says it can't find input table or query

I have some code that is giving me this error whenever I try to run it:
"Run-time error '3078': The Microsoft Access database engine cannot find the input table or query". Make sure it exists and that its name is spelled correctly."

I have checked the table it references and the name is correct.

Is there something wrong in the code I have written?

Private Sub btn_AddtoTeam_Click()
Dim db As DAO.Database
Dim rst As Recordset
Dim strsql As String
Dim TNAME As String

Set db = CurrentDb

Set rst = db.OpenRecordset(strsql)

strsql = "Select Top 1 * from Team"


TNAME = Forms!frm_Staff!FName & " " & Forms!frm_Staff!Lname

rst.AddNew
    rst!TEAMName = TNAME
rst.Update

Set rst = Nothing
Set db = Nothing

Exit Sub

End Sub

Open in new window

MeginAsked:
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.

Anthony BerenguelCommented:
What line does it error on? Have you spelled the table name/ query correctly?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Reverse these two statements
Set rst = db.OpenRecordset(strsql)

strsql = "Select Top 1 * from Team"

to
strsql = "Select Top 1 * from Team"

Set rst = db.OpenRecordset(strsql)
0

Experts Exchange Solution brought to you by

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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You were referring to strsql before it was defined :-)
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.

Anthony BerenguelCommented:
try this
Private Sub btn_AddtoTeam_Click()
Dim db As DAO.Database
Dim rst As Recordset
Dim strsql As String
Dim TNAME As String

Set db = CurrentDb
strsql = "Select Top 1 * from Team"
Set rst = db.OpenRecordset(strsql)




TNAME = Forms!frm_Staff!FName & " " & Forms!frm_Staff!Lname

rst.AddNew
    rst!TEAMName = TNAME
rst.Update

Set rst = Nothing
Set db = Nothing

Exit Sub

End Sub

Open in new window

0
Anthony BerenguelCommented:
Sorry, joe! I wasn't trying to step on toes!
0
MeginAuthor Commented:
Woohooo!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!  I WORKS!!!!!


THANK YOU THANK YOU THANK YOU!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
See my post in your previous Q re simplifying this code ...

mx
0
MeginAuthor Commented:
I just looked closer at it.
(I get really excited when things actually work, and sometimes I glaze over what is said afterwards.)

I have a few questions:

You suggested I remove the Dim statement declaring that db is dao.database. Can I remove that from other pieces of code I use in my Access database? Or, rather, how come I don't need it?

When I use the With statement, does that just mean that it implied that I mean to put "rst" in front of everything that comes after it? I just want to verify because it looks like that is exactly what you did.

With rst
        .AddNew
        ![TeamName] = TNAME
        .Update
        .Close
     End With  'rst

Open in new window

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"When I use the With statement, does that just mean that it implied that I mean to put "rst" in front of everything that comes after it? I"

Exactly.  Saves a LOT of typing ... and Intellisense still works  ... and technically, it executes faster.



"Can I remove that from other pieces of code I use in my Access database? "

The answer is ... "it depends"

In the case above, there is really only one place where CurrentDB needs to be used/referenced ... which is on the OpenRecordSet statement.  So, no need to Dim an Object variable and have code to Set in and the cleanup code.

However, if you have a routine where you need to use CurrentDB several places in the code, then you will want to Dim an Object Variable ... and use it instead of continually referring to CurrentDB ... which would technically be a performance hit.

OR ... you can also do

With CurrentDB
      ' code which refers to CurrentDB several times (and other code - whatever)

End With

For example (although this example is overkill)

Private Sub btn_AddtoTeam_Click()

Dim rst As DAO.Recordset
Dim strsql As String
Dim TNAME As String

TNAME = Form!frm_Staff.Form!FName & " " & Form!frm_Staff.Form!Lname
strsql = "Select Top 1 * from Team"

With CurrentDB

  Set rst = .OpenRecordset(strsql)   ' CurrentDB is implied here
   With rst
       .AddNew
       ![TeamName] = TNAME
       .Update
      .Close
    End With   'rst

End With   'CurrentDB

Set rst = Nothing

End Sub


BTW ... this cannot be working either:

TNAME = Form!frm_Staff.Form!FName & " " & Form!frm_Staff.Form!Lname

s/b

TNAME = Forms!frm_Staff.Form!FName & " " & Forms!frm_Staff.Form!Lname
0
MeginAuthor Commented:
I am going to print this for reference.

Thank you for taking the time to explain all of this to me!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
No problem and you are welcome.

Where are you located btw ?
0
MeginAuthor Commented:
I am in Seattle. You?
0
MeginAuthor Commented:
I have another question about this that may complicate things. Do you mind if I ask you here, or would it be better if I opened another question?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Rancho Cucamonga CA :-)
(same time zone at least)

I will be in Seattle (Bellevue/Redmond) in Nov for the Microsoft Global MVP Summit.

Another Q ...
I don't mind but ... per EE rules, you should open another Q ... just to keep it on the up and up :-)
0
MeginAuthor Commented:
I just put up the second next question.

I used to live near Rancho Cucamonga!  Seattle has much nicer weather this time of year. ;)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.