Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Command Button Multiple Query Run?

    I would like to run all three of these small queries with the selection depress of one command button on my form.

    Any Ideas . . . ?

    Rick

    Code:
    SELECT tblPreScmls.STREETNUM, tblPreScmls.STREETNAME, tblPreScmls.CITY, tblPreScmls.CLOSEDDATE, tblPreScmls.LISTPRICE, tblPreScmls.SALESPRICE, tblPreScmls.AGENTLIST, tblPreScmls.P_OFFICENAME, tblPreScmls.AGENTSELL, tblPreScmls.P_OFFICENAMESELL INTO tblAgentRecs
    FROM tblPreScmls
    WHERE (((tblPreScmls.AGENTLIST)="U13549") AND ((tblPreScmls.AGENTSELL)<>"U13549"));


    Code:
    INSERT INTO tblAgentRecs ( STREETNUM, STREETNAME, CITY, CLOSEDDATE, LISTPRICE, SALESPRICE, AGENTLIST, P_OFFICENAME, AGENTSELL, P_OFFICENAMESELL )
    SELECT tblPreScmls.STREETNUM, tblPreScmls.STREETNAME, tblPreScmls.CITY, tblPreScmls.CLOSEDDATE, tblPreScmls.LISTPRICE, tblPreScmls.SALESPRICE, tblPreScmls.AGENTLIST, tblPreScmls.P_OFFICENAME, tblPreScmls.AGENTSELL, tblPreScmls.P_OFFICENAMESELL
    FROM tblPreScmls
    WHERE (((tblPreScmls.AGENTLIST)<>"U13549") AND ((tblPreScmls.AGENTSELL)="U13549"));

    Code:
    INSERT INTO tblAgentRecs ( STREETNUM, STREETNAME, CITY, CLOSEDDATE, LISTPRICE, SALESPRICE, AGENTLIST, P_OFFICENAME, AGENTSELL, P_OFFICENAMESELL )
    SELECT tblPreScmls.STREETNUM, tblPreScmls.STREETNAME, tblPreScmls.CITY, tblPreScmls.CLOSEDDATE, tblPreScmls.LISTPRICE, tblPreScmls.SALESPRICE, tblPreScmls.AGENTLIST, tblPreScmls.P_OFFICENAME, tblPreScmls.AGENTSELL, tblPreScmls.P_OFFICENAMESELL
    FROM tblPreScmls
    WHERE (((tblPreScmls.AGENTLIST)="U13549") AND ((tblPreScmls.AGENTSELL)="U13549"));

  2. #2
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    I tried this but it didn't work!

    Dim strSQL As String
    INSERT INTO tblAgentRecs ( STREETNUM, STREETNAME, CITY, CLOSEDDATE, LISTPRICE, SALESPRICE, AGENTLIST, P_OFFICENAME, AGENTSELL, P_OFFICENAMESELL )
    SELECT tblPreScmls.STREETNUM, tblPreScmls.STREETNAME, tblPreScmls.CITY, tblPreScmls.CLOSEDDATE, tblPreScmls.LISTPRICE, tblPreScmls.SALESPRICE, tblPreScmls.AGENTLIST, tblPreScmls.P_OFFICENAME, tblPreScmls.AGENTSELL, tblPreScmls.P_OFFICENAMESELL
    FROM tblPreScmls
    WHERE (((tblPreScmls.AGENTLIST)<>"U13549") AND ((tblPreScmls.AGENTSELL)="U13549"));
    CurrentDb.Execute strSQL

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by Rick Schreiber
    I would like to run all three of these small queries with the selection depress of one command button on my form.

    Any Ideas . . . ?
    Is there any reason you can't just do:
    Code:
    Dim strSQL1 As String, strSQL2 As String, strSQL3 As String
    strSQL1 = ...
    strSQL2 = ...
    strSQL3 = ...
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL1
    DoCmd.RunSQL strSQL2
    DoCmd.RunSQL strSQL3
    DoCmd.SetWarnings True
    in the On Click Event?
    Me.Geek = True

  4. #4
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Tried your suggestion nckdryr

    I think I have a problem of lines being too long, quotes in the wrong place, incorrectline rapping. Some lines are flagged in red. I just can't figure it out.

    Any ideas?

    Private Sub Command57_Click()
    Dim strSQL1 As String, strSQL2 As String, strSQL3 As String
    strSQL1 = "SELECT tblPreScmls.STREETNUM AS [St No], tblPreScmls.STREETNAME AS StName, tblPreScmls.CITY, tblPreScmls.CLOSEDDATE AS Dte, tblPreScmls.LISTPRICE AS AskPrice, tblPreScmls.SALESPRICE AS SalePrice, tblPreScmls.AGENTLIST AS ListID, tblPreScmls.P_OFFICENAME AS ListName, tblPreScmls.AGENTSELL AS SellID, tblPreScmls.P_OFFICENAMESELL AS SellName INTO tblAgentRecs"
    FROM tblPreScmls
    WHERE (((tblPreScmls.CLOSEDDATE) Between [Enter Start Date] And [Enter End Date]) AND ((tblPreScmls.AGENTLIST)="U13549") AND ((tblPreScmls.AGENTSELL)<>"U13549"));"
    strSQL2 = "INSERT INTO tblAgentRecs ( [St No], StName, CITY, Dte, AskPrice, SalePrice, ListID, ListName, SellID, SellName )"
    SELECT tblPreScmls.STREETNUM, tblPreScmls.STREETNAME, tblPreScmls.CITY, tblPreScmls.CLOSEDDATE, tblPreScmls.LISTPRICE, tblPreScmls.SALESPRICE, tblPreScmls.AGENTLIST, tblPreScmls.P_OFFICENAME, tblPreScmls.AGENTSELL, tblPreScmls.P_OFFICENAMESELL
    FROM tblPreScmls
    WHERE (((tblPreScmls.CLOSEDDATE) Between [Enter Start Date] And [Enter End Date]) AND ((tblPreScmls.AGENTLIST)<>"U13549") AND ((tblPreScmls.AGENTSELL)="U13549"));"
    strSQL3 = "INSERT INTO tblAgentRecs ( [St No], StName, CITY, Dte, AskPrice, SalePrice, ListID, ListName, SellID, SellName )"
    SELECT tblPreScmls.STREETNUM, tblPreScmls.STREETNAME, tblPreScmls.CITY, tblPreScmls.CLOSEDDATE, tblPreScmls.LISTPRICE, tblPreScmls.SALESPRICE, tblPreScmls.AGENTLIST, tblPreScmls.P_OFFICENAME, tblPreScmls.AGENTSELL, tblPreScmls.P_OFFICENAMESELL
    FROM tblPreScmls
    WHERE (((tblPreScmls.CLOSEDDATE) Between [Enter Start Date] And [Enter End Date]) AND ((tblPreScmls.AGENTLIST)="U13549") AND ((tblPreScmls.AGENTSELL)="U13549"));"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL1
    DoCmd.RunSQL strSQL2
    DoCmd.RunSQL strSQL3
    DoCmd.SetWarnings True
    End Sub


    Thanks Much . . . Rick

  5. #5
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    The first problem you are going to have is that you can not run a select query via the RunSQL method.
    Darasen

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Darasen, I think you'll find he hasn't tried.

    Rick, for starters you need to change the "inner" double quotes to single quotes ("U13549"). Did my quick glance at the SQL miss something, or is the only difference between those 3 the WHERE clause? It would seem that one query with

    ...AGENTLIST='U13549' OR AGENTSELL='U13549'

    would accomplish the same goal.
    Paul

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    here is how your post#2 should have looked:
    Code:
    Dim strSQL As String
    strSQL = "INSERT INTO tblAgentRecs ( STREETNUM, STREETNAME, CITY, CLOSEDDATE, LISTPRICE, " _
           & "SALESPRICE, AGENTLIST, P_OFFICENAME, AGENTSELL, P_OFFICENAMESELL ) " _
           & "SELECT STREETNUM, STREETNAME, CITY, CLOSEDDATE, LISTPRICE, SALESPRICE, " _
           & "AGENTLIST, P_OFFICENAME, AGENTSELL, P_OFFICENAMESELL " _
           & "FROM tblPreScmls WHERE (AGENTLIST<>'U13549') And (AGENTSELL='U13549');"
    CurrentDb.Execute strSQL
    note the single quotes around the string values (as pbaldy said)
    note the line continuation (_) and concatenation (&)
    Code:
    strSQL = "blahbla<space>"<space>_
           &<space>"blahbla<space>"<space>_
           &<space>"blahbla;"
    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Eventually . . . DoCmd.OpenQuery . . . etc.

    What I ended up doing was creating all three of the queries and then placing this in the Button.

    It's quite fast. Is this acceptable or should I consider something else?

    Code:
    Private Sub Command57_Click()
    DoCmd.OpenQuery "qryFindAgentRecsList"
    DoCmd.OpenQuery "qryFindAgentRecsSell"
    DoCmd.OpenQuery "qryFindAgentRecsListSell"
    End Sub
    Thanks . . . Rick

  9. #9
    Join Date
    Mar 2006
    Posts
    163
    Rick

    Of course it's acceptable, if it works.

    The only problem might be that you've hardcoded the values for the criteria.

    If you wanted to change those values you would either need to use parameters or construct the queries in code.

Posting Permissions

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