Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Location
    Pembroke Pines, Florida, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo, Parameters, and SQL queries (Access 2002 SP1)

    Hi all,

    New problem of the day. :

    I have a search form with unbound text boxes and bound combo boxes. The form has code to take the input from the user in each of these controls and builds a WHERE condition which is stored in a public variable. This is then used to open up a results form with the public variable as the WHERE condition for the form.

    This works very well with the unbound controls. However, when the user selects an item from the combo box, the results form prompts for a Parameter from the user. Why is this? And how can I prevent it?

  2. #2
    New Lounger
    Join Date
    Jan 2003
    Location
    Pembroke Pines, Florida, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo, Parameters, and SQL queries (Access 2002 SP1)

    I played with this some more.

    I changed one of the combo boxes to a plain text box, and manually input a test value to search for. I was still requested for a Parameter. So, the issue is not the use of a combo box, but rather the field itself.

    Upon further investigation, all search fields that result in this Parameter Value request are specified in the table as Table/Query or Value List in their Row Source.

    So now I know what, but still not why, or how to get around it. [img]/forums/images/smilies/sad.gif[/img]

  3. #3
    New Lounger
    Join Date
    Jan 2003
    Location
    Pembroke Pines, Florida, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo, Parameters, and SQL queries (Access 2002 SP1)

    Some more research (and forum reading) and I think I have a handle on the actual question to ask (which is always the greater part of the problem) <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    The following code is a stripped down version of what I have for the Search_Click event once a user inputs some search criteria and clicks Search.

    -------------------------------------------
    Dim dbClient As DAO.Database
    Dim rcdClient As DAO.Recordset

    gstrSearch = ""

    If Not Me!SearchID = "" Then
    If gstrSearch = "" Then
    gstrSearch = "[IDNo] LIKE " & Chr$(34) & Me!SearchID
    Else
    gstrSearch = gstrSearch & " AND [IDNo] like " & Chr$(34) & Me!SearchID
    End If

    If Right$(Me!SearchID, 1) = "*" Then
    gstrSearch = gstrSearch & Chr$(34)
    Else
    gstrSearch = gstrSearch & "*" & Chr$(34)
    End If
    End If

    If Not Me!SearchClientTypeDBID = "" Then
    If gstrSearch = "" Then
    gstrSearch = "[ClientTypeDB_ID] = " & Chr$(34) & Me!SearchClientTypeDBID
    Else
    gstrSearch = gstrSearch & " AND [ClientTypeDB_ID] = " & Chr$(34) & Me!SearchClientTypeDBID
    End If

    gstrSearch = gstrSearch & Chr$(34)
    End If

    Set dbClient = CurrentDb
    Set rcdClient = dbClient.OpenRecordset("SELECT DISTINCTROW Client.ClientDB_ID FROM Client WHERE " & gstrSearch & ";")

    DoCmd.OpenForm "frmSearchClientSummary", , , gstrSearch
    DoCmd.Close acForm, Me.Name

    rcdClient.Close
    Set dbClient = Nothing
    Set rcdClient = Nothing
    -------------------------------------------

    The form allows the user to either enter a Client ID, or a Client Type, and then click Search to open up a form with the search results.

    A search for Client ID works fine, but the ClientTypeDB_ID always results in a Parameter Value box request. How do I prevent that?

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

    Re: Combo, Parameters, and SQL queries (Access 2002 SP1)

    Do you, in fact, have a field called ClientTypeDB_ID in the Client table? That persistent parameter usually means you've misspelled the name of a field or entered one that doesn't exist.
    Charlotte

  5. #5
    New Lounger
    Join Date
    May 2002
    Location
    Gen, Gen, Switzerland
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo, Parameters, and SQL queries (Access 2002 SP1)

    Another potential problem is if you use a join query (two or more tables) and the field ClientTypeDB_ID is a field used to join to tables.
    In that case you have to qualified the field with the name of the table in your where string.
    For sample: TableName.ClientTypeDB_ID

  6. #6
    New Lounger
    Join Date
    Jan 2003
    Location
    Pembroke Pines, Florida, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo, Parameters, and SQL queries (Access 2002 SP1)

    /sigh

    Never mind this ... were having an extremely 'blonde' moment (and you hit the nail on the head Charlotte). [img]/forums/images/smilies/smile.gif[/img]

Posting Permissions

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