Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    PSSMargaret is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    55

    Update Records in Filtered Form

    What is the best way to update a field for records in a subform when the form is filtered? I only want to update a field titled "EmailSelect" to "Yes" of the filtered records. I have a command button and have an update query ready, just don't know how to write the criteria part. I also tried the below code which works perfect without the WHERE portion but when I add the WHERE portion it only prompts me to enter a parameter value. Any guidance is appreciated.



    Code:
    DoCmd.RunSQL "UPDATE tblContacts SET EmailSelect ='Yes' WHERE Me.frmFilterSfrm.Form.Filter"

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Try
    Code:
    strSQL = "UPDATE tblContacts SET EmailSelect ='Yes' WHERE " &  Me.frmFilterSfrm.Form.Filter
    DoCmd.RunSQL strSQL
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    PSSMargaret is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    55
    Welshgasman, Thank you for your suggestion. I tried your code and still get prompted to enter a parameter value. It's prompting on the field I filtered the records by.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	12 
Size:	2.5 KB 
ID:	43933

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How about posting a copy of the database showing the issue, and instructions for getting to the problem area?

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Something has to be =,>,<, Like, etc to whatever the criteria is. You have no such comparison with either example. However, I don't think that's the reason for the prompt, which often means the table/query or field reference is mis-spelled or either is missing.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by PSSMargaret View Post
    Welshgasman, Thank you for your suggestion. I tried your code and still get prompted to enter a parameter value. It's prompting on the field I filtered the records by.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	12 
Size:	2.5 KB 
ID:	43933
    I don't think you can use your filter like that, you would only use the field names.
    You SQL is not going to have any knowledge of that query unless you include it.?

    That is my thoughts on it?

    Edit: You could try updating that query and not the table.?
    Last edited by Welshgasman; 01-18-2021 at 12:19 PM. Reason: Added update query option
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    PSSMargaret is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    55
    Micron,
    I don't think it's a misspelling. If I change and filter the form by a different field, then it prompts for that field.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by PSSMargaret View Post
    Micron,
    I don't think it's a misspelling. If I change and filter the form by a different field, then it prompts for that field.
    Not disputing what Welshgasman is saying, just your statement. If the query or table name was misspelled it would not matter which field you used. Further, if a query involves another query (a stacked query) and the other query has the same problem, you'd still get the message.

    Posting a zipped db copy is probably your best and fasted path to a solution.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    PSSMargaret is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    55
    Database attached. On frmFilter Users will filter data by different columns. Then when they click "Select" I need "Yes" added to the "EmailSelect" field in tblContacts . Temp Database.zip

  11. #11
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Those fields are NOT in your table?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  12. #12
    PSSMargaret is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    55
    EmailSelect is in tblContacts. ContactTypeID (ID) is in tblContacts and ContactType (Value) is in qryContactDetails. Are you saying that the query and the table have to match exactly?

  13. #13
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    ContactTypeID is in tblContacts, but you are using ContactType ?
    I just used the second field in the form as a test?

    You will need to use whatever links qryContactDetails to tblContacts?

    That is why you get the prompt all the time, as Access does not know of any of those fields.?

    I even tried just getting the fileldame and had the same prompt?

    Code:
    Private Sub cmdSelect_Click()
    
    '    DoCmd.RunSQL "UPDATE tblContacts SET EmailSelect = 'Yes' WHERE Me.frmFilterSfrm.Form.Filter"
    
        Dim strsql As String, strFilter As String
        Dim iDot As Integer
        strFilter = Me.frmFilterSfrm.Form.Filter
        strFilter = Replace(strFilter, """", "'")
        iDot = InStr(1, strFilter, ".")
        strFilter = Mid(strFilter, iDot + 1)
        strFilter = Replace(strFilter, ")", "")  
    
        strsql = "UPDATE tblContacts SET EmailSelect ='Yes' WHERE " & strFilter
        DoCmd.RunSQL strsql
    
        Me.frmFilterSfrm.Requery
    
    End Sub
    strFilter was [ContactType]='Agency'

    You would need to use the ID for Agency in that control, the same with any other similar type controls?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  14. #14
    PSSMargaret is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    55
    Are you saying list the IDs in frmFilterSfrm in lieu of the value. The IDs wouldn't make sense to the users. Maybe I'm misunderstanding you.

  15. #15
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    What I am saying is that you must compare like with like.?
    You use the ContactType combo to show the description, but you store the ID, which is good.

    So, to update tblContacts that match that ContactType you would need to use the relevant ContactTypeID for that ContactType.?

    I think part of the problem is that you are using Access manual methods to filter, and that is using the descriptive field.
    If you were doing it via VBA you would normally use the ID which would be the field bound to the combo. Also then the filter would just be as I trimmed it down above. No mention would be made of the domain for the fields.

    So you need to work out when you filter by ContactType what real/actual field do you need to use as criteria.

    That is my view on it. ?

    Regardless, the users are not going to know any of this is going on.?


    I was once showing someone one of my DBs in my last place of work on how to use it when I was on holiday.
    They remarked 'Well that seems nice and simple'
    I replied 'You do not know how much hard work went in to this, to make it that way'

    Any clearer?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 28
    Last Post: 01-29-2018, 06:40 PM
  2. Filtered Records Wont Stay Filtered
    By ortizimo in forum Access
    Replies: 4
    Last Post: 11-29-2017, 07:08 PM
  3. Replies: 13
    Last Post: 04-16-2014, 10:11 AM
  4. update query fon a filtered form HELP!
    By campanellisj in forum Queries
    Replies: 0
    Last Post: 11-12-2010, 09:08 AM
  5. Open form to filtered records
    By ducecoop in forum Access
    Replies: 3
    Last Post: 10-22-2010, 10:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums