Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Embedded Query Subroutine (2k)

    My friend recommended I use this subroutine for checking some permissions in my database. However, I'm having problems with the code, its telling me that the 2nd line (Set Permrec ...) is an invalid arguement, and my friend is on vacation for the holidays so I can't find out from him what the problem may be. Perhaps someone can spot the problem? I may not be referencing something that I need, or something along those lines.

    Public permission(25) As Boolean

    Sub GetPermissions()

    Set PermRec = CurrentDb.OpenRecordset("Select * from permissions where UserID = " & "'" & vUser & "'", dbOpenDynaset) '<------ erroring line of code

    For i = 2 To PermRec.Fields.Count - 1
    j = i - 1
    permission(j) = PermRec.Fields(i).Value
    Next i

    PermRec.Close
    Set PermRec = Nothing

    End Sub
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Embedded Query Subroutine (2k)

    This is DAO code, so you need a reference to the Microsoft DAO 3.6 Object Library (in Tools | References...). Otherwise, dbOpenDynaset is not recognized.

    Your code does not declare the variables PermRec, vUser, i and j. In particular, in the code as posted, it is not clear whether vUser has a value.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Embedded Query Subroutine (2k)

    Sorry Hans, I haven't had my coffee yet. I should clear that up. I already reference Microsfot DAO 3.6 Object Library, and vUser has a value already (string). It is found just before i call GetPermissions. Here is the part of my code in Form_Load:

    Private Sub Form_Load()
    'permissions code
    vUser = fOSUserName 'get the user name
    Call GetPermissions

    If permission(0) = False Then Command3.Enabled = False
    If permission(1) = False Then Command4.Enabled = False
    If permission(2) = False Then Command5.Enabled = False
    If permission(3) = False Then Command6.Enabled = False

    End Sub

    what should PermRec be declared as? I cleaned up the other parts and declared vUser, i and j.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Embedded Query Subroutine (2k)

    Declare Permrec as follows:

    Dim PermRec As DAO.Recordset

  5. #5
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Embedded Query Subroutine (2k)

    well that fixed that, which of course takes me to my next error <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    permission(j) = PermRec.Fields(i).Value

    vba highlights this line and says "No Current Record". but... i have a lot of records. do i need to set it to a particular record?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Embedded Query Subroutine (2k)

    When you open a DAO recordset, it automatically points to the first record - if there is one. Perhaps there are no records for the vUser you specify. You can test by temporarily inserting the following lines after the one that opens the recordset:

    MsgBox PermRec.RecordCount & " record(s)"

    If the recordset has no records, the record count will be 0, otherwise it can be any positive number (not necessarily accurate)

    Note: the SQL you use is only valid if UserID is a text field in the Permissions table.

  7. #7
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Embedded Query Subroutine (2k)

    thanks Hans, it works now.

    someone needs to make a [BowsToHans] tag.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Embedded Query Subroutine (2k)

    Like this? <img src=/S/moon.gif border=0 alt=moon width=15 height=15>

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Embedded Query Subroutine (2k)

    <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22> <img src=/S/king.gif border=0 alt=king width=21 height=22> is more appropriated <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Francois

Posting Permissions

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