Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Unhappy Unanswered: Pass a user variable to a Stored Procedure

    There is a report that I want to run and it requires a user to put in their ID, like GARY1234, it returns records, everything is okay.

    But now I want to pass the GARY1234 variable to a MS SQL Stored Procedure to run some calculations for other sub reports on the SAME report.

    I know that 1 step is to create the PASSTHRU query to the SQL server, I have done that : EXEC spCLIENTID GARY1234 but how do I get the GARY1234 in the query?


  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    One common technique is to use a DAO QueryDef to change the pass through query's SQL to a string that includes the value. Don't have the here, but play with that and see how you do.
    Paul

  3. #3
    Join Date
    Nov 2003
    Posts
    300
    pbaldy= Thanks, I understand what you are saying, I was able to find some examples but ran into some problems / questions

    Here is what I have so far:

    Option Compare Database

    Private Sub UID_Click()????? OPEN????????

    'Sub DAO_FindUID(UID As String)
    Dim db As DAO.Database
    Dim qdef As DAO.QueryDef
    Set db = CurrentDb
    Set qdef = db.QueryDefs("qryPASSTHRU")
    qdef.SQL = "EXEC spClientID " & ???????????
    DoCmd.OpenQuery "qryPASSTHRU", , acReadOnly
    qdef.Close
    db.Close
     
    End Sub


    Since this is going into a report, that has an initial query with a user input of the ClientID, I guess I want to put the above code into the OPEN section????

    Next question is how do I actually pass that user variable to the code I have above? I guess with the & ????????????? that part I am not sure.


    THANKS!

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I typically do it from the code calling the report, but it can probably be done in the report's open event as well. For your text value it would likely look like:

    qdef.SQL = "EXEC spClientID '" & Forms!FormName.ControlName & "'"

    which assumes the value is in a form control somewhere. You don't to open the query, presuming it's the source of the report. Just open the report.
    Paul

  5. #5
    Join Date
    Nov 2003
    Posts
    300
    Okay, that makes sense.
    I have a query in the report that asks for the CLIENTID,
    simply [Enter CLIENTID]

    when I put that into the following:

    qdef.SQL = "EXEC spClientID '" & Forms!FormName.ControlName & "'"

    qdef.SQL = "EXEC spClientID '" & [Enter CLIENTID]

    I get an error: 2465
    Can't find the field '|' referred to in your expression.

    so, because it is a report and not a form, do I do this:?

    qdef.SQL = "EXEC spClientID '" & Report!ReportName.ControlName & "'"

    what is the ControlName going to be? the [Enter CLIENTID] ???

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The stored procedure can't interact with the user that way, nor can the passthrough. You will need to gather the user input some other way. Most of us use forms, since you have a lot more control that way (validate the input for instance). My example presumed the user had entered the value on a form. If you want to prompt them in a similar way, you can use an InputBox:

    qdef.SQL = "EXEC spClientID '" & InputBox("Enter CLIENTID", "Enter CLIENTID") & "'"
    Paul

  7. #7
    Join Date
    Nov 2003
    Posts
    300
    I am getting close!--- almost there!!!

    That line works great. It puts the CLIENTID in the Passthru query.

    The next problem I am having is that I am getting an error on the next line of code:

    DoCmd.OpenQuery "qryPASSTHRU", , acReadOnly

    Run time - 3325
    Pass-through query with ReturnsRecords property set to True did not return any records.

    I guess what I really want this line to do, it just RUN the qryPASSTHRU and not try to OPEN the query, I don't need to look at the records.

    Any thoughts?

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Like I said, you don't need to open the query, just make it the source of the report. After your code fixes the query, you'd have an OpenReport line.

    If you just open the query after this code has changed the SQL, does it return records?
    Paul

  9. #9
    Join Date
    Nov 2003
    Posts
    300
    If you just open the query after this code has changed the SQL, does it return records?

    I can answer this question first, No, it does not return records. What the Stored Procedure does is populate 3 SQL tables, and I use those tables as the source for the other 3 subreports on the main report. That's all

    Like I said, you don't need to open the query, just make it the source of the report. After your code fixes the query, you'd have an OpenReport line.

    I think I am unclear as to what you are saying here.



  10. #10
    Join Date
    Nov 2003
    Posts
    300
    I think I see what you are saying now..

    I don't need this line.

    DoCmd.OpenQuery "qryPASSTHRU", , acReadOnly

    I commented it out and the error is gone.

    Only strange problem is that I actually have to close and run the report twice to get the desired results.

    I have the VBA code in the LOAD part of the report.

    Should I have it somewhere else?

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I was on the wrong track. I typically use pass through queries as the source for a form or report. When I want to run a process like yours, I typically use an ADO command object. Yours might work though. In your case, you may want to open that query to run your process prior to opening the report (the load event may be too late; I would try it in the open event). Try changing the Returns Records property of the pass through to No and put your OpenQuery line back in.
    Paul

  12. #12
    Join Date
    Nov 2003
    Posts
    300

    Smile

    Thanks! That seemed to do the trick. I moved the code to OPEN and it gives me the data on the report (with 5 sub reports) instantly. Also, the error code is gone because of the Return Records property as you mentioned.


  13. #13
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Ah good, glad we got it working for you.
    Paul

Posting Permissions

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