Results 1 to 12 of 12
  1. #1
    Join Date
    May 2002
    Posts
    395

    Unanswered: Parameter Dialog Box Reference

    Could anybody tell me why am I getting the Parameter Dialog Box referencing the searchVal variable?

    Private Sub cmdSearch_Click()

    Dim DB As Database
    Dim QD As QueryDef
    Dim searchVal As Variant
    Dim MyRecordSource As String

    searchVal = ""
    searchVal = [Forms]![frmSearchValue]![txtSearchValue]


    MyRecordSource = "SELECT * FROM qrySearch WHERE qrySearch.poNum = searchVal"
    Me.RecordSource = MyRecordSource

    Set DB = DBEngine.Workspaces(0).Databases(0)
    On Error Resume Next
    DB.QueryDefs.Delete "qryResults"
    On Error GoTo 0

    Set QD = DB.CreateQueryDef("qryResults", MyRecordSource)
    Me.SetFocus

    End Sub

    Thank you!

  2. #2
    Join Date
    Jul 2002
    Posts
    36
    I think you need to change:

    MyRecordSource = "SELECT * FROM qrySearch WHERE qrySearch.poNum = searchVal"

    to

    MyRecordSource = "SELECT * FROM qrySearch WHERE qrySearch.poNum = '" & searchVal & "'"

    The way you have it, the recordsource thinks searchval is a column in the query.

    J

  3. #3
    Join Date
    May 2002
    Posts
    395

    Parameter Dialog Box referencing the searchVal variable

    Hi J,

    It is not working yet.
    I’m a little confuse with the quotation marks.
    For example:
    MyRecordSource = "SELECT * FROM qrySearch WHERE qrySearch.poNum = '" & searchVal & "';"

    What is the purpose of the single quotes in this code?

    Thanks!
    Alice

  4. #4
    Join Date
    May 2002
    Posts
    395

    Parameter Dialog Box referencing the searchVal variable

    Hi J,

    It is not working yet.
    I’m a little confuse with the quotation marks.
    For example:
    MyRecordSource = "SELECT * FROM qrySearch WHERE qrySearch.poNum = '" & searchVal & "';"

    What is the purpose of the single quotes in this code?

    Thanks!
    Alice

  5. #5
    Join Date
    Jul 2002
    Posts
    36
    The single quotes let you assign a value with quotes embeded in it.

    poNum = "A123"
    sql = "select * from PO where PO.ID = '" & poNum & "'"

    sql looks like this to Access:
    select * from PO where PO.ID = 'A123',

    and becuase PO.ID is alphnumerica, you must have quotes.

    As far as it not working, run your sub in the Immediate Window, put a break point in where you set myrecordsource and checak you record source string to make sure it looks right.

  6. #6
    Join Date
    May 2002
    Posts
    395

    Dialog Box

    Thank you so much for the explanations on the quotation marks.
    Now I know how they work.

    If you have a minute I'm still having trouble with parameter dialog box.

    I have an OpenForm button on a main form. When I clicked on it a Parameter Value Dialog Box pops up on the main form and prompted me to enter a value for a variable in the subform. I clicked on O.K. without enter a value the subform opens up.

    Why is it trying to obtain a value in the subform before it opens up?

    I didn't include the filter criteria in the OpenForm method. This is what I wrote in the OpenForm button to open the subform:
    DoCmd.OpenForm “frmSearchValue”


    Any ideas why?




  7. #7
    Join Date
    Jul 2002
    Posts
    36
    It sounds like you misspelled a column name in your query. Look at the query in your subform and make sure that the fields on the form that reference columns in the query are spelled correctly. Check you parent and child link fileds. The parameter dialog box should tell you what parameter it is looking for so that shold help you narrow it down.

  8. #8
    Join Date
    May 2002
    Posts
    395
    I checked the field names and they are fine.
    Can I supply you with a little more information on the problem?

    The parameter prompt does appear in the subform when click on directly.

    CmdSearch is the Search button on the subform. When the button is clicked it compares the value entered in the textbox txtSearchValue with qrySearch. The textbox is also located in the subform and the searchVal variable is used to store the entered value. The searchVal is the parameter value prompting by the dialog box.

    The problem seems to be on line #5, but I can’t see why?
    -The value entered in txtSearchValue is being stored into searchVal variable.
    -SQL statement(line #6) was executed and a record was found and displayed.

    Private Sub cmdSearch_Click()

    Dim DB As Database
    Dim QD As QueryDef
    Dim searchVal As String
    Dim MyRecordSource As String


    searchVal = Me![txtSearchValue]
    MyRecordSource = "SELECT * FROM qrySearch WHERE qrySearch.poNum = '" & searchVal & "';"

    Me.RecordSource = MyRecordSource

    Set DB = DBEngine.Workspaces(0).Databases(0)
    On Error Resume Next
    DB.QueryDefs.Delete "qryResults"
    On Error GoTo 0

    Set QD = DB.CreateQueryDef("qryResults", MyRecordSource)
    Me.SetFocus
    Me!lstDisplay.Requery


    End Sub

    If you can help I would appreciated it.
    Thank you.

  9. #9
    Join Date
    Jul 2002
    Posts
    36
    Where is the text box [SerachValue]? If it is on the main form and the cmdSearch button is on the subform, try changing:


    searchVal = Me![txtSearchValue]

    to

    searchVal = forms!frmSearchValue![txtSearchValue]

    When you click on the cmdSearch button, which is on the subform, you set focus to the subform. Just fyi, the me! syntax references controls on the active form or report.

    Other than that, I can't see it.

    J

  10. #10
    Join Date
    Jul 2002
    Posts
    36
    scratch that. I don't think what I just said will work. (I'm trying to test it now).

    J

  11. #11
    Join Date
    May 2002
    Posts
    395

    Thanks!

    Thank you for looking into it.
    Have a good weekend!

    Alice

  12. #12
    Join Date
    May 2002
    Posts
    395

    The problem is fixed.

    The problem is fixed.
    I had the SQL statement containing the searchVal as the Recordsource of the subform, but since it is not declared @ the main form when I clicked on the command to open the subform the parameter dialog box popped up.

    Thank you very much for your time and effort to help me.
    You Have a Nice Day!

Posting Permissions

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