Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    build a query from a form (Access97)

    I would like to build a query or SQL statement from the input a user selects on a form. The query will draw data from one of 12 possible tables based on the users selection. The form has a combo box that offers the user 12 choices. How do I use the AfterUpdate event to store the value of the selection somewhere to be used in a query or SQL statement?

    Thanks,
    aap2

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: build a query from a form (Access97)

    Which AfterUpdate event were you thinking of? Each control has one and so does the form itself. You don't have to store the value anywhere if the code to build the SQL is in the form where they make the selection. All you have to do is refer to the value in the control. If you want to store the value and use it later (leaving aside why for the moment) you would probably want to store it in a table that you use for this specific purpose.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: build a query from a form (Access97)

    Below is a more detailed description of what I am attempting.

    The form has a combo box (cboSelectTable) where the user selects an item. The AfterUpdate event of the combo box is what I wish to use. The value of the item selected will determine which table will be searched in the query. The combo box RowSource Type is "Table/Query" and the
    SELECT DISTINCTROW tblClientLookUpTablesIndex.TableName, Row Source looks like this
    tblClientLookUpTablesIndex.TableDescription FROM tblClientLookUpTablesIndex;
    the TableDescription field contains valid table names.

    cboSelectTable_AfterUpdate()
    'create variable to hold the valid table name
    dim mstrLUTable as string
    Here is the part I am wondering about. How do I make this assignment so that the variable
    "mstrLUTable" holds a valid table name such as "tblLU0301". And, can you use a variable name in a SQL statement?

    'assign the value of the users selection to the variable name
    ????mstrLUTable = cboSelectTable.value or something like this????

    Then the varialbe mstrLUTable is filled into this SQL Statement.

    SELECT mstrLUTable.Group, mstrLUTable.CBO, mstrLUTable.DIVISION, [LU Program Info].[SERVICE LINE], mstrLUTable.[HOSPITAL ID], mstrLUTable.[HOSPITAL NAME], [LU Program Info].[PLACEMENT NO], [LU Program Info].[PLACEMENT DATE], [LU Program Info].STATE, Stats.[Client No], Stats.[Client Name], Sum(Stats.[MTD # Listed]) AS [SumOfMTD # Listed], Sum(Stats.[MTD $ Listed]) AS [SumOfMTD $ Listed], Sum(Stats.[MTD $ Collected]) AS [SumOfMTD $ Collected], Sum(Stats.[MTD $ Fees]) AS [SumOfMTD $ Fees], Sum(Stats.[YTD $ Listed]) AS [SumOfYTD $ Listed], Sum(Stats.[YTD $ Collected]) AS [SumOfYTD $ Collected], Sum(Stats.[YTD $ Fees]) AS [SumOfYTD $ Fees], Avg(Stats.Age) AS AvgOfAge, Stats.Date
    FROM (mstrLUTable LEFT JOIN Stats ON mstrLUTable.[CLIENT NO] = Stats.[Client No]) LEFT JOIN [LU Program Info] ON mstrLUTable.[CLIENT NO] = [LU Program Info].[CLIENT NO]
    GROUP BY mstrLUTable.Group, mstrLUTable.CBO, mstrLUTable.DIVISION, [LU Program Info].[SERVICE LINE], mstrLUTable.[HOSPITAL ID], mstrLUTable.[HOSPITAL NAME], [LU Program Info].[PLACEMENT NO], [LU Program Info].[PLACEMENT DATE], [LU Program Info].STATE, Stats.[Client No], Stats.[Client Name], Stats.Date
    ORDER BY [LU Program Info].[SERVICE LINE];

    Once the SQL statement is built, based on the user selection, I want to use that statement in the On Click event of a command button.

    Private Sub cmdRunSearch_Click()
    On Error GoTo Err_cmdRunSearch_Click

    Dim stSQLstatement As String


    stSQLStatement = the sql statement above

    Run the search

    End Sub

    Many thanks,
    aap2

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: build a query from a form (Access97)

    When you say you want to "Run the Search", what do you mean? How do you want to run the search, via a DoCmd.RunSql (which would not achieve much) or maybe an OpenRecordset in conjuction with other code.

    If the latter, then define the "Dim stSQLStatement as String" immediately after the Option Explicit (if you don't have Option Explicit you are asking for trouble). Then in the cboSelectTable_AfterUpdate event do the following:

    stSQLStatement = "SELECT mstrLUTable.Group, mstrLUTable.CBO, mstrLUTable.DIVISION, [LU Program Info].[SERVICE LINE], mstrLUTable.[HOSPITAL ID], mstrLUTable.[HOSPITAL NAME], [LU Program Info].[PLACEMENT NO], [LU Program Info].[PLACEMENT DATE], [LU Program Info].STATE, Stats.[Client No], Stats.[Client Name], Sum(Stats.[MTD # Listed]) AS [SumOfMTD # Listed], Sum(Stats.[MTD $ Listed]) AS [SumOfMTD $ Listed], Sum(Stats.[MTD $ Collected]) AS [SumOfMTD $ Collected], Sum(Stats.[MTD $ Fees]) AS [SumOfMTD $ Fees], Sum(Stats.[YTD $ Listed]) AS [SumOfYTD $ Listed], Sum(Stats.[YTD $ Collected]) AS [SumOfYTD $ Collected], Sum(Stats.[YTD $ Fees]) AS [SumOfYTD $ Fees], Avg(Stats.Age) AS AvgOfAge, Stats.Date
    FROM (" _
    & mstrLUTable & _
    " LEFT JOIN Stats ON mstrLUTable.[CLIENT NO] = Stats.[Client No]) LEFT JOIN [LU Program Info] ON mstrLUTable.[CLIENT NO] = [LU Program Info].[CLIENT NO]
    GROUP BY mstrLUTable.Group, mstrLUTable.CBO, mstrLUTable.DIVISION, [LU Program Info].[SERVICE LINE], mstrLUTable.[HOSPITAL ID], mstrLUTable.[HOSPITAL NAME], [LU Program Info].[PLACEMENT NO], [LU Program Info].[PLACEMENT DATE], [LU Program Info].STATE, Stats.[Client No], Stats.[Client Name], Stats.Date
    ORDER BY [LU Program Info].[SERVICE LINE];"

    Don't forget your " _" (space, underscore) at the end of each line, I cannot get the formatting correct in this response. I would appreciate someone's help for this.

    Then your cmdRunSearch_Click event could look like:

    Private Sub cmdRunSearch_Click()
    On Error GoTo Err_cmdRunSearch_Click

    dim dbs as database, rs as recordset

    set dbs=currentdb
    set rs=dbs.openrecordset(stSQLStatement)

    'Run the search
    do while not rs.eof
    ... your loop code in here
    rs.movenext
    loop

    End Sub

    Hope this all helps,
    Pat

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: build a query from a form (Access97)

    Pat,

    Option Explicit is there and I will create a recordset. Thanks for your assistance. I'll let you know how it works out.
    aap2

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: build a query from a form (Access97)

    Where I have the line
    & mstrLUTable & _

    I really meant
    & cboSelectTable & _

    Pat

Posting Permissions

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