Passing Form Variable to Query

[allenrickson]

I have a form which takes in a userID through a textbox, the user then clicks a button which forwards them to a report. I'd like to pass on that userID variable so that I could call it in my query:

execute dbo.someStoredProcedure
@UserID = [Forms]![FormName]![userID]



However I keep getting a syntax error and I'm not sure the variable is being passed on...

[June7]

Pass a value to report with WHERE CLAUSE or OpenArgs arguments. What is purpose of this value, to filter the records for report - use WHERE clause.

[allenrickson]

Pass a value to report with WHERE CLAUSE or OpenArgs arguments. What is purpose of this value, to filter the records for report - use WHERE clause.

So I have a form where you type in a value, which is the userID, then when you click a button it takes you to a report where it calls some SQL Stored Procedures. So it would look like "execute dbo.blahblah @userID =[variable passed goes here]" Not sure where to go from here. How would that WHERE clause look?

[June7]

Not sure what you mean by report 'calls some SQL stored procedures'. A query can be the RecordSource for a report.

I would open a report with:
DoCmd.OpenReport "reportnamehere", , , "fieldnamehere=" & form controlname here

Post your button procedure for analysis.

[allenrickson]

Not sure what you mean by report 'calls some SQL stored procedures'. A query can be the RecordSource for a report.

I would open a report with:
DoCmd.OpenReport "reportnamehere", , , "fieldnamehere=" & form controlname here

Post your button procedure for analysis.

Ok, So I should note that I'm new to Access if you haven't already figured that out.

So I have 3 things here. One is form which is suppose to pass on a variable to a report. This report has a bunch of subreports that say "Query.somequery". When it goes to these queries I want to be able to use the userID variable from the form.

So here is a the procedure:

FORM:
Private Sub FindCalReport_Click()
DoCmd.OpenReport "CalibReport", acViewReport,,,acWindowNormal, Me.user_ID
End Sub


CALIB REPORT:

No code, just subreports that say "Query.CalibQuery", this goes to the CalibQuery


CALIB QUERY:

execute dbo.Calibate
@uID = ????

So I'm not sure what to put in ???? to get the userID i passed on in the OpenArgs

[khalid]

I think you mixed up the query with SQL store procedure which asks for Input parameter value @uID.

June has given you the clear code:
DoCmd.OpenReport "reportnamehere", , , "fieldnamehere=" & Forms!Form1.Field1

[June7]

Still doesn't make sense. Do you want to attach project to post and I will look at?

How can a report run a query that is not its RecordSource? Either you are trying something that can't be done or can be and I have never encountered it.

[allenrickson]

I think you mixed up the query with SQL store procedure which asks for Input parameter value @uID.

June has given you the clear code:
DoCmd.OpenReport "reportnamehere", , , "fieldnamehere=" & Forms!Form1.Field1

So when I do this, I get an error telling me that the form or report isn't bound to a table or query. Which is right, because there are multiple subreports in this one report, each subreport is calling a query that runs that stored procedure. I'm just really confused on how to wire this all together

[June7]

A main report can have a RecordSource and then subreports can be synchronized with the main report by the Master/Child link properties of the subform container. Of course, this depends on the nature of the data.

Still not getting what you mean by 'each subreport is calling a query that runs that stored procedure. Again, if you want to attach project, I will look at. Run Compact & Repair, put in zip folder if too big.

[allenrickson]

So I wish I could send this information out to the public, but unfortunately that's not possible.

Let me try to explain this one more time.

So I start with a blank document. First I connect my access project to the ODBC so I can access all the data in the sql database, this all works well. The database contains a bunch of stored procedures as well. So now I make my first object, a Query. I will call this query, Calibrate. In this query we will have it do:

execute dbo.calibrationdatabase @userID = 12345

So that means the query will execute the stored procedure and define the input userID as 12345 (userID is required by the store procedure to actually run).

Now, if I run this, I get some nice table. However this is just one of very many possible table, so as you change the userID you get different tables out of this stored procedure. Works great, but the userID is hard coded in.

Now I make a blank report. I then create a blank subreport within that report. It will be unbound so I click on the box, and type in "Query.Calibrate". So if I now go to run the report, the report will run that subreport and which is really running the Calibrate query. So the final report will just be a table. The same table from before when I ran the query.

This is great, except now I want to give the user the option to pick the userID. So that's when I created the form, which asks the user for a userID as a text input and when they click a button it opens the report. However, my problem is I can't pass on that input userID to the report, which should be passed on to the subreport, which should be passed on to the query so I can define it for the store procedure.

[June7]

Makes my head spin. Can't help if I can't see it. Need code posted or the project. Don't care about your data. Make copy of database, delete data, put in dummy records for testing, run Compact & Repair. Otherwise, maybe someone else will grasp what you are doing and offer solution.