VBA button to update every record in a table (1 Viewer)

Locopete99 · Dec 13, 2017

Hi Guys,

I have a table that has a field in it which is a "months remaining" field.

This is calcuated by doing a date diff between today and the end date field on the record.

I wont paste the whole code, as i'm sure you know how to do it.

What I want to be able to do though is have a refresh button that calculates this date for all records in a table.

Code:

Set rsSubba11 = CurrentDb.OpenRecordset("Tbl_ShikomiRegister", dbOpenDynaset)

With rsSubba11
 .FindFirst "[Blanket Agreement Number] =" & "'" & [Blanket Agreement Number] & "'"
     .Edit
    ![Months to run out] = ddif
   .Update
End With
DoCmd.RefreshRecord

So using my record set to update the field, how do I get this to run for all records?

Minty · Dec 13, 2017

I wouldn't bother storing this, as you have discovered, you have to try and update it all the time to keep it accurate. Simply calculate on the fly in a query whenever you need it.

arnelgp · Dec 13, 2017

if you are unable to do
what minty has suggested.
you can loop through the recordset:

Code:

Set rsSubba11 = CurrentDb.OpenRecordset("Tbl_ShikomiRegister", dbOpenDynaset)
With rsSubba11
    .FindFirst "[Blanket Agreement Number] =" & "'" & [Blanket Agreement Number] & "'"
    While Not .NoMatch
        .Edit
        ![Months to run out] = ddif
        .Update
        
        .FindNext "[Blanket Agreement Number] =" & "'" & [Blanket Agreement Number] & "'"
    Wend
    .Close
End With
Set rsSubba11 =  Nothing
DoCmd.RefreshRecord

Locopete99 · Dec 13, 2017

Thanks Arnel

arnelgp · Dec 13, 2017

or you can use SQL statement to update all record:

Code:

Dim strUpdateSQL As String
strUpdateSQL = "Update Tbl_ShikomiRegister Set [Month to run out]=" & ddif & " Where Tbl_ShikomiRegister.[Blanket Agreement Number]=" & Chr(34) & [Blanket Agreement Number] & Chr(34) & ";"

Currentdb.Execute strUpdateSQL, dbSeeChanges

Minty · Dec 13, 2017

To maybe clarify what I said - it's a bit like storing someones age, instead of the date of birth.

It's a completely dynamic value. It's value is determined by when you look at it. So by definition storing it is always out of date / inaccurate, unless you have just recalculated it.
You also have the problem that you have to make sure you always redo your update whenever a new value is entered or an update is made anywhere in your system to the underlying values.

Hence why you shouldn't store it, you should always calculate it.

Locopete99 · Dec 13, 2017

Hi Minty,

I understand completely what your saying.

However I have found that I need to save this information. Currently I have it so that whenever the record is looked at individually on the form, this field is automatically updated on load.

The problem is I now need to find all instances when we are 6 months from the end date.

Originally the database would have been viewed a record at a time and updated by external sales where needed.

New functionality that I have been requested to put in means that I now need every record to be up to date.

This would mean that I either now have to add a date calc to each of my queries that picks up information based on this or try to do a mass update of all records once the database is opened (possibly adding an update to the on load function of a login screen).

Minty · Dec 13, 2017

You can easily find that by querying the end date, no need to store how far away it is at all.

In a query simply put in the EndDate criteria < DateAdd("m",6,Date())

Normally if you are storing something like this there is a better way. You could even have your form have a automated "Expiry Date" set to 6 months and allow end user to set a different date and query it based on that. No need to hard code it let your user decide.

Locopete99 · Dec 13, 2017

Hi Minty,

I'm in the process of swapping over to calculated fields.

I'm going half and half.

I'm using your DateAdd as suggested, but for clarity I also want to add a field that works out the Date Difference when run.

This will update every time the query is run (every time the form is opened as I'm using it to populate a subform)

Could you just remind me on how to add this in as an expression on the end of a query?

I've put

Code:

Months Remaining: DateDiff("m","Date",[Tbl_ShikomiRegister]![End Date])

I aim to have this show as well, so it will have agreement number, part, and Months remaining with the Date Add driving the selection of <X months on the query but not showing.

Minty · Dec 13, 2017

In the forms underlying query ;

Months Remaining: DateDiff("m",Date(), [End Date])

Assuming [End Date] is the only field called that - it should work.
I would get rid of the spaces in your field names - makes it much harder to type and fault find.

VBA button to update every record in a table (1 Viewer)

Locopete99

Registered User.

Minty

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

Locopete99

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Minty

AWF VIP

Locopete99

Registered User.

Minty

AWF VIP

Locopete99

Registered User.

Minty

AWF VIP

Similar threads

Users who are viewing this thread