how do I pass a value from a SQL string into a variable? 1

[Spyridon]

Hi,


I'm having a bit of a time doing this, so any help is appreciated. What I need my code to do, is look up a value from a table, verify that value against what someone typed in, and then execute the next portion of the code. I can have this take care of by a Dlookup function, but I don't want to as it seems to have a problem with in an Access project (adp). What I think, is that I can have this taken care of by working with a recordset, but being recordset ignorant, I'm pretty sure that I'm not coding it correctly. Here is what I have:

Dim SQL As String
Dim SQL2 As String
Dim rs As ADODB.Recordset
Dim passwrd As String


Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection

SQL2 = "SELECT Password FROM Employee WHERE UserID LIKE " _ & '[Forms]![FoLogin]![txtUserID]'

rs.Open SQL2



If SQL2 = [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




So basically, I need SQL2 to pull the value up from the table, and then I need to validate that value (I'm assuming that the value is now stored in SQL2 as compared to what the user typed in. What am I doing wrong???


Michael

 

[BuGlen]

Hi Michael,

You seem to be confusing the query string with the result data. Once you open the recordset based on the query string, the results will be held in the recordset object. So to compare, you would need the following statement:
-----
If rs.Fields("Password&quot.Value = [Forms]![FoLogin]![txtPassword] Then
' You code here
End If
-----

Hope this helps.


Glen Appleton

VB.Net student.

 

[Spyridon]

such a simple solution... Thank you sooooo much! You just allowed me to sleep at night! If I could see ya I'd kiss ya! Or at least give you a handshake! No, but seriously thank you. It's working like a charm now, and now I know how to replace those other pesky dlookup's that I've got in there. I'm guessing, that using the recordset will be quicker than a dlookup as well.

Another quick question off topic, can you use VB.Net to program just regular windows programs (for example a front end program for a database), or is it used only in conjunction with the internet? I assumed it was the former, but... ?

 

[BuGlen]

Glad to help.

Yes, you can use VB.Net to create Windows apps, Web apps, and web services. You can get the standard edition from Microsoft right now for about $109 which will work fine for non-enterprise level application development. You can also get the Visual Studio Tools for Microsoft Office Systems for about $199 (I think), which includes VB.Net standard and some other goodies for using VB.Net with Office 2003.

Glen Appleton

VB.Net student.