Subform Filter / Update Issues

[mrubel99]

I have a subform built into a form in Access 2007. I would like the user to be able to filter the subform using their own criteria by right clicking the column header in the subform. Once the user has the subform record set filtered as they want I would like them to be able to click a button on the main form that will set the yes/no field [EXCLUDE_LINE] in the filtered subfom data set to true.

Here is the code I have been trying to get to work and it keeps resulting in an error "Few to parameters, Expected 1"? I have searched and tried some different code as well with the same results. Any help is greatly appreciated.

Private Sub Command196_Click()
Dim strSql As String

strSql = "UPDATE tblUtilizationDataAll SET EXCLUDE_LINE = -1 "
strSql = strSql & "WHERE PROJECT_NAME = '" & Me.Text81 & "' And " & Me.tblUtilizationDataAll_subform.Form.Filter & ";"
CurrentDb.Execute strSql

Me.tblUtilizationDataAll_subform.Requery
Me.Requery

End Sub

Thanks - Mike

[Stewart Ross]

...

Private Sub Command196_Click()
Dim strSql As String
 
strSql = "UPDATE tblUtilizationDataAll SET EXCLUDE_LINE = -1 "
strSql = strSql & "WHERE PROJECT_NAME = '" & Me.Text81 & "' And " & Me.tblUtilizationDataAll_subform.Form.Filter & ";"
CurrentDb.Execute strSql
 
Me.tblUtilizationDataAll_subform.Requery
Me.Requery
 
End Sub

Hi Mike. You're referring to the subform's filter property, which is a string value, but unlike the other part of your WHERE clause you are not using single quotes on either side to indicate a string literal. Replace what is listed as line 5 above with:
[code=vb]strSql = strSql & "WHERE PROJECT_NAME = '" & Me.Text81 & "' And '" & Me.tblUtilizati onDataAll_subfo rm.Form.Filter & "';"[/code]
-Stewart

[mrubel99]

Thank you Stewart for the response. The query is not picking up the criteria from the filter for some reason. The code runs but updates all of the records under each project name?

[Stewart Ross]

Thank you Stewart for the response. The query is not picking up the criteria from the filter for some reason. The code truns but updates all of the records under each project name?

Hi Mike. Your code does not filter the subform; it simply picks up any filter that is already set and applies it as a WHERE clause to your update of the records. I did not comment on this before, as it seemed to me that you would not have used an UPDATE statement unless you really needed to change values in records.

If you could let us know what the main form does, and what you really want to achieve when you mention filtering the subform I am sure we can advise you on how to go about it. It looks to me that the UPDATE stetement is an entirely unnecessary step if what you really want is not to update anything but to apply a filter to your subform. To filter the form you need to set its filter first, then set the form's filteron property true.

I wonder if your underlying table design is appropriate? Subforms are often used to represent tables linked in one-to-many relations, with the main form based on the one-side table and the subform the many-side (e.g. a form displaying a purchase order in the main form and the order lines (the items items being purchased) in the subform).

Please tell us a bit more about what you want to do, and the relevant fields of the tables underlying your current form/subform, so we can advise you more suitably how to proceed.

-Stewart

[mrubel99]

Stewart, the main and the subform do have a one to many relationship. The subform is showing line item detail for the project in the main form. The idea was the the user could filter the line items in the subform and exclude them from the project. Through the exclusions the totals for the project on the main form would adjust. Anyway, I was able to use an update loop to accomplish what I needed.

Regards,

Mike