Solved #Name? Error (1 Viewer)

PatAccess

Registered User.
Local time
Today, 06:03
Joined
May 24, 2017
Messages
209
Hello Guys,
I am trying to retrieve a value through a SQL statement and place it into an unbound text-field. When I run the program, the immediate window shows me the correct information but the text-field shows (#Name?) What could be the problem?
Here is my code
Code:
Private Sub cmdViewLogin_Click()
Dim PElogin As String
Dim strComments As String

strComments = "SELECT [Comments] FROM TblPEComments WHERE [Employee]='" & Me.Employee & "' And StateRegistered= '" & Me.StateID & "' And RegistrationNo='" & Me.RegistrationNo & "'"
Debug.Print strComments

PElogin = InputBox("Please Enter Password", "PE Login")
If PElogin = "password" Then
    MsgBox "Thank You"
    Me.Commentstxt.Visible = True
    Me.Commentstxt.ControlSource = strComments
Else
    MsgBox "Invalid Password!" & vbNewLine & "Please Contact Administrator" & vbNewLine, vbCritical
Exit Sub
End If

End Sub

Thank you for your help 😕
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:03
Joined
Oct 29, 2018
Messages
13,187
Hi. The Control Source property can only accept a field name or an expression. You can't use a SQL statement in it. Maybe try using DLookup() instead.
 

June7

AWF VIP
Local time
Today, 02:03
Joined
Mar 9, 2014
Messages
3,362
VBA can be eliminated with DLookup in textbox:
=DLookup("[Comments]", "TblPEComments", "[Employee]='" & [Employee] & "' And StateRegistered= '" & [StateID] & "' And RegistrationNo='" & [RegistrationNo] & "'")

If field is a number type, don't use apostrophe delimiters.
 

PatAccess

Registered User.
Local time
Today, 06:03
Joined
May 24, 2017
Messages
209
VBA can be eliminated with DLookup in textbox:
=DLookup("[Comments]", "TblPEComments", "[Employee]='" & [Employee] & "' And StateRegistered= '" & [StateID] & "' And RegistrationNo='" & [RegistrationNo] & "'")

If field is a number type, don't use apostrophe delimiters.
Thank you Guys. The DLookup worked!
 

PatAccess

Registered User.
Local time
Today, 06:03
Joined
May 24, 2017
Messages
209
Hi. The Control Source property can only accept a field name or an expression. You can't use a SQL statement in it. Maybe try using DLookup() instead.
Thank you for the information. I did know this about the Control Source property.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:03
Joined
Oct 29, 2018
Messages
13,187
Thank you for the information. I did know this about the Control Source property.
Hi. Glad to hear you got it sorted out. I sometimes use a one row listbox, so I can use a SQL statement. Good luck with your project.
 

Users who are viewing this thread

Top Bottom