Results 1 to 11 of 11

Passing Form Variable to Query

  1. #1
    allenrickson is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    7

    Passing Form Variable to Query

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

  2. #2
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    30,624
    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.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    allenrickson is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    7
    Quote Originally Posted by June7 View Post
    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?

  4. #4
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    30,624
    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.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    allenrickson is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    7
    Quote Originally Posted by June7 View Post
    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

  6. #6
    khalid's Avatar
    khalid is offline MS-Access Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2010
    Location
    Kuwait
    Posts
    244
    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

  7. #7
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    30,624
    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.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  8. #8
    allenrickson is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    7
    Quote Originally Posted by khalid View Post
    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

  9. #9
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    30,624
    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.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  10. #10
    allenrickson is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    7
    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.

  11. #11
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    30,624
    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.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

Please reply to this thread with any new information or opinions.

Similar Threads

  1. passing variable from modal form to main form
    By crowegreg in forum Forms
    Replies: 2
    Last Post: 06-03-2011, 04:34 PM
  2. Passing variable values to Stored Procedure
    By rodrigopcnet in forum Access
    Replies: 1
    Last Post: 04-14-2010, 09:35 AM
  3. Passing SQL result into variable
    By jonny in forum Access
    Replies: 3
    Last Post: 10-18-2009, 06:46 AM
  4. Passing a variable to a form
    By cjamps in forum Forms
    Replies: 0
    Last Post: 03-02-2009, 03:32 AM
  5. Passing a value from a variable to an update query
    By MUKUDU99 in forum Programming
    Replies: 0
    Last Post: 08-24-2008, 10:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums