open a form with userlevel criteria

[Mark Carlyle via AccessMonster.com]

I want to open my database directly to a form and filter that form to only
show info pertaining to the user that is currently logged in.

Now I have a User Database that assings a "rep number" to each person.

My forms already sort the data by the "rep number"

What I want to do is compare the CurrentUser() info to the Debt Advisor table
and find the rep number that matches the current user.

Then filter the current form "greybar" to only show data where the rep number
of the currentuser = the repnumber of the form.

I hope what I am writing is understandable... thanks in advance for any help.

 

[Mark Carlyle via AccessMonster.com]

I got lucky and figured this out partially... I used this code...

Dim strCriteria As String
Dim repname As String

repname = CurrentUser()

replogin = DLookup("[rep number]", "Debt Advisors", "[username] ='" & repname
& "'")


strCriteria = "[salesperson] = '" & replogin & "'"

Me.Filter = strCriteria
Me.FilterOn = True

The final problem I am having with this is how can i keep it so the filter
cannot be removed? I do not want my reps to be able to just remove the
filter and then see everyone else's info.

TIA for any help.

 

[John Nurick]

Hi Mark,

I think the best way to do this is by using code in the form's Load
event procedure to modify its RecordSource. Assuming the current
recordsource is "MyQuery", you'd need something like this:

Dim strRepLogin As String

strRepLogin = DLookup("[rep number]", "Debt Advisors", _
"[username] ='" & repname & "'")

Me.RecordSource = "SELECT * FROM MyQuery WHERE [salesperson]='" _
& strRepLogin & "';"

I got lucky and figured this out partially... I used this code...

Dim strCriteria As String
Dim repname As String

repname = CurrentUser()

replogin = DLookup("[rep number]", "Debt Advisors", "[username] ='" & repname
& "'")


strCriteria = "[salesperson] = '" & replogin & "'"

Me.Filter = strCriteria
Me.FilterOn = True

The final problem I am having with this is how can i keep it so the filter
cannot be removed? I do not want my reps to be able to just remove the
filter and then see everyone else's info.

TIA for any help.

I want to open my database directly to a form and filter that form to only
show info pertaining to the user that is currently logged in.

Now I have a User Database that assings a "rep number" to each person.

My forms already sort the data by the "rep number"

What I want to do is compare the CurrentUser() info to the Debt Advisor table
and find the rep number that matches the current user.

Then filter the current form "greybar" to only show data where the rep number
of the currentuser = the repnumber of the form.

I hope what I am writing is understandable... thanks in advance for any help.

 

[Mark Carlyle via AccessMonster.com]

Awesome... this worked great... now I want to do the same thing with a
subform of this form... the query for the subform is called [task list].
When I try to put this code in the subform, it cannot find the data source.
I think it has something to do with the me. command and a subform...

Any ideas?

Hi Mark,

I think the best way to do this is by using code in the form's Load
event procedure to modify its RecordSource. Assuming the current
recordsource is "MyQuery", you'd need something like this:

Dim strRepLogin As String

strRepLogin = DLookup("[rep number]", "Debt Advisors", _
"[username] ='" & repname & "'")

Me.RecordSource = "SELECT * FROM MyQuery WHERE [salesperson]='" _
& strRepLogin & "';"

I got lucky and figured this out partially... I used this code...

[quoted text clipped - 31 lines]

 

[Mark Carlyle via AccessMonster.com]

OK I spoke too soon... It works; however, I have a search tool that I have
written in a subform. The subform opens the main form with the criteria of
the seached record. This worked with the filter because it just removed the
filter, but with the query, it just makes the form blank since the search can
find a record that is not in the query.

Can I update the recordsource of the main form "greybar" from within the
subform? If I do that, How do I overwrite the on open command that we used
previously.

TIA for any help.

Awesome... this worked great... now I want to do the same thing with a
subform of this form... the query for the subform is called [task list].
When I try to put this code in the subform, it cannot find the data source.
I think it has something to do with the me. command and a subform...

Any ideas?

Hi Mark,

[quoted text clipped - 20 lines]

Please respond in the newgroup and not by email.

 

[John Nurick]

I'm not certain how much the subform can do to the main form, but have
you tried (for code running behind the subform):

Me.Parent.RecordSource = "blah blah"

? Me is the subform, .Parent is therefore the main form.

OK I spoke too soon... It works; however, I have a search tool that I have
written in a subform. The subform opens the main form with the criteria of
the seached record. This worked with the filter because it just removed the
filter, but with the query, it just makes the form blank since the search can
find a record that is not in the query.

Can I update the recordsource of the main form "greybar" from within the
subform? If I do that, How do I overwrite the on open command that we used
previously.

TIA for any help.

Awesome... this worked great... now I want to do the same thing with a
subform of this form... the query for the subform is called [task list].
When I try to put this code in the subform, it cannot find the data source.
I think it has something to do with the me. command and a subform...

Any ideas?

Hi Mark,

[quoted text clipped - 20 lines]

Please respond in the newgroup and not by email.