Another SQL Question

[bonekrusher]

Hi All
In reference to my last post: http://www.access-programmers.co.uk/forums/showthread.php?t=102106

In my code below I get the users info from "username = Environ$("username")". My question is, can I use that value in my sql statement? I keep getting an error "No Value Given for one or more required parameters."

If I replace username with 'jdoe' it works fine. What am I missing?

Dim myconnection As ADODB.Connection
Dim myrecordset As New ADODB.Recordset
Set myconnection = CurrentProject.Connection
Dim username As String
Dim answer As String

username = Environ$("username")
    myrecordset.ActiveConnection = myconnection

myrecordset.Open "SELECT Table1.Name, Table1.eMAIL FROM Table1 WHERE (([Table1]![Name]=username))"

answer = myrecordset("email")
Me.Text1 = answer

 

[ghudson]

Check this out...
Functions and properties that are blocked in sandbox mode

Add this to a public [not a form] module if you are using Access 2003....

Public Function Environ(Expression)
On Error GoTo Err_Environ
    
    Environ = VBA.Environ(Expression)

Exit_Environ:
    Exit Function

Err_Environ:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_Environ

End Function

 

[bonekrusher]

G,

I added this to a module and have the same error message. I read the article but I am still confused....what am i missing?

Public Function Environ$(Expression)
On Error GoTo Err_Environ$
    
    Environ$ = VBA.Environ(Expression)

Exit_Environ$:
    Exit Function

Err_Environ$:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_Environ$

End Function

 

[ghudson]

You can not put a variable within a string like that.

Trying something like this...

myrecordset.Open "SELECT Table1.Name, Table1.eMAIL FROM Table1 WHERE (([Table1]![Name]=" & username & "))"

 

[bonekrusher]

Thanks G, but I am still getting the error. oh well, I'll look for a work around....

Thanks for the help!

 

[whatever]

Assuming username is a string then try

WHERE (([Table1]![Name]='" & username & "'))"

Note the single quotes which will delimit your username variable, so if user name = Kevin the 'where' clause will read:

WHERE (([Table1]![Name]='Kevin'))


HTH


Kevin