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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

but also highlights in blue

Please Login or Register  to view this content.

Would it maybe be easier if i attached my file ?

thank you for your help !

I've renamed my textbox with the code

Please Login or Register  to view this content.

Can i not change the name of it like that ?

I've also blocked the change event

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

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

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

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

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:

Please Login or Register  to view this content.

userform with input box that i named outputrange

Please Login or Register  to view this content.

And module

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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

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)

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

OK do this AFTER the last one is finished

and let us know if its ok..

Please Login or Register  to view this content.

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

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

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

Please Login or Register  to view this content.

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

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