Results 1 to 8 of 8
  1. #1
    Join Date
    May 2004
    Posts
    9

    Unanswered: pass parameter value from macro/VB to query

    Hi there folks,

    Does anybody know of a way that will let me pass a parameter value to a query from say VB from a command button on a form. Can this be done through the doCmd object??

    Many thanks

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What exactly do you need to do? If you have some complex vb that returns a specific value, you may be best off dynamically building the query at runtime. Otherwise you may be able to get by using simple parameter queries. What is it that you need to do?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2004
    Posts
    9

    Parameter passing to query

    I have a report based on a simple query based on a customer. The query has a parameter for a customer ID (primary key) and therefore only reports one record. I want to be able to call this report up from other forms in my database, and pass the customer ID (known at run-time) to the report via vb so the user does not have to input the ID again via the query input box.

    Many thanks for helping.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Check into "Input Parameters"

    Essentially you use a "?" instead of a prompt in your query. Then you can point to the form and control from which ot retrieve a value.

    Eg:

    SELECT * FROM yourTable WHERE yourID = ?

    Then in the INput Parameter section you could use:

    ? = Forms!yourform!yourIDtxtField.Value
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    May 2004
    Posts
    9
    That sounds great, but it assigns the query to the one textbox. Is there anway that I could have various textboxes in various forms and call the query from there passing the value in the textbox? Thanks very much for you help.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If you need to pass values from DIFFERENT text boxes on different forms all the time, I would recommend dynamically building the recordsource instead.

    Declare a global variable and use it to build your sql string. Eg:

    strSQL = "SELECT * FROM yourTable WHERE yourField = " & GlobalVar

    Then right before you open the report or whatever, you can set that GlobalVar to whatever value you'd like.

    If this is for a report, the code would go in the On Open event for the report, followed by:

    Me.RecordSource = strSQL
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    May 2004
    Posts
    9

    Thank You

    That worked a dream!! Thank you very much!!

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Hey hey!! I got one right!!

    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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