Updating records based on form data

[Accessosaurusrex]

I am looking how to build the code so that if a form you have open has a value of 0 in three different fields, I can press an update button at the bottom of the form and it will place the current date in another field on each record. To explain better (hopefully), I have three check boxes on a form. Should a record exist that does not meet the conditions of the check boxes, they will leave the boxes alone which are defaulted to 0. Otherwise they will check the box and other functions take place. When the user is through with the check boxes, I would like them to hit an update button which will look at only the records displayed on the form, and if no boxes are checked on that record to apply the current date to another field on that record.

The check boxes are called NS RX and Re and the field I wish to update is ClrDate.

I am open to better ideas than this but my VBA skills are pretty poor.

I am using Access 2007 at this time.
Thank you for any assistance with this.

 

[Beetle]

So which one of the following scenarios is more accurate;

The records that are in your Form's current Recordset are the only ones (out of the entire underlying table) that could meet the criteria of having NS, RX and RE be False (0) and ClrDate be Null (no value). Any other records in the underlying table where NS, RX and RE are False would have already had a value entered in ClrDate (as in you've always done this manually in the past and now you want to automate it).

-OR-

There may be other records in the underlying table where NS, RX and RE are False and ClrDate is Null, but you don't want to update those records, just the ones in your Form's current Recordset.

-OR-

I'm way off base and it's something else entirely.

 

[Accessosaurusrex]

The second scenario is accurate.

"There may be other records in the underlying table where NS, RX and RE are False and ClrDate is Null, but you don't want to update those records, just the ones in your Form's current Recordset."

There are thousands of records that may have the NS, RX and RE as False and ClrDate is Null, but I only care to address the ones on this form retrieved by a query. The only other issue I neglected to mention earlier is that I only want to update the ClrDate field if it is Null to begin with.

So to try and summarize, my form has records based on the query and I only wish to update those records returned by the query. There are many others in the source table that will meet the NS, RX and RE = False criteria. When the user looks at the form and finishes checking boxes that apply, any remaining records where NS, RX and RE = False AND ClrDate is Null, I would like to update ClrDate to now. This can be achieved by the user and a button or if there is an easy way to do that on exit, I am certainly amenable to that or any other better ideas.

Thank you again for your assistance, I sincerely appreciate it.

 

[Beetle]

You could run an Update query (from the Click event of your button) using the same criteria you used to generate the recordset in the first place plus additional criteria to limit the update to only those fields. So if the query for your fom's record set looks like;

Select FieldA, FieldB From tblYourTable Where FieldB = 'X'

Then you Update query would look like;

Update tblYourTable Set ClrDate = Date Where FieldB = 'X' And NS = False And RX = False And RE = False And ClrDate Is Null

So the code for your button might look like;

Private Sub cmdYourButton_Click()

Dim strUpdate As String

strUpdate = "Update tblYourTable Set ClrDate = " & Date & _
            " Where FieldB = 'X' And NS = False And RX = False" & _
            " And RE = False And ClrDate Is Null;"

CurrentDb.Execute strUpdate, dbFailOnError

End Sub

Alternatively, you could loop through your form's RecordsetClone and update each field as you go. Example code;

Private Sub cmdYourButton_Click()

With Me.RecordsetClone
    .MoveFirst
    Do While Not .EOF
        If !NS = False And !RX = False And !RE = False And IsNull(!ClrDate) Then
            .Edit
            .ClrDate = Date
            .Update
        End If
    .MoveNext
    Loop
End With
    
Me.Requery

End Sub

The first option would be more efficient but if we're only talking about a few records you wouldn't really notice a difference in speed. In general though, an Update query is going to be faster than looping through a record set.

 

[Accessosaurusrex]

My apologies for the length of time in replying, however it worked beautifully and thanks for your time. I thought I had posted earlier but was cleaning out my e-mail and noticed that I had not. Thanks again!

 

[Beetle]

No problem. Happy to help.