Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2007
    Posts
    17

    Unanswered: run query programmatically

    hello to everyone
    i have created an update query with the designer, i want to run it from a listbox's click event in vbscript, however i haven't found a way to do this, all i've found are methods using DAO but i know it is deprecated
    could you please tell me how to set the parameter and run the query programatically??
    thanks for your help

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    you need to build a event

    I would do it in the
    AfterUpdate of the listbox


    Private Sub listbox_AfterUpdate()
    '1st turn off warning
    'make shore you double check you qurey
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "the update query"
    'don't forget to turn the warning back on
    DoCmd.SetWarnings true
    '....
    '....
    '....
    End Sub

    parameter what are they

    what you can do is put textbox on the screen and then get the parameter in the query and point then to the textbox
    Last edited by myle; 02-14-07 at 05:03.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Feb 2007
    Posts
    17
    hello myle, thanks for your help, i've succesfully run the query!!
    but i still have a small problem, when the query runs it doesn't get the value from the listbox, it asks the user for that parameter, i've tried to set the right addres using the expression generator and i'm sure the path is right, but i still got the prompt.

    here is the query:
    Code:
    UPDATE productos SET productos!ocultar = True
    WHERE ((([productos]![mod])<>[Formularios]![productos]![modelosLista].[value]));
    it prompts for "[Formularios]![productos]![modelosLista].[value]"

    "[productos]![mod]" is a column in the table
    i'm pretty sure the path is right, so, could you tell me what i'm doing wrong?, this is one of the very last details and my proyect will be finished!!

    thanks for your help

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Not the appropriate way to point to the control. Here's a good reference:

    http://www.mvps.org/access/forms/frm0031.htm
    Paul

  5. #5
    Join Date
    Feb 2007
    Posts
    17
    hello pbaldy thanks for your link

    i haven't found a way to make it work, i've tried with the three options (mainform, sub1 and sub2) and still get the prompt, here is a quick explanation of how i'm doing it

    there is a listbox in the main form, in the click event of the listbox the query is called:
    Code:
    Private Sub modelosLista_Click()
        DoCmd.SetWarnings False
            DoCmd.OpenQuery "mostrarTodos"
            DoCmd.OpenQuery "esconderOtros" 'this is the one
        DoCmd.SetWarnings True
        [prods].Requery
    End Sub
    i created the query using the designer, this is how it looks in the sql view:
    Code:
    UPDATE productos SET productos!ocultar = True
    WHERE (((productos![mod])<>Me!modelosLista));
    as i said i've tryed with this options: Me!modelosLista, Me.Parent!modelosLista, Me.Parent.Parent!modelosLista and all of them pop up the prompt

    is there another thing i must consider?
    thanks for your help

    EDIT!!!:: i finally found how to do it: i just created a 'proxy' function in vbscript that gets the value, then set the function in the query like this:
    Code:
    function listId() as integer
         listId=Form_productos.modelosLista.value
    end function
    
    'in the query designer: 
    'UPDATE productos SET productos!ocultar = True
    'WHERE (((productos![mod])<>listId()));
    thank you guys now i'm very happy=D
    Last edited by ramone; 02-14-07 at 16:09.

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    this is what I do


    Private Sub listbox_AfterUpdate()
    Dim SQL as string

    'build the SQL line on the fly
    SQL = ""
    SQL = SQL & "UPDATE productos SET productos!ocultar = True "
    SQL = SQL & " WHERE ((([productos]![mod])<>'" & [Formularios]![productos]![modelosLista].[value] & "'));"

    note the ' and the " only if the Formularios]![productos]![modelosLista].[value] has text in it

    if number them

    SQL = SQL & " WHERE ((([productos]![mod])<>" & [Formularios]![productos]![modelosLista].[value] & " ));"



    '1st turn off warning
    'make shore you double check you qurey
    DoCmd.SetWarnings False
    'DoCmd.OpenQuery "the update query"
    DoCmd.RunSQL SQL
    'don't forget to turn the warning back on
    DoCmd.SetWarnings true
    '....
    '....
    '....
    End Sub
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Your problem was because "Me" is only valid within VBA. The query could simply reference the form, like:

    WHERE FieldName = Forms!FormName.ControlName
    Paul

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If I've followed correctly... You wanted your report to open after update on a list box... and you wanted to use the listbox contents as criteria in your query?

    Cant you simply set the criteria in the query as =Forms![Form Name]![Field Name]
    EDIT: Bad explaination - see attachment

    I've done that before, but I can't find the project I did it in...
    Attached Thumbnails Attached Thumbnails Criteria.bmp  
    George
    Home | Blog

Posting Permissions

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