problem with reading recordset

[Guest]

I am using the following code to read in the username logged onto the system
and then am trying to match that value to a lookup table to populate a field
on a form.

Dim User, UserName, stSQL As String
Dim RS, Con As Object

'get username from system

User = VBA.Environ("username")


'Match username to tblFCStaff


Set Con = Application.CurrentProject.Connection
stSQL = "SELECT * from [tblFCStaff] WHERE [logon]= " & User & ";"
Set RS = CreateObject("ADODB.Recordset")


With RS
.ActiveConnection = Con
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = stSQL
.Open
.ActiveConnection = Nothing
End With


' If there are no matches, display a message.

If (RS.EOF) Then
Me![OptionLabel1].Visible = True
Else
Me![cboLookup] = RS![Staff]
End If

' Close the recordset and the database.

RS.Close
Set RS = Nothing
Set Con = Nothing

When I run this, I get an error. I stepped through the code and it seems to
be bombing out in the "with RS...it goes fine until it gets to ".Open" and
then it errors out with "No value given for one or more required parameters."

Any idea what is wrong here? I am stumped!
Thanks!

 

[Dirk Goldgar]

I am using the following code to read in the username logged onto the
system and then am trying to match that value to a lookup table to
populate a field on a form.

Dim User, UserName, stSQL As String
Dim RS, Con As Object

'get username from system

User = VBA.Environ("username")


'Match username to tblFCStaff


Set Con = Application.CurrentProject.Connection
stSQL = "SELECT * from [tblFCStaff] WHERE [logon]= " & User & ";"
Set RS = CreateObject("ADODB.Recordset")


With RS
.ActiveConnection = Con
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = stSQL
.Open
.ActiveConnection = Nothing
End With


' If there are no matches, display a message.

If (RS.EOF) Then
Me![OptionLabel1].Visible = True
Else
Me![cboLookup] = RS![Staff]
End If

' Close the recordset and the database.

RS.Close
Set RS = Nothing
Set Con = Nothing

When I run this, I get an error. I stepped through the code and it
seems to be bombing out in the "with RS...it goes fine until it gets
to ".Open" and then it errors out with "No value given for one or
more required parameters."

Any idea what is wrong here? I am stumped!
Thanks!

I imagine that the text value of User must be enclosed in quotes in the
SQL string. Try:

stSQL = "SELECT * from [tblFCStaff] WHERE [logon]= " & _
Chr(34) & User & Chr(34) & ";"

While you're in there, change these lines:

Dim User, UserName, stSQL As String
Dim RS, Con As Object

to these:

Dim User As String, UserName As String, stSQL As String
Dim RS As Object, Con As Object

As you had it, all variables except stSQL and Con were declared as
Variant.

 

[Guest]

You dim statements are incorrect. You are suffering from a common
misconception. The variable type at the end of a line does not define the
type for all variables on the line, only the last one. Any variable dimmed
without an explicit type is Variant.

In this case, RS is not an object, it is a variant and therefore not the
property type for how you are using it.

 

[Guest]

Thanks!
This solved it!

I am using the following code to read in the username logged onto the
system and then am trying to match that value to a lookup table to
populate a field on a form.

Dim User, UserName, stSQL As String
Dim RS, Con As Object

'get username from system

User = VBA.Environ("username")


'Match username to tblFCStaff


Set Con = Application.CurrentProject.Connection
stSQL = "SELECT * from [tblFCStaff] WHERE [logon]= " & User & ";"
Set RS = CreateObject("ADODB.Recordset")


With RS
.ActiveConnection = Con
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = stSQL
.Open
.ActiveConnection = Nothing
End With


' If there are no matches, display a message.

If (RS.EOF) Then
Me![OptionLabel1].Visible = True
Else
Me![cboLookup] = RS![Staff]
End If

' Close the recordset and the database.

RS.Close
Set RS = Nothing
Set Con = Nothing

When I run this, I get an error. I stepped through the code and it
seems to be bombing out in the "with RS...it goes fine until it gets
to ".Open" and then it errors out with "No value given for one or
more required parameters."

Any idea what is wrong here? I am stumped!
Thanks!

I imagine that the text value of User must be enclosed in quotes in the
SQL string. Try:

stSQL = "SELECT * from [tblFCStaff] WHERE [logon]= " & _
Chr(34) & User & Chr(34) & ";"

While you're in there, change these lines:

Dim User, UserName, stSQL As String
Dim RS, Con As Object

to these:

Dim User As String, UserName As String, stSQL As String
Dim RS As Object, Con As Object

As you had it, all variables except stSQL and Con were declared as
Variant.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)