By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,914 Members | 1,100 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,914 IT Pros & Developers. It's quick & easy.

Subform Filter / Update Issues

P: 9
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command196_Click()
  2. Dim strSql As String
  3.  
  4. strSql = "UPDATE tblUtilizationDataAll SET EXCLUDE_LINE = -1 "
  5. strSql = strSql & "WHERE PROJECT_NAME = '" & Me.Text81 & "' And " & Me.tblUtilizationDataAll_subform.Form.Filter & ";"
  6. CurrentDb.Execute strSql
  7.  
  8. Me.tblUtilizationDataAll_subform.Requery
  9. Me.Requery
  10.  
  11. End Sub
  12.  
Thanks - Mike
Mar 5 '08 #1
Share this Question
Share on Google+
4 Replies


Expert Mod 2.5K+
P: 2,545
...
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command196_Click()
  2. Dim strSql As String
  3.  
  4. strSql = "UPDATE tblUtilizationDataAll SET EXCLUDE_LINE = -1 "
  5. strSql = strSql & "WHERE PROJECT_NAME = '" & Me.Text81 & "' And " & Me.tblUtilizationDataAll_subform.Form.Filter & ";"
  6. CurrentDb.Execute strSql
  7.  
  8. Me.tblUtilizationDataAll_subform.Requery
  9. Me.Requery
  10.  
  11. End Sub
  12.  
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:
Expand|Select|Wrap|Line Numbers
  1. strSql = strSql & "WHERE PROJECT_NAME = '" & Me.Text81 & "' And '" & Me.tblUtilizationDataAll_subform.Form.Filter & "';"
-Stewart
Mar 6 '08 #2

P: 9
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?
Mar 10 '08 #3

Expert Mod 2.5K+
P: 2,545
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
Mar 10 '08 #4

P: 9
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
Mar 25 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.