• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1871
  • Last Modified:

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.
0
Megin
Asked:
Megin
  • 5
  • 3
  • 2
  • +1
1 Solution
 
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 MVP, Access and Data Platform)Commented:
Remove Set


TNAME = Form!frm_Staff.Form!FName & " " & Form!frm_Staff.Form!Lname
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 MVP, Access and Data Platform)Commented:
TNAME  is not an Object .... it's a String Variable per

Dim TNAME As String

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
" 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 MVP, Access and Data Platform)Commented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now