Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: Set RecordSource to a query with a parameter

    Using Microsoft Access 2003...an mdb database and VBA application...

    I am working on code that does the following in VBA:

    Forms!inquiry.RecordSource = "Emp Current Work Inquiries qry"

    I have modified "Emp Current Work Inquiries qry" to take an input parameter, prmUser. I don't know how to pass this information to the query in the above context. I took a stab at it and tried this,

    Dim qd As QueryDef
    Set qd = db.QueryDefs("Emp Current Work Inquiries qry")
    qd.Parameters![prmUser] = CurrentUser()
    Forms!inquiry.RecordSource = qd.OpenRecordset
    But it didn't like me setting my recordsource to a querydef recordset, I guess. Can I do what I want to do? I am at a customer site today working on this bug for them and this is the last change I need to make. Needless to say, I want a quick fix, without redesigning as this is not my code and they just want their bug fixed. I have already spent hours on other parts of the code and queries.

    Thank you!

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Quick fix:

    Replace the parameter in your query with a function.

    In a module, create a Public Variable that holds the data and a Public Function to pass the variable to the function.

    Example:

    In Query, replace [Parameter] with fnParameter()

    In module, add
    Code:
    Public pubParameter As Variant
    
    Public Function fnParameter() As String
        fnParameter = Nz(pubParameter,"")
    Exit Function
    Now, instead of trying to assign the value to the parameter directly, set the public variable equal to the value and simply invoke the query. The query calls the function and gets the value.

    tc

  3. #3
    Join Date
    Nov 2005
    Posts
    113
    qd.Parameters![prmUser] = CurrentUser()

    I use qd like this and it works. I read a value from a combobox in my question.
    qd![Forms!frmStart!cbo1] = [Forms]![frmStart]![cbo1]

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    If you are using CurrentUser() as the parameter, why not just put it in the query itself? CurrentUer() would be your criteris.

    Code:
    SELECT tblUser.UserName
    FROM tblUser
    WHERE (((tblUser.UserName)=CurrentUser()));

  5. #5
    Join Date
    Jul 2006
    Posts
    111
    DCKunkle - You are going to be SO sorry you asked that question!

    The code originally was using CurrentUser() in the query. And all was fine. My client has a department of engineers that run the Access application, and other departments as well. The other departments have no issues. It seems to be conflicting on the engineers' machines because of homegrown software only the engineers have installed. I worked on bugs a few weeks ago where there were conflicts calling RunCode in autoexec. So I moved that out of autoexec and put it in Form_Load. Now their next error is the call inside the query to CurrentUser() says "Unknown function name". I think then I changed it to CurrentUser without parentheses and it works when I run the query standalone but when the query is invoked from the code it still says "Unknown function name". I thought I would spare you these disturbing details in my original post, but yes using CurrentUser() would be the ideal thing to do if it worked!

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by M Mock
    DCKunkle - You are going to be SO sorry you asked that question!

    The code originally was using CurrentUser() in the query. And all was fine. My client has a department of engineers that run the Access application, and other departments as well. The other departments have no issues. It seems to be conflicting on the engineers' machines because of homegrown software only the engineers have installed. I worked on bugs a few weeks ago where there were conflicts calling RunCode in autoexec. So I moved that out of autoexec and put it in Form_Load. Now their next error is the call inside the query to CurrentUser() says "Unknown function name". I think then I changed it to CurrentUser without parentheses and it works when I run the query standalone but when the query is invoked from the code it still says "Unknown function name". I thought I would spare you these disturbing details in my original post, but yes using CurrentUser() would be the ideal thing to do if it worked!
    Go into your references and check that you've not dropped one or that one is MISSING ... CurrentUser should work at all times (even w/o logging in - defaults to Admin) ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I agree with M Owen, it might be in references. But, even if there is nothing that says MISSING, uncheck one library, exit references, then go back in and check the library you unchecked.

    This will force Access to reset the references for that particular machine. I have had problems (and there is an article on microsoft.com) with different versions of libraries on different systems causing problems.

  8. #8
    Join Date
    Jul 2006
    Posts
    111
    That's a good point, especially since CurrentUser() called from the query is ok when I run the query directly but not when the query is run from the code. I'll either try it today if I can connect remotely to that machine or on Monday when I am onsite again. I will let you know the results. Thanks.

  9. #9
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Another thing you can try, is to give the full name of the function:

    Application.CurrentUser()

    From what I read recently, you can avoid most reference problems by using the full name. Without the full name Access starts checking the linked libraries for function and procedure names and blows up when a reference is wrong. So if you are explicit with the name then Access knows exactly were the function resides.

    Only read it, never tried it.

  10. #10
    Join Date
    Jul 2006
    Posts
    111
    That makes sense, too. Now that I've read it (from you), I will try it and let you know if it works as well in practice. Thanks.

  11. #11
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Aside from good programming practice, blah blah blah, there are occasions where an object has the same name in two or more libraries. This is where using the explicit call becomes critical, since it can work on your machine but not somebody elses' simply due to the references being in a different order of precedence on the other machine.

    Admitedly, it doesn't happen often and I rarely do it myself (except with recordset objects since DAO and ADO use all the same names).

    tc

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Option Explicit

    Good coding practice starts here...
    George
    Home | Blog

  13. #13
    Join Date
    Jul 2006
    Posts
    111
    Testing on my machine, Application.CurrentUser() gave me this error:

    Undefined function 'Application.CurrentUser' in expression.

    However, Application.CurrentUser without parentheses worked.

    Why do I have to drop the parens?

    Remember, this line is in a query, not in VBA code. Not that I think that should matter, because on my machine CurrentUser() worked, with parens.

    I don't know what this foretells happening on my customer's machine, once I implement it there....

  14. #14
    Join Date
    Jul 2006
    Posts
    111
    I am back to my original question - how do I pass the parameter in when it's a recordset?

    Nothing works on the customer's machine with this strange other software installed. So I have to continue tackling it with my first workaround - passing a parameter to a recordset.

    If this thread isn't too old and long and people are still viewing it, I have to do something like this:

    Set qd = db.QueryDefs("Emp Current Work Inquiries qry")
    qd.Parameters![prmUser] = CurrentUser()
    Forms!inquiry.RecordSource = qd.OpenRecordset

    But I get a Type Mismatch on the third instruction.

  15. #15
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Do you have a split database? With a frontend and a backend? Does each user get a frontend? If so you can store the CurrentUser() in a table and then use a join in your query to limit it by the CurrentUser(). The table should be put in the frontend (not the backend).

    The table will have one field and one record. Then write a little routine to update the table with the current user.

    If that is not an option then I believe your Type Mismatch is due to missing quotes around the CurrentUser(). Add "'" before and after to make it a string.

Posting Permissions

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