recordset help 1

[Spyridon]

First of all, sorry for posting this to two different forums, but I wasn't sure of the best one to post to.

Here's what I'm trying to do, have a SQL statement Insert info into a table, and then verify that info from what someone typed in from a text box. My problem is coming in at "If LocalUser.Password =......" It tells me an object is needed. What's the syntax to type in to pull up this record in this manner? Thanks for any help!

Dim SQL As String
Dim SQL2 As String


SQL = "INSERT INTO LocalUser (Password) " & _
" SELECT Password " & _
" FROM Employee " & _
" WHERE [UserID] = [Forms]![FoLogin]![txtUserID]"

DoCmd.RunSQL SQL



If LocalUser.Password = [Forms]![FoLogin]![txtPassword] Then

 

[TonyJollans]

Hi Spyridon,

If I understand correctly you are reading a master table of userids and passwords to get the password of a user who is trying to log on, which you are then storing (temporarily) in another table. Why do you need to store it?

Leaving aside why you are doing any of this rather than using Access security, all you need to do is use the DLookup function on the base table (I don't know enough about LocalUser to provide an example against that) ..

If DLookup("Password", "Employee", "[UserID] = '" & [Forms]![FoLogin]![txtUserID]" & "'") = [Forms]![FoLogin]![txtPassword] Then ...

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[Spyridon]

Hey thanks Tony,,

Funnily enough I am using Dlookup right now, but in converting Access into an Access project, the Dlookup function has stopped working. I was aware that it would not work in a query, but it seems as though it won't work in the code either. This is what I have used:

If DLookup("[Password]", "Employee", _
"[UserID] = [Forms]![FoLogin]![txtUserID]&quot = [Forms]![FoLogin]![txtPassword] Then

DoCmd.OpenForm "FoMain"
DoCmd.OpenQuery "QALocalUser"
DoCmd.Close acForm, "FoLogin"

ElseIf IsNull(txtPassword) Then
MsgBox "The password field cannot be blank. Please type your password."
DoCmd.GoToControl "txtPassword"
Else
MsgBox "Your password is wrong"
DoCmd.GoToControl "txtPassword"
txtPassword.Text = ""
End If



And yes, I am doing exactly what you are saying, storing the info in a local table that basically holds only one record. The reason for this is twofold. 1stly, I don't know enough about Access security to do what I want. And 2cdly, my login isn't neccesarily to keep people from cracking in, I'm not so worried about that aspect. It is mainly to keep track of who the user is so that their personal sales are logged, reported on, etc. It's in a group of about 200 over a server (the reason why it will need to be moved into SQL Server)...


Any suggestions on an alternate route to dlookup in VBA? I can't figure out how to use a valid SQL statement, as I'm not inserting or updating anything, I'm just passing a value from a field in a table to a variable in the code.

thanks!

Michael

 

[TonyJollans]

Hi Spyridon,

Why not open a recordset with your SQL as source ..

"SELECT Password FROM Employee WHERE [UserID] = " & [Forms]![FoLogin]![txtUserID]

And (if found) assign the result (RecordsetName!Password) to a variable which you can then use both to validate and to write to your localuser table.

Can't give you the precise syntax off the top of my head and am not set up to try it - SQL Server is sitting on a CD on my desk at the moment.



Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[Spyridon]

Thanks again Tony, I'm one step closer here. I'm missing a huge part of the programming scheme since I know almost absolutely nothing about recordsets. But I'm giving it the old heave hoe..

Now obviously something is wrong here in what I'm doing, I'm writing this code in a regular Access .mdb first, seeing if it will work and then upsizing it if it does. This is the code that I have, but I'm guessing I'm not setting up the recordset right. I've seen where people use ADODB.connection, etc. Can you help in telling me how I'm fouling this up? Here's the code. Oh, and by the way, thank you for being so helpful, you have my respect.


Private Sub LoginCom_Click()
Dim SQL As String
Dim rs As Recordset
Dim passwrd As String


Set rs = "SELECT Password FROM Employee WHERE UserID = " & [Forms]![FoLogin]![txtUserID]
passwrd = rs!Password


If passwrd = [Forms]![FoLogin]![txtPassword] Then


SQL = "INSERT INTO LocalUser ( UserID, UserLevel, SalesID, Manager, Employee ) " & _
"SELECT [Forms]![FoLogin]![txtUserID] AS Expr1, UserLevel, SalesID, Manager, Employee " & _
"FROM Employee " & _
"WHERE (((UserLevel)=(SELECT UserLevel FROM Employee WHERE UserID = [Forms]![FoLogin]![txtUserID]))" & _
"AND ((SalesID)=(SELECT SalesID FROM Employee WHERE UserID = [Forms]![FoLogin]![txtUserID])) " & _
"AND((Manager)=(SELECT Manager FROM Employee WHERE UserID = [Forms]![FoLogin]![txtUserID])) " & _
"AND ((Employee)=(SELECT Employee FROM Employee WHERE UserID = [Forms]![FoLogin]![txtUserID]))) "

DoCmd.OpenForm "FoMain"
DoCmd.RunSQL SQL
DoCmd.Close acForm, "FoLogin"

ElseIf IsNull(txtPassword) Then
MsgBox "The password field cannot be blank. Please type your password."
DoCmd.GoToControl "txtPassword"
Else
MsgBox "Your password is wrong"
DoCmd.GoToControl "txtPassword"
txtPassword.Text = ""
End If


End Sub

 

[TonyJollans]

Hi Spyridon,

I could get out of my depth on this - I'm not really up to speed on ADO - but here goes.

A Recordset is the result set of a Query (in effect a File) and a recordset variable is (in effect) a pointer to the recordset, not the SQL string itself.

Now you don't show your connection in the code so I can't fill in all the blanks but instead of ..

Dim rs As Recordset
Dim passwrd As String


Set rs = "SELECT Password FROM Employee WHERE UserID = " & [Forms]![FoLogin]![txtUserID]
passwrd = rs!Password

.. you need something like ..

Dim rs_sql As String
Dim rs As Recordset
Dim passwrd As String


rs_sql = "SELECT Password FROM Employee WHERE UserID = " & [Forms]![FoLogin]![txtUserID]
Set rs =

ConnectionName

.Execute(rs_sql)
passwrd = rs!Password

Next, as this uses values from the form, you can't actually open the recordset until you've got the form input (just before you run the other SQL).

I don't have time at the moment to mock up your situation and don't want to post dodgy code so I hope that's enough to move you forward. I'll look in again tomorrow.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.