Datasheet Refresh

[dbprogman]

Form1
TabCntrl1
Subform1 - Datasheet

FieldA = FieldB (FieldB unbound calculated changes daily)

Problem:

When subform is opened and records dispalyed (filtered by combobox Form1)
only the currently selected record is updated.

How can I get all records to update (= FieldB) when opened.

I hope I have explained things ok...

Thanks

 

[vbaInet]

I don't understand what you're trying to achieve here. Please elaborate.

 

[dbprogman]

Yes, Sorry was a bit to brief.

Ok, I have a form that has a tab control on it with two pages, on each page there is a subform that is displayed in datasheet format. When a user opens this form (Mainform) They choose a property from a combobox and then the datasheet shows those records associated with that property. Great, works well, however...

On the subform there is a field called date allocated which the user enters a date when a job was allocated to that property. Then there is a unbound field that calculates the time in days from the date allocated and the current date. Great works well, however...

On the subform there is a field that is called Time to Complete, this field needs to be the same as the unbound field - Me.Time to Complete = Me.Unbound the problem exists in the fact that the unbound calculative field is changing daily. However when a user opens the form each day the Time to Complete field no longer is the same as the unbound field. All attempts at getting the fields / form to refresh on open or after some events have failed as I can only get the selected record to refresh, I need all displayed records to refresh at once.

I hope I have explained things a little better my query in a nutshell is how can I get a datasheet full of records to refresh based on a calculative field all at once not just a selected one.

I sincerely appreciate all your help...

 

[vbaInet]

There's just one thing I need clarified. If the time to complete is supposed to be the same as the unbound field, do you really need two fields with pretty much the same data?

So your problem is, the subform is in datasheet view and the calculated control is repeating itself across all the records?

 

[dbprogman]

No the calculative field doesnt repeat its self it works as I would expect being different for each record the problem is that as 24hrs pass the calculative field is advanced by 1 great, however now the Time to Complete field is 1 day behind. How can I get all records to refresh on open or after the user has chosen a property etc...

I tried using code behind the Time to Complete field without much success, hence why I chose to use an unbound field instead which calculates perfectly.

Basically once working this will act as a warning system for users for example if Time to complete equals or greater than 14 then field will turn red else be green. The colour coding is simply done with conditional formating but its of no use if the field cant display the correct number of days.. Whew! I am getting confused now and I am writing this masterpiece.

 

[vbaInet]

Have you tried setting its Control Source property to that textbox?

 

[dbprogman]

If I change the control source of Time to Complete to the unbound field then Time to Complete would become an unbound field and the information no longer stored in the table, this is needed for reporting.

 

[vbaInet]

What you want is a button that will set the Time To Complete field to the current Date/Time. If you set the Time to Complete field to the unbound field and still have it bound then the data will change the next 24 hrs and that would be meaningless for your report. Also you shouldn't store values gotten from calculated controls. Have a look at this:

http://allenbrowne.com/casu-14.html

If you still want to go ahead with your idea then you put this in the AFTER UPDATE event of the unbound control:

Me.TimeToCompleteName = Me.UnboundControlName

 

[dbprogman]

Please find attached a sample of what I am attempting...

 

[vbaInet]

I was just reading your post today and I think I understand your requirement.

See attached.