Results 1 to 10 of 10
  1. #1
    Josha is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2019
    Location
    Victoria, Australia
    Posts
    42

    VBA button to update all fields within a column to a simple YES

    SO I have a database that my students use to type their biology examination notes. And then they generate a report for it and export it to PDF for me to mark.



    They need to tell the database that a report has been generated for each item by changing a field to a YES.

    But my students are requesting a button to update all items to be a yes instead of individually changing them to a yes. See figure below..

    Click image for larger version. 

Name:	fig 1 yes.png 
Views:	33 
Size:	43.3 KB 
ID:	48478

    So I tried this code

    Code:
    Set rs = Me.fsub_Items.Form.RecordsetClone
    
    rs.MoveFirst
    
    
    Do While Not rs.EOF
    
    
        Me!fsub_Items.Form.Itemreportgenerated = "YES"
    
    
    rs.MoveNext
    
    
    Loop
    
    
    Me.Refresh
    ...... but it only updates the first one in the column

    Any help would be appreciated!

    I have attached a stripped down version of the database (for privacy reasons for my students)
    Attached Files Attached Files

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Here's a quickie. It updates the table directly.

    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub cmd_updateYES_Click()
        
        
        Set rs = Me.fsub_Items.Form.RecordsetClone
        
        rs.MoveLast: rs.MoveFirst
        
        Do While Not rs.EOF
            rs.Edit
            rs!itemreportGenerated = "Yes"
            rs.Update
            'Me!fsub_Items.Form.Itemreportgenerated = "YES"
        
            rs.MoveNext
        
        Loop
        
        Me.Refresh
        Set rs = Nothing
    End Sub

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    docmd.openquery "quUpd1Field"

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I vote for the sql/query option as well. I don't see anything in that code that distinguishes between one user and another, so everyone's value becomes Yes?
    Dunno, maybe I need another ?

    EDIT - took a look at the attachment. Surely there isn't a db for every student? Respectfully, I think you're short on tables and haven't really designed with the idea of one table representing one entity. Rather it seems that table fields contain a mix of attributes, some of which don't really belong there. If all you want is a solution to your posted question then I guess you have that.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by Josha View Post
    SO I have a database that my students use to type their biology examination notes. And then they generate a report for it and export it to PDF for me to mark.

    They need to tell the database that a report has been generated for each item by changing a field to a YES.

    But my students are requesting a button to update all items to be a yes instead of individually changing them to a yes. See figure below..

    Click image for larger version. 

Name:	fig 1 yes.png 
Views:	33 
Size:	43.3 KB 
ID:	48478

    So I tried this code

    Code:
    Set rs = Me.fsub_Items.Form.RecordsetClone
    
    rs.MoveFirst
    
    
    Do While Not rs.EOF
    
    
        Me!fsub_Items.Form.Itemreportgenerated = "YES"
    
    
    rs.MoveNext
    
    
    Loop
    
    
    Me.Refresh
    ...... but it only updates the first one in the column

    Any help would be appreciated!

    I have attached a stripped down version of the database (for privacy reasons for my students)
    You are doing what another member here does all the time

    You are processing a recordset, yet using the form control, which stays the same no matter what record of the recordset you are on.
    You need to update the actual recordset record, which I would have thought would still need an .Edit and .Update ?
    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

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Please don't use a Row By Agonizing Row method, but use a query as Ranman suggested.

  7. #7
    Josha is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2019
    Location
    Victoria, Australia
    Posts
    42
    Thank you for all the comments.... I realise I have been going about this ALL WRONG...


    I should have been using an update query - derr ...


    So i have made an update query that looks like this.....

    Click image for larger version. 

Name:	update1.png 
Views:	12 
Size:	19.9 KB 
ID:	48492

    And then on the main form I made a simple button with this code

    Code:
    Private Sub Command16_Click()
    
    DoCmd.OpenQuery "updatequery1"
    
    
    Me.Refresh
    
    
    
    
    End Sub
    So this button runs the update query, but before it does that it requires the user to tell the query what ExamID do you want to filter by.....

    So the user must look at the form's current ExamID...

    Click image for larger version. 

Name:	update2.png 
Views:	12 
Size:	22.6 KB 
ID:	48493

    This is to make sure the update query only updates "No" to "YES" for the current ExamID (primary key) - because I do not want EVERY "no" updated to a "yes"....

    How can I make it so that the user doesn't have to manually type the ExamID number... How can I make it so that the update query looks at the current forms ExamID and uses that automatically???

    THanks!

  8. #8
    davegri's Avatar
    davegri is online now Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You're original plan was not going about it "all wrong". It's a matter of opinion how you solve the problem, considering factors such as record volume, coding experience and just personal preference.

    Here's your original DB with some modifications to make it work. I added a combobox to select the desired exam number.
    There's no table for students, so I assume each student has his/her own copy of the DB?

    JoshaExam-davegri-v01.zip

    Click image for larger version. 

Name:	choose.png 
Views:	12 
Size:	23.8 KB 
ID:	48495
    Last edited by davegri; 08-10-2022 at 11:18 PM.

  9. #9
    Josha is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2019
    Location
    Victoria, Australia
    Posts
    42
    Thanks davegri! You are totally right about it being a matter of opinion! I'm still surprised by the amount of talented people here and that there are so many solutions to the one problem!

    Your solution worked! Thanks! And I have learnt something new!



    Still curious how to make the update query automatically pull the forms ExamID number, but nevertheless its working now!

  10. #10
    davegri's Avatar
    davegri is online now Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Glad I could help, and thanks for the star!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 03-15-2018, 08:59 AM
  2. Replies: 9
    Last Post: 03-31-2015, 04:13 PM
  3. Update a database column when mandatory fields are completed
    By thebionicredneck2003 in forum Forms
    Replies: 7
    Last Post: 05-12-2013, 01:17 PM
  4. Replies: 3
    Last Post: 09-13-2012, 11:14 AM
  5. Replies: 1
    Last Post: 06-15-2012, 10:47 AM

Tags for this Thread

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