Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    21

    Unanswered: modifying a parameter in a query and changing the recordsource?

    I have been looking around and I have found plenty of info on how to change parameters in a parameter query. But, I want to be able to have a listbox that will change a parameter in a query. The thing is that query is also the recordsource for the form. Any ideas on how to click on the item in the list box and get it to refresh the form with the new recordsource?

    Any help is appreciated. Thanks
    Brian

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    since you are going to need code to change the .recordsource, why not do the whole thing in code and ignore "parameter queries" (which are inherently ugly!).

    open your query in SQL view and copy/paste it into (e.g.) the _AfterUpdate() on your listbox.

    dim strSQL as string
    strSQL = "blah blah
    strSQL = strSQL & "blah blah
    ........and start stuffing the pasted SQL into strSQL (take care with spaces at the end of lines).
    myform.recordsource = strSQL
    myform.requery

    no doubt you'll get errors if you try to use string parameters, but you'll get there if you use
    ...table.field = '" & listbox & "'...
    to get the string value into quotes in your SQL.

    you are five minutes away from forgetting all about parameter queries: it's a great day.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Feb 2004
    Posts
    21
    Originally posted by izyrider
    since you are going to need code to change the .recordsource, why not do the whole thing in code and ignore "parameter queries" (which are inherently ugly!).

    open your query in SQL view and copy/paste it into (e.g.) the _AfterUpdate() on your listbox.

    dim strSQL as string
    strSQL = "blah blah
    strSQL = strSQL & "blah blah
    ........and start stuffing the pasted SQL into strSQL (take care with spaces at the end of lines).
    myform.recordsource = strSQL
    myform.requery

    no doubt you'll get errors if you try to use string parameters, but you'll get there if you use
    ...table.field = '" & listbox & "'...
    to get the string value into quotes in your SQL.

    you are five minutes away from forgetting all about parameter queries: it's a great day.

    izy


    Sample code:

    Private Sub List81_AfterUpdate()

    Dim strSQL As String
    strSQL = "Sql data from the sql view on the query?? goes here"
    strSQL = strSQL & "what goes here?"
    Me.RecordSource = "strSQL"
    Me.Requery

    End Sub


    As noted above I do not know what is required here:

    strSQL = "Sql data from the sql view on the query?? goes here"
    strSQL = strSQL & "what goes here?"

    Thank You for your reply,
    Brian

  4. #4
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    Originally posted by darkdante
    As noted above I do not know what is required here:
    strSQL = "Sql data from the sql view on the query?? goes here"
    strSQL = strSQL & "what goes here?"
    "what goes here?" is the parametre (the value provided by your list box).

    Hence, if your list box is called lstListBox1, you would like to put the current value of that one into the query, for example as the parametre to field1:

    strSQL = "(SELECT tbl1.field1, tbl1.field6 FROM tbl1 HAVING
    tbl1.field1 = '" & cstr(me.lstListBox1) & "' )"

    or cint(me.lstListBox1)

    Maybe better to assign the value of the list box to a variable, and refer to that one in the strSQL, like this:

    .... HAVING tbl1.field1 =" + cstr(ListBox1Variable)

    or + cint(ListBox1Variable)

    kedaniel.

  5. #5
    Join Date
    Feb 2004
    Posts
    21
    Originally posted by kedaniel
    "what goes here?" is the parametre (the value provided by your list box).

    Hence, if your list box is called lstListBox1, you would like to put the current value of that one into the query, for example as the parametre to field1:

    strSQL = "(SELECT tbl1.field1, tbl1.field6 FROM tbl1 HAVING
    tbl1.field1 = '" & cstr(me.lstListBox1) & "' )"

    or cint(me.lstListBox1)

    Maybe better to assign the value of the list box to a variable, and refer to that one in the strSQL, like this:

    .... HAVING tbl1.field1 =" + cstr(ListBox1Variable)

    or + cint(ListBox1Variable)

    kedaniel.

    haha..AWESOME! I got it working up to this point

    '" & cstr(me.lstListBox1) & "' )"
    Just had to through and extra ) in there.
    '" & cstr(me.lstlistbox1) & '" ))"

    Thanks for the help!!

Posting Permissions

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