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
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.
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:
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.
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.
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.