Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Dec 2000
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Opening a form based on parameter query (97/SR-2)

    Hi,

    I have some code (below) for a command button on a form which opens up a second form based on a filter. What I want to do is open the second form based on a parameter query instead of a filter.

    Specifically, what I mean is I want to change the underlying query which the second form is based on to include a WHERE condition, and then use a parameter to supply the criteria. Changing the query to a parameter query is no sweat. What I can't figure out is how to supply the parameter value to the query in the code below (assuming now that the second form is based on a parameter query).

    '******************* START CODE *******************
    Private Sub cmdEstimates_Click()
    On Error GoTo Err_cmdEstimates_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmEstimatesWorkload"

    stLinkCriteria = "[EstimatesWorkloadID]=" & Me![txtEstimatesWorkloadID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_cmdEstimates_Click:
    Exit Sub

    Err_cmdEstimates_Click:
    MsgBox Err.Description
    Resume Exit_cmdEstimates_Click
    End Sub
    '******************* END CODE *******************

    Thanks,

    Stephan

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a form based on parameter query (97/SR-2)

    Stephan,

    I don't understand where your problem is.
    You don't have to change the query to a parameter query because the variable stLinkCriteria contains the where condition that is add to the query when you open your form.
    Your code is correct and don't work with a filter but with a where condition.
    Francois

  3. #3
    Star Lounger
    Join Date
    Dec 2000
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a form based on parameter query (97/SR-2)

    Francois,

    Thanks for your reply.

    Just to clarify, you're right, there is no problem with the existing code. I just need to figure out how to do the same thing a different way. The reason for this is if you open the second form with a filter (as it's being done in my current code), the user can remove the filter himself using Access's Remove Filter/Sort command once the second form opens. I need to avoid this possibility.

    That's why I would like to make the underlying query for the second form a parameter query with a WHERE condition, and in my code pass the value to the parameter query when the second form opens.

    I hope this clarifies what I'm trying to do.

    Thanks again,

    Stephan

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a form based on parameter query (97/SR-2)

    Stephan,

    You are right. I have never tested this but if you set the Allow Filters property of your form to No, the filtering will be disabled.

    You can also create the sql statement in the on open event of your second form:
    <pre>Private Sub Form_Open(Cancel As Integer)
    Dim rst as string
    rst = "Select * from MyTable where "[EstimatesWorkloadID]=" & _
    FirstForm![txtEstimatesWorkloadID]
    Me.recordsource = rst
    End Sub</pre>

    Hope this can help
    Francois

  5. #5
    Star Lounger
    Join Date
    Dec 2000
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a form based on parameter query (97/SR-2)

    Francois,

    Thanks!

    Using the recordsource property to change the second form's record source was precisely the type of functionality I was looking for. I really appreciate your help.

    Stephan

Posting Permissions

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