Access 2013 Programming: How to grab a value from a Text box

I am attaching code to the mouseUp event of a button on a form. I want to send some data to a procedure in a sql database that will validate if the password was correct for the user id entered.  See code below. What am I doing wrong.

Private Sub Command8_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)

Dim UserIDEntered As String
Dim PasswordEntered As String
Dim USER_ID_Returned As Integer
Dim SQLString As String

UserIDEntered = txtUserName.Text   ' this doesn't work, it generates an error
PasswordEntered = "Password1234"

SQLString = "SELECT [dbo].[ValidatePassword] ( '" & UserIDEntered & "', '" & PasswordEntered & "')"

MsgBox (SQLString)]
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.

cipriano555Author Commented:
The ValidatePassword routine will return an positive integer valued userID if the provided password and user id match, otherwise, it returns a -1.  That's the idea.
You have a number of problems I can see:
It would be better to use the OnClick event of the button, that way you can even use a keyboard to fire the event by pressing the Enter key.  

You should used Value and not Text when referring to the contents of the textbox after it has been updated.
UserIDEntered = Me.txtUserName.Value

You don't even have to specify the keyword Value because it is implicitly understood by the compiler.
 UserIDEntered = Me.txtUserName

It is better to use the Me keyword when referring to controls on the form because it helps to quickly identify if one is working with a variable within the scope of the code or to a control on the form.  It also helps you because it invokes Intellisense.

You should name your controls appropriately so that you can tell by looking at the code what control is in use.  btnLogin or cmdVerifyPassword makes more sense then Command8.

Shouldn't you be setting PasswordEntered to Me.txtPassword?  Or where you just testing?

Your SQL string looks to be incomplete.


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
cipriano555Author Commented:
Wow, what a great response!  Yes, I'm a newbie at this and am trying things out   I will incorporate all your suggestions.  Once I construct the query, which returns the user ID (or -1) I actually don't know how to get the program to execute it (to run the query).  So if you know....  :-)

You should explain exactly how you're trying to validate the password.  Are the passwords stored in a local or linked table?  Do they use encryption?  It's simple enough to just do a DLookup or DCount to validate your password.  
If DCount("*","TableName", "[Username]='" & Me.txtUserName & "' AND [Password]='" & Me.txtPassword & "'") > 0 Then
    Msgbox "Validated"
    Msgbox "The Username or Password you entered is incorrect"
End If 

Open in new window

It's best to use some sort of password encryption function; however, all this is not really related to your original question so you should open a new thread.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Referring to your second posted error:

You can refer to the Text property of a control, but that control must have the "focus", so you could do this:

Msgbox Me.YourTextbox.Text

However, referring to it as Ron suggests is a better solution.

Regarding "running" the procedure:

You generally must have a connection to the SQL Server to do this. If you're using Linked Tables, and the link points to the SQL Server, then you should be able to do this:

Dim rst As ADODB.Recordset
Set rst = "EXEC [dbo].[ValidatePassword] ( '" & UserIDEntered & "', '" & PasswordEntered & "')", CurrentProject.Connection

This assumes the ValidatePassword stored proc returns a recordset, of course. If not, you'd have to post the SQL from that Stored Proc so we could determine how best to handle it.

If you're NOT linked to the SQL Tables, then you'd have to build an ADODB.Connection object. If you need to do that, let us know.
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.