Username/Password with vba error: "Method or data member not found"

Hi guys,

I have an access database with a column for usernames and a column for passwords.
I also designed a userform on excel vba with an input box for usernames and an input box for password.

After quite a bit of googling, i've ended up with this code:

Private Sub CommandButton1_Click()

    Dim Username As String
    Username = txtUserNameIn.Text
    Dim password As String
    password = txtPasswordIn.Text
     
    If IsNull(Me.txtUserNameIn) Or Me.txtUserNameIn = "" Then
        MsgBox "You must enter your username.", vbOKOnly, "Required Data"
        Me.txtUserNameIn.SetFocus
        Exit Sub
    End If
     
    
    If IsNull(Me.txtPasswordIn) Or Me.txtPasswordIn = "" Then
        MsgBox "You must enter your Password (case sensitive).", vbOKOnly, "Required Data"
        Me.txtPasswordIn.SetFocus
        Exit Sub
    End If
     
    
    Dim temp As String
On Error Resume Next
temp = WorksheetFunction.VLookup(Me.txtUserNameIn.Value, Range("UserRegister"), 1, 0)
 
If Username = temp Then
     
    Err.Clear
    temp = ""
    temp = WorksheetFunction.VLookup(Me.txtUserNameIn.Value, Range("UserRegister"), 2, 0)
    On Error GoTo 0
    If password = temp Then
        Sheets("ADMIN").Visible = xlSheetVisible
        MsgBox "Password & Username Accepted"
        Unload Me
        Sheets("ADMIN").Select
        Range("A1").Select
    Else
        Sheets("ADMIN").Visible = xlVeryHidden
        MsgBox "Username & Password Combination Not Accepted, Workbook will close"
        Unload Me
            
        End If
    End If
     


End Sub

But I get the error "Method or data member not found".

I am quite new to this so any help is welcomed, thank you !

Hi,

Where does the error occur?

hard to say what the problem might be just by looking at it..

Thanks
Paul S.

Hi,

The error highlights in yellow

Private Sub CommandButton1_Click()

but also highlights in blue

.txtUsernameIn

Would it maybe be easier if i attached my file ?

thank you for your help !

I've renamed my textbox with the code

Private Sub TextBox9_Change()
Textbox9.Text=Me.txtUsernameIn
End Sub

Can i not change the name of it like that ?

I've also blocked the change event

Public mychg As string
Sub Mysub()
mychg="off"
End Sub

Stopping the change event has not caused any problems with other textboxes.


The 2 input boxes for username and password are textbox9_Change() and textbox10_Change()

Yes that works thank you !
However that data is located in an access database and so the range

Range("UserRegister"), 2, 0)

is not valid.

I have already written a code in a module to connect access and excel, so how would i use declare that the usernames and passwords are in the database?

I have quite a few big tables in access and this is part of a uni project where we have to keep our databases in access. So don't really have a choice.

To connect both excel and access i've used the code:

Function QueryData(Src, OutputRange)
Dim cntStudConnection As ADODB.Connection

Set cntStudConnection = New ADODB.Connection
cntStudConnection.Provider = "Microsoft.Ace.OLEDB.12.0"
cntStudConnection.Open "C:\Users\Hala\Documents\BBB\Dummydata1.accdb"

Set rstNewQuery = New ADODB.Recordset
rstNewQuery.Open Source:=Src, ActiveConnection:=cntStudConnection
Do Until rstNewQuery.EOF
For Each rstFld In rstNewQuery.Fields
msg = msg & " : " & rstFld.Value
Next
msg = msg & vbNewLine
rstNewQuery.MoveNext
Loop
UserForm8.TextBox1.Text = msg

Set rstNewQuery = Nothing
cntStudConnection.Close
Set cntStudConnection = Nothing

End Function

I am creating a blood bank management system.
All my data is in access, and all userforms are created in excel.

The code i used further up is to create an SQL query from excel and retrieve values from access.

Would the same code work in my case ?
or should i remove

Do Until rstNewQuery.EOF
For Each rstFld In rstNewQuery.Fields
msg = msg & " : " & rstFld.Value
Next
msg = msg & vbNewLine
rstNewQuery.MoveNext
Loop

Im not sure what you mean by SQL string ?

would it be easier if i attached my excel and access file ?

Unfortunately i do as it is a requirement given by the professor for the project

Im not sure i understand what you mean by "to return the password for the username entered" ?

yes but i can't find a way to make excel check if the username and passwords match the access database

A workbook of what ?

Is this your uni project or did you steal it

it looks like you've just pulled a bunch of files from somewhere and glued them all together, you are miles away from getting this working because not even your control names are lining up with the code, which makes me think (hush hush) are you cheating .........

Your going to have get a few things sorted and some information on a piece of paper before you will finish this or even get it working ............

If your code says 'Control1.text' you better have a control called 'Control1'.... not only that it needs a 'Text' property (likely a textbox, but could be a few things), what are they teaching in uni these days?

Your using a database and yet you dont understand what a query string is...hmm OK maybe you use the wizards (excel/access), Look in your code (you posted above) 'Function QueryData(Src, OutputRange)' The Src here is going to have a query string or some procedure call, you need to see what is passed through this variable...
if its "Select..........." then this is the SQL string. you need to write is down somewhere and list all the names between SELECT....FROM, these are columns of data, they are seperated with a comma, if you see SELECT Tbl.Col As NewCol FROM... this is Field (Col) but its being renamed to Field (NewCol).......

Now you can link all the data to your forms, sheets whatever easily you now have all the names corrected and a list of fields to map.......

no point doing anything until this part is fixed..


Paul S.

Hi Paul,

Thank you for your help !
I did not write this code, we were only taught the very basics then asked to create a management system so i've been looking online for codes.

I will try what you suggested and get back to you !

ok thank you, i will try this
and yes i know how to write basic queries

I do know how to write SQL queries though

Well i have to write this project with the requirements I am given.

I know i am new at this but i do believe that I have the basic knowledge to do it.
I will go through what you guys have posted for me and try to sort it out.

Thank you for your help

Anyone going bald :D

Lets take a step back for a moment and concentrate on the Excel Form.........

FrmLogin (let say)

From all the info you have given we only need 3-4 things on this form
1) txtUsername = TextBox
2) txtPassword = Textbox
3) cmdLogin = CommandButton
4) cmdCancel = CommandButton


NEXT......

Now your trying to do lookups and use ranges which you need data on a worksheet to do (looking at your first code snip)..........so your trying to dump the data from your username database(access) into a sheet first and then do a lookup from the sheet is that right?
(not ideal in a real world scenario i must say haha)

Really this part needs to be clarified, what is your goal.......Load the database into a table.....before or after the OK button is clicked......or are you trying to send the username/password to the database and get an OK/NOTOK response...........

these answers lead to COMPLETELY different Things that need to be done for all this to work......so let us know

I am trying to input a username and password in input boxes and if it matches a value in my database, then continue on to the next userform when i click the command button. If not then close all userforms

JapanDave really wants to know what your query string is..... please have a look, its very important

I made a mistake posting the second code like that.
I did not mean that it was what i was using for the username/password login but how i connected excel and access for another query.


We were given the following code by our professor to connect them together and i re-used that for my other query, while changing a few lines to adapt it.

Function QueryData(Src, OutputRange)
dbUnivInfo = ThisWorkbook.Path &
"\UniversityInformationSystem.mdb"
Set cntStudConnection = New ADODB.Connection
CnctSource = "Provider=Microsoft.Jet.OLEDB.4.0;
Set cntStudConnection = New ADODB.Connection
CnctSource = "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=" &
dbUnivInfo & ";“ cntStudConnection.Open ConnectionString:=CnctSource
Set rstNewQuery = New ADODB.Recordset
rstNewQuery.Open Source:=Src, ActiveConnection:=cntStudConnection
Range(OutputRange).CopyFromRecordset rstNewQuery
Set rstNewQuery = Nothing
cntStudConnection.Close
Set cntStudConnection = Nothing
End Function

The first code i posted was an attempt at giving constraints to the username/password login
i have a userform with 2 textboxes (9 and 10) and a command button.

OK so this code is pulling some data from the database file and its putting it into a namedRange called OutputRange

so if your going to use this actual code then what you will do is.....

First make sure you have a named range called 'OutputRange' on some sheet.....Do you have a named range called OutputRange?

Next once this is clarified then in your button_click event
1st, check is username and password are blanks etc etc, not point doing anything if they are blanks
2nd, run the query above (WITH THE CORRECT QUERY STRING, probably something like "SELECT username, Password FROM tblLoginData"
3rd, Just comment out all your other code for now, just check the textboxes and run that query and post back with what it looks like.

if your teacher gave you something like this query string, post it here so we know how he expects you do do this............otherwise just download the full table to the output range and we will go from there.....

I know that src is meant to be the query but i can't seem to figure out which query would check the username and password of the database

why dont you just put all your queries here and let us have a look

Ok. I have 2 userforms and 1 module.

userform with inputboxes for username/password + command button:

Private Sub CommandButton1_Click()

    Dim Username As String
    Username = TextBox9.Text
    Dim password As String
    password = TextBox10.Text
     
    If IsNull(Me.TextBox9.Text) Or Me.TextBox9.Text = "" Then
        MsgBox "You must enter your username.", vbOKOnly, "Required Data"
        Me.TextBox9.SetFocus
        Exit Sub
    End If
     
    
    If IsNull(Me.TextBox10.Text) Or Me.TextBox10.Text = "" Then
        MsgBox "You must enter your Password (case sensitive).", vbOKOnly, "Required Data"
        Me.TextBox10.SetFocus
        Exit Sub
    End If
     
    
    Dim temp As String
On Error Resume Next
temp = WorksheetFunction.VLookup(Me.TextBox9.Value, Range("UserRegister"), 1, 0)
 
If Username = temp Then
     
    Err.Clear
    temp = ""
    temp = WorksheetFunction.VLookup(Me.TextBox9.Value, Range("UserRegister"), 2, 0)
    On Error GoTo 0
    If password = temp Then
        Sheets("ADMIN").Visible = xlSheetVisible
        MsgBox "Password & Username Accepted"
        Unload Me
        Sheets("ADMIN").Select
        Range("A1").Select
    Else
        Sheets("ADMIN").Visible = xlVeryHidden
        MsgBox "Username & Password Combination Not Accepted, Workbook will close"
        Unload Me
            
        End If
    End If
     

Call Module1.QueryData1("SELECT UserName,Password FROM data", OutputRange)

End Sub

userform with input box that i named outputrange

Private Sub TextBox1_Change()
TextBox1.Text = "OutputRange"

End Sub

And module

Function QueryData1(Src, OutputRange)

Dim cntStudConnection As ADODB.Connection

Set cntStudConnection = New ADODB.Connection
cntStudConnection.Provider = "Microsoft.Ace.OLEDB.12.0"
cntStudConnection.Open "C:\Users\Hala\Documents\BBB\Dummydata1.accdb"

Set rstNewQuery = New ADODB.Recordset
rstNewQuery.Open Source:=Src, ActiveConnection:=cntStudConnection
Range(OutputRange).CopyFromRecordset rstNewQuery

Set rstNewQuery = Nothing
cntStudConnection.Close
Set cntStudConnection = Nothing

End Function

My database table is called "data" and the columns are username and password

yay a connection string joke......

Its a little backwards in the way all this code is working and there are a couple of gaps

you need to be calling/getting the userdata before you try looking at the data

Call Module1.QueryData1("SELECT UserName,Password FROM data", OutputRange)

The OutputRange here is coded like a variable.........to you have a Const OutputRange as String = "somename" somewhere in your module or is there any other code, if not this should be changed to a string, some range like "A1:B100" or the name of a namedrange


you really need to comment out all the red code and test whatst left
or send your workbook over

Private Sub CommandButton1_Click()

    Dim Username As String
    Username = TextBox9.Text
    Dim password As String
    password = TextBox10.Text
     
    If IsNull(Me.TextBox9.Text) Or Me.TextBox9.Text = "" Then
        MsgBox "You must enter your username.", vbOKOnly, "Required Data"
        Me.TextBox9.SetFocus
        Exit Sub
    End If
     
    
    If IsNull(Me.TextBox10.Text) Or Me.TextBox10.Text = "" Then
        MsgBox "You must enter your Password (case sensitive).", vbOKOnly, "Required Data"
        Me.TextBox10.SetFocus
        Exit Sub
    End If
     
    
    Dim temp As String
On Error Resume Next
temp = WorksheetFunction.VLookup(Me.TextBox9.Value, Range("UserRegister"), 1, 0)
 
If Username = temp Then
     
    Err.Clear
    temp = ""
    temp = WorksheetFunction.VLookup(Me.TextBox9.Value, Range("UserRegister"), 2, 0)
    On Error GoTo 0
    If password = temp Then
        Sheets("ADMIN").Visible = xlSheetVisible
        MsgBox "Password & Username Accepted"
        Unload Me
        Sheets("ADMIN").Select
        Range("A1").Select
    Else
        Sheets("ADMIN").Visible = xlVeryHidden
        MsgBox "Username & Password Combination Not Accepted, Workbook will close"
        Unload Me
            
        End If
    End If
     

Call Module1.QueryData1("SELECT UserName,Password FROM data", OutputRange)

End Sub

Book1 (Autosaved).xlsm

this is my workbook

Try this code as a test, it should connect to the database and put all the results into a new sheet, just scrap your old code for now, put it somewhere else.

Private Sub CommandButton1_Click()
 
Call Module1.QueryData1("SELECT UserName,Password FROM data", "A1:B100")

End Sub

Function QueryData1(Src, OutputRange)

Dim Sht as Excel.WorkSheet

Dim cntStudConnection As ADODB.Connection

Set cntStudConnection = New ADODB.Connection
cntStudConnection.Provider = "Microsoft.Ace.OLEDB.12.0"
cntStudConnection.Open "C:\Users\Hala\Documents\BBB\Dummydata1.accdb"

Set rstNewQuery = New ADODB.Recordset
rstNewQuery.Open Source:=Src, ActiveConnection:=cntStudConnection

Set Sht = thisworkbook.Sheets.Add
Sht.Range(OutputRange).CopyFromRecordset rstNewQuery

Set rstNewQuery = Nothing
cntStudConnection.Close
Set cntStudConnection = Nothing

End Function

I like these forms very presentable......

your task getting this data and checkking it is really not hard to do......... just get rid of all the junk like i showed you above and lets just get the data from the database, you cant do anything unless you have that first.....

I get "No value given for one or more required parameters"

rstNewQuery.Open source:=Src, ActiveConnection:=cntStudConnection
is highlighted in yellow

OK, it maybe a problem with the SQL/Query string........ are you 100% sure that

"SELECT UserName,Password FROM data"

UserName and Password are names of columns
data is the name of the table

i noticed there is no space after the first comma try "SELECT [UserName], [Password] FROM [data]"

is there a prefix that your missing?, im not good with database setups but you sometimes need a prefix ... ."SELECT [UserName], [Password] FROM [DatabaseName].[dbo].[data]"

check the file yourself and double check these

putting the brackets made it work !

but now i just get all the data on my worksheet, it does not accept or reject the username/password combination

yes we removed all that to test this part out, so the next step now is to put this part in the "Correct" place

bare with me for a moment......

OK do this AFTER the last one is finished

and let us know if its ok..

Private Sub CommandButton1_Click()
    Dim Sht as Excel.Worksheet <<-- ADD THIS
    Dim Username As String
    Username = TextBox9.Text
    Dim password As String
    password = TextBox10.Text
    
    Call Module1.QueryData1("SELECT [UserName], [Password] FROM [data]", OutputRange) '<<---PLACE THIS PART HERE

    Set Sht = Thisworkbook.Sheets("TempSheet101")    <-- ADD THIS, THIS IS THE NEW SHEET WITH THE DATA RIGHT!

    If IsNull(Me.TextBox9.Text) Or Me.TextBox9.Text = "" Then
        MsgBox "You must enter your username.", vbOKOnly, "Required Data"
        Me.TextBox9.SetFocus
        Exit Sub
    End If
     
    
    If IsNull(Me.TextBox10.Text) Or Me.TextBox10.Text = "" Then
        MsgBox "You must enter your Password (case sensitive).", vbOKOnly, "Required Data"
        Me.TextBox10.SetFocus
        Exit Sub
    End If
     
    
    Dim temp As String
     On Error Resume Next
     temp = WorksheetFunction.VLookup(Me.TextBox9.Value, Sht.usedRange.Columns(1), 1, 0)' <<-- CHANGED SLIGHTLY
 
If Username = temp Then
     
    Err.Clear
    temp = ""
    temp = WorksheetFunction.VLookup(Me.TextBox9.Value, Sht.usedRange.Columns(2), 1, 0) '<<--AND HERE
    On Error GoTo 0
    If password = temp Then
        Sheets("ADMIN").Visible = xlSheetVisible              '<<-- MAKE SURE YOU ACTUALLY DO HAVE AN ADMIN SHEET :)
        MsgBox "Password & Username Accepted"
        'Unload Me'<<-----DELETE THIS
        Sheets("ADMIN").Activate    ''''Sheets("ADMIN").Select '<<-- NEVER USE SELECT ON A SHEET LIKE THIS
        Range("A1").Select     '<<--THIS IS OK AS LONG AS YOU ACTIVATE THE SHEET FIRST OTHERWISE U GET ERRORS
    Else
        Sheets("ADMIN").Visible = xlVeryHidden
        MsgBox "Username & Password Combination Not Accepted, Workbook will close"
        'Unload Me'<<--DELETE THIS
            
        End If
    End If
     
    Sht.Delete  '<<---SOME MORE ADDITIONAL LINES
    Unload Me

End Sub

thank you so much for your help !

OK

so i just did a mock up...... i havent tested this but just go through and do the corrections

ill test it now and adjust some parts (most likely)

Function QueryData1(Src, OutputRange)

Dim Sht as Excel.WorkSheet

Dim cntStudConnection As ADODB.Connection

Set cntStudConnection = New ADODB.Connection
cntStudConnection.Provider = "Microsoft.Ace.OLEDB.12.0"
cntStudConnection.Open "C:\Users\Hala\Documents\BBB\Dummydata1.accdb"

Set rstNewQuery = New ADODB.Recordset
rstNewQuery.Open Source:=Src, ActiveConnection:=cntStudConnection

Set Sht = thisworkbook.Sheets.Add

Sht.Name = "TempSheet101"'<<--LET NAME THE SHEET HERE SO WE CAN DELETE IT EASY LATER

Sht.Range(OutputRange).CopyFromRecordset rstNewQuery

Set rstNewQuery = Nothing
cntStudConnection.Close
Set cntStudConnection = Nothing

End Function

Lets put your old code back and see whats what...

Private Sub CommandButton1_Click()
    Dim Sht as Excel.Worksheet <<-- ADD THIS
    Dim Username As String
    Username = TextBox9.Text
    Dim password As String
    password = TextBox10.Text
    
    Call Module1.QueryData1("SELECT [UserName], [Password] FROM [data]", OutputRange) '<<---PLACE THIS PART HERE

    Set Sht = Thisworkbook.Sheets("TempSheet101")    <-- ADD THIS, THIS IS THE NEW SHEET WITH THE DATA RIGHT!

    If IsNull(Me.TextBox9.Text) Or Me.TextBox9.Text = "" Then
        MsgBox "You must enter your username.", vbOKOnly, "Required Data"
        Me.TextBox9.SetFocus
        Exit Sub
    End If
     
    
    If IsNull(Me.TextBox10.Text) Or Me.TextBox10.Text = "" Then
        MsgBox "You must enter your Password (case sensitive).", vbOKOnly, "Required Data"
        Me.TextBox10.SetFocus
        Exit Sub
    End If
     
    
    Dim temp As String
     On Error Resume Next
     temp = WorksheetFunction.VLookup(Me.TextBox9.Value, Sht.usedRange, 1, 0)' <<-- ADD THE SHEET HERE TOO
 
If Username = temp Then
     
    Err.Clear
    temp = ""
    temp = WorksheetFunction.VLookup(Me.TextBox9.Value, Sht.UsedRange, 2, 0) '<<--AND HERE
    On Error GoTo 0
    If password = temp Then
        'Sheets("ADMIN").Visible = xlSheetVisible              '<<-- I COMENTED A COUPLE OF LINES HERE
        MsgBox "Password & Username Accepted"
        Unload Me
        'Sheets("ADMIN").Select
        'Range("A1").Select
    Else
        'Sheets("ADMIN").Visible = xlVeryHidden
        MsgBox "Username & Password Combination Not Accepted, Workbook will close"
        Unload Me
            
        End If
    End If
     



End Sub

just work with this for now........ these lookups i dont think will work correctly just yet.... im not used to using excels lookups just give me a minute to test this out......

I now get 'That name is already taken, try a different one' even if the data i enter is wrong

This should fix it from happening again.....

Function QueryData1(Src, OutputRange)

Dim Sht as Excel.WorkSheet

Dim cntStudConnection As ADODB.Connection

Set cntStudConnection = New ADODB.Connection
cntStudConnection.Provider = "Microsoft.Ace.OLEDB.12.0"
cntStudConnection.Open "C:\Users\Hala\Documents\BBB\Dummydata1.accdb"

Set rstNewQuery = New ADODB.Recordset
rstNewQuery.Open Source:=Src, ActiveConnection:=cntStudConnection

OnError ResumeNext
set Sht = Thisworkbook.Sheets("TempSheet101")'<<-- THIS CHECKS IF THE SHEET ALREADY EXISTS
on error goto 0

Application.DisplayAlerts = False '<<--THIS STOPS A STUPID MESSAGE POPPING UP WHEN YOU DELETE A SHEET
If Not Sht is nothing then Sht.Delete  '<<-- DELETE THE SHEET
Application.DisplayAlerts = True

Set Sht = thisworkbook.Sheets.Add
Sht.Name = "TempSheet101"'<<--LET NAME THE SHEET HERE SO WE CAN DELETE IT EASY LATER

Sht.Range(OutputRange).CopyFromRecordset rstNewQuery

Set rstNewQuery = Nothing
cntStudConnection.Close
Set cntStudConnection = Nothing

End Function

Please note that this is not how i would personally do everything, im just following the way it was presented but at least your prof wont suspect anything funny

Let me know how its going

I now get that On Error is not defined

Oh yes, delete the TempSheet101 from the workbook, ill add another line of code to check that....

i get
Method 'Range' of objects'_Worksheet' failed

it's quite weird..
when i put random values in i see the sheet run in the back for a milisecond but then it goes back to vba
whereas when i put the correct values in i get told that the subscript is out of range

Don't worry about it, it is due in 1hour and i've done the best I could so it's ok

the error is gone, but any username/password combo still works

it's ok don't worry !

thank you