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

    Unanswered: Parameter Value 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. the subform then opens up.
    Why is it trying to look for 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?

  2. #2
    Join Date
    Feb 2002
    Posts
    403
    I assume when you click on the subform directly the parameter prompt does not appear.

    The parameter prompt may be for a record associated with the main form, and Access does not realise the parameter value has been supplied by the main form. Maybe you can control this by loading the subform from code in the OnCurrent event of the form.

  3. #3
    Join Date
    May 2002
    Posts
    395

    parameter prompt

    Can I supply you with a little more information on the problem?

    I just checked. 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.

  4. #4
    Join Date
    Feb 2002
    Posts
    403
    I think I see the issue here:

    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 subform is based on qrySearch. The subform contains cmdSearch which is intended to serach the database based on searchVal.

    If the above are correct the problem is at qrySearch. Remove the SearchVal from the query criteria. I think you have left it there and forgotten about it. I do not think your code is being called when the subform opens.

    HTH

  5. #5
    Join Date
    May 2002
    Posts
    395

    Parameter Dialog Box

    Hi HTH,

    If I remove the SearchVal from the query criteria and replace it with [Forms]![frmSearchValue].[txtSearchValue] it will work. But my the SQL statement will be very long and Access will only make comparison up to six fields in the query.

    Example
    MyRecordSource = "SELECT * FROM qrySearch WHERE (((qrySearch.poNum)=[Forms]![frmSearchValue].[txtSearchValue])) OR (((qrySearch.company)=[Forms]![frmSearchValue].[txtSearchValue])) OR (((qrySearch.partNum)=[Forms]![frmSearchValue].[txtSearchValue])) OR (((qrySearch.serialNum)=[Forms]![frmSearchValue].[txtSearchValue])) OR (((qrySearch.partFor)=[Forms]![frmSearchValue].[txtSearchValue])) OR (((qrySearch.Description)=[Forms]![frmSearchValue].[txtSearchValue]));"

    The best way I know is to assign the txtSearchValue to the SearchVal variable.
    searchVal = Me![txtSearchValue] ‘Me being the subform

    The textbox value entered which is in the subform is being stored in searchVal. I can’t see why the dialog box is prompting for searchVal before the form opens up.

    I also need this field to be a variable so I can concatenate it with Like operator as well as Wild Card character (*) in the criteria.

    Here is my code again-

    Private Sub cmdSearch_Click()

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

    '''searchVal = ""
    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
    End Sub



    Thank you again.



  6. #6
    Join Date
    Feb 2002
    Posts
    403
    I am not sure why this is happening either, but I wonder if:

    if Me![txtSearchValue]<>"" then

    '''searchVal = ""
    searchVal = Me![txtSearchValue]

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

    End if

    or

    MyRecordSource = "Select * From qrySearch " & _
    "WHERE qrySearch.poNum= [Forms]![frmSearchValue].[txtSearchValue]"



    May stop it. Have you walked through the code to see if a specific event is triggering the code?

    The strSQL as shown is only searching on PONum so the second SQL will also work.

  7. #7
    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 click 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
  •