VBA Error message "Object Required" in Access Database

I have a piece of code I am using to add information entered on a form to a table. When I try to run it, I get an error message that says "Compile Error: Object Required" and it highlights the line "

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

Open in new window


I think that the piece of code after the equal sign might be the problem, but I don't know what a better way to do this would be.

Here is the larger piece of code:
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"

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

rst.AddNew
    rst!TeamName = TNAME
rst.Update

Set rst = Nothing
Set db = Nothing

Exit Sub

End Sub

Open in new window


Any help will be greatly appreciated.
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.

Rey Obrero (Capricorn1)Commented:
try changing Form with Forms

Set TNAME = Forms!frm_Staff.Form!FName & " " & Forms!frm_Staff.Form!Lname
0
Anthony BerenguelCommented:
try this
et TNAME = Forms!frm_Staff!FName & " " & Forms!frm_Staff!Lname"

Open in new window

this is assuming that form "frm_Staff" is the parent form and not a subform.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Remove Set


TNAME = Form!frm_Staff.Form!FName & " " & Form!frm_Staff.Form!Lname
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
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:
Set TNAME = Forms!frm_Staff!FName & " " & Forms!frm_Staff!Lname"
0
Anthony BerenguelCommented:
Yeah, DatabaseMX is right.

So, try...
TNAME = Forms!frm_Staff!FName & " " & Forms!frm_Staff!Lname

Open in new window

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
TNAME  is not an Object .... it's a String Variable per

Dim TNAME As String

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Anthony Berenguel

You just repeated exactly what I said :-)
0
Rey Obrero (Capricorn1)Commented:
(edit) are you using a subforms ? if not

try changing Form with Forms

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

if you are using a subform

her is the syntax

 Forms![NameOfMainForm]![NameOfSubformControl].Form!FName
0
MeginAuthor Commented:
Wow! I can't believe how many responses I just got!

Thank you so much.

The error message I was getting is not happening now. Why am I not supposed to use "Set" in this case?

Now I have a new error message, but it is about a different line of code. I will post it next.

THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
" Why am I not supposed to use "Set" in this case?"

Because Set is only for Object Variables.  TNAME was dimmed as a String Variable, and does not require Set.

Set Example:

Dim rst As DAO.Recordset  ' (rst is an Object variable because RecordSet is an Object by definition)
Set rst = CurrentDB.OpenRecordset ("SomeTableOrQueryName", dbOpenDynaset)

' more code

' cleanup routine - typical
rst.Close
Set rst = Nothing ' Cleanup - always do this when exiting your code
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Simplified version:

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"

   Set rst = CurrentDb.OpenRecordset(strsql)
   With rst
       .AddNew
       ![TeamName] = TNAME
       .Update
      .Close
    End With  'rst

Set rst = Nothing

End Sub
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.