Re: open a form with userlevel criteria

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 & "';"



On Tue, 09 Aug 2005 00:45:41 GMT, "Mark Carlyle via AccessMonster.com"
<forum@xxxxxxxxxxxxxxxxx> wrote:

>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.
>
>Mark Carlyle wrote:
>>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.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

Relevant Pages

  • Re: open a form with userlevel criteria
    ... Dim strCriteria As String ... Dim repname As String ... The final problem I am having with this is how can i keep it so the filter ... >What I want to do is compare the CurrentUser() info to the Debt Advisor table ...
    (microsoft.public.access.formscoding)
  • Inner Join question!
    ... with recordsource "L". ... Dim lngLen As Long 'Length of the criteria string to append to. ... msg2 = "Filter by Languages?" ... using a diffrent record source.. ...
    (comp.databases.ms-access)
  • Inner Join question!
    ... with recordsource "L". ... Dim lngLen As Long 'Length of the criteria string to append to. ... msg2 = "Filter by Languages?" ... using a diffrent record source.. ...
    (comp.databases.ms-access)
  • Inner Join question!
    ... with recordsource "L". ... Dim lngLen As Long 'Length of the criteria string to append to. ... msg2 = "Filter by Languages?" ... using a diffrent record source.. ...
    (comp.databases.ms-access)
  • Re: Filter By Selection
    ... > If you are trying to apply a filter to the form's ... > recordsource based upon the value of yourComboBox, ... >>I've tried this on the premise that I can build the string ...
    (microsoft.public.access.formscoding)