Results 1 to 10 of 10

Thread: see my records

  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: see my records

    Hi all,
    I've been working on a database with three users. They don't want each other to see their records. I created a login form for the users. When they open the database, they see a switchboard. When they click a button to open or edit records, I have some code to open the form based on their login information. Problem is it's not working. I created a query and put it in the code:
    Code:
    Private Sub cmdEnterEdit_Click()
    On Error GoTo Err_cmdEnterEdit_Click
    
        Dim stDocName As String
        Dim stLinkCriteria As String
        Dim strnewquery As String
        
         
         ' passes the filter criteria when the form is opened
    strnewquery = "SELECT tblServiceType.TXT_ServType FROM tblServiceType " & _
    "WHERE (((tblServiceType.TXT_ServType) = [Forms]![frmLogon]![Txt_ServType])) "
    
    
        stDocName = "frmOccur"
        DoCmd.OpenForm stDocName, , , strnewquery
    
    Exit_cmdEnterEdit_Click:
        Exit Sub
    
    Err_cmdEnterEdit_Click:
        MsgBox Err.Description
        Resume Exit_cmdEnterEdit_Click
        Resume
    End Sub
    I get a syntax error and when I debug, it points to the strnewquery. Please help.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by slimjen
    I get a syntax error and when I debug, it points to the strnewquery. Please help.
    I bet you don't. Is this defo the original code? What exactly is the error message and exactly which line is highlighted in yellow when you debug?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    first:
    "WHERE (((tblServiceType.TXT_ServType) = [Forms]![frmLogon]![Txt_ServType])) "
    sticks the literal text
    [Forms]![blah blah
    into the SQL which is certainly not what you want

    second: Txt_ServType looks like it is text, so you need some quotes.

    so have a go with:
    "WHERE (((tblServiceType.TXT_ServType) = '" & [Forms]![frmLogon]![Txt_ServType] & "')) "

    izy

    LATER i see you are messing with filters - not something i do so maybe my comment is incorrect in filter-world.
    Last edited by izyrider; 10-18-06 at 13:10.
    currently using SS 2008R2

  4. #4
    Join Date
    Jul 2004
    Posts
    214
    I tried the code "Where..." and it still gave me a syntax error. The highlighted code is the "docmd..." line.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    a peek at help shows:
    WhereCondition
    Optional Variant.
    A string expression that's a valid SQL WHERE clause without the word WHERE.


    so with the quotes and concatenation fixed you now have a valid SQL query...
    ...but it seems that you only need the stuff after the WHERE.

    have another go (no guarantees tho, since i am not a filter person)

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Jul 2004
    Posts
    214
    I am trying a different approach but getting a error: "the openform action was cancelled." Here's my code:
    Code:
    Private Sub cmdEnterEdit_Click()
    On Error GoTo Err_cmdEnterEdit_Click
    
        Dim stDocName As String
        Dim stLinkCriteria As String
        Dim strnewquery As String
        
         
        stLinkCriteria = "IDX_ServID = '  & Forms!frmLogon!IDX_ServID & '" ' 
        
        stDocName = "frmOccur"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    Exit_cmdEnterEdit_Click:
        Exit Sub
    
    Err_cmdEnterEdit_Click:
        MsgBox Err.Description
        Resume Exit_cmdEnterEdit_Click
        Resume
    End Sub
    IDX_ServID is the name of the field on the form I want to open and the name of the field I am trying to point to filter the records. What am I doing wrong please?

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    gosh - i really wish a filter-person would comment here.

    anyhow, your current effort
    stLinkCriteria = "IDX_ServID = ' & Forms!frmLogon!IDX_ServID & '" '
    is doomed.

    please, have one more go with
    stLinkCriteria = "IDX_ServID = '" & Forms!frmLogon!IDX_ServID & "'"

    izy

    with my ZERO knowledge of filters, what i am trying to cause to happen is that your filter string ends up like
    "IDX_ServID = 'Fred'"
    ...the first ' is literal
    the & formfield & concatenates in the text value Fred from the form
    ...the second ' closes the quotes around the text field (i'm still assuming IDX_ServID is text (you changed the name!!!) - if it has magically become numeric, have a go with
    stLinkCriteria = "IDX_ServID = " & Forms!frmLogon!IDX_ServID
    )
    Last edited by izyrider; 10-19-06 at 11:20.
    currently using SS 2008R2

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by izyrider
    gosh - i really wish a filter-person would comment here.
    You look to be doing fine from where I am standing
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jul 2004
    Posts
    214
    thanks for all your help. I approached it a different way and got what I needed. But, I just received a request from one of the users to give all users access to two different types of service types. As of now, I have a table of users assigned to a password and one type of service. I have no idea how to give them access to two service types! I know I saw a table in the past that has the username, password, and a series of checkboxes with lables of the type of permissions the user has access to. But, I have no idea how to set it up. I just got the filter working and don't want to mess that up. Any ideas anyone?

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    idea #1:
    tell the users that you have implemented per design brief - go have a beer and forget it.

    idea#2
    ask the users if they will want three service types 20secs after you implement two service types. then four, then N. ask why they wanted records tied to one user in the first place - go have a beer and forget it.

    idea#3
    i have a db where records are owned by one user, but with the possibility for admin users to allow other users to have access (e.g. backup-user during vacation etc). two-step logon: netlogin -> table reveals if the person has (in my case temporary) multi-access, permitted person chooses 'who he wants to be' for a session (simplifies my audit trail)...etc

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •