ADO Recordset Problem

[Don Wilson]

I have a users table wich stores user information,
username, pass, privelages etc....
I use this security for all the databases i develop.
This allows for authenticating the user of course.

Here is my problem, i need to set a forms record source
based on whether the user logged in has data admin
priveleges or not.
If the user logged in has data admin privelages i would
like the record source of the form to open all records.
But if the user logged in does not have data admin
privelages then i only want the record source to show the
records for that user.

I created an ado recordset wich returns the records for
the user logged in, but it will not allow any updates,
additions, or deletions. If i remove the criteria
everything works fine.

Here is the code. Any help would be greatly appreciated.
Thx in advance.



Private Sub Command15_Click()
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim varLoginId As Integer

'The following line extracts the login id from a
hidden login form that is always open.
varLoginId = Forms!frmLogIn!LoginId


'Use the ADO connection that Access uses
'Create an instance of the ADO Recordset class, and set
its properties
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open _
Source:="SELECT * " & _
"FROM tblTimesheets " & _
"WHERE (((TechID)= " & varLoginId & "))", _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic
'Set the form's Recordset property to the ADO recordset
Set Me.Recordset = rst

rst.Close

Set rst = Nothing
Set cn = Nothing
End Sub

 

[Sam]

Don,
I'm not really answering your question, however I can't see why you don't
just set the Recordsource for the form directly, for example...

varLoginId = Forms!frmLogIn!LoginId
me.recordsource="SELECT * " & _
"FROM tblTimesheets " & _
"WHERE (((TechID)= " & varLoginId & "))", _

Not sure if you'll need to a me.requery, (I don't think its needed).

HTH
Sam

 

[Don Wilson]

Sam, you are the MAN!

I have been trying to get that to work for over a month.

Nobody has replied to my posts at all. (And i have that
same post, posted numerous times slightly variated)

Your way works, im not sure why, but it works.

Thx a bunch, and Bless You!