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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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:

Private Sub Form_Open(Cancel As Integer)
Dim rst as string
rst = "Select * from MyTable where  "[EstimatesWorkloadID]=" & _
           FirstForm![txtEstimatesWorkloadID]
Me.recordsource = rst
End Sub

Hope this can help

Reply | Quote

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

Reply | Quote