Full Version: Updating Txt Box Based On Dates
UtterAccess Forums > Microsoft® Access > Access Forms
sekonda40
I am trying to get a field (txtExpiration) to display the expiration date of the training that has been selected dependant upon two factors, firstly the date the training was carried out (txtDateAchieved) and the duration of the training (txtDuration)


I have tried:

Private Sub txtDateAchieved_Change()
Me.txtDuration.Value = TempVars!tempDuration
Me.txtExpiration.Value = Me.txtDateAchieved.Value + Me.txtExpiration.Value
End Sub

but nothing happens, other than that txtDuration displays the value of the duration of the training selected.

I also have anther question for once this is sorted, in that some of the training is a once only event so would I be right in thinking with the value of 0 in txtDuration it will set the expiration date equal to the value held in txtDateAchieved, which is not what I want. If so would I have to stick some code in along the lines of "if me.txtDuration.value = "0" then do nothing"?


Many Thanks


Ross Bennett
GroverParkGeorge
First, I do not think you want to use the Change event. It fires every time you enter a new character, delete or change a character in the control. Probably that is not what you are looking for, is it?

Second, how and where are you setting the value of TempVars!tempDuration? Does it even have a value when the Change event fires on the txtDateAchieved control?

Third, are you sure the logic I the last line is what you want? Your code says, "Take whatever value is currently in txtExpiration and add to that the value in txtDateAchieved". You have a date already in txtExpiration, if I read your description correctly. And now you want to add to that date a second date, the one in txtDateAchieved, which is also a date. Is that the right logic here?


theDBguy
Hi Ross,

Does "duration" represent the number of days before the training expires? If so, what do you get if you set the Control Source of txtDuration to this?

=IIf(TempVars!tempDuration=0,Null,DateAdd("d",TempVars!tempDuration,[txtDateAchieved]))

Hope it helps...

Edit: Oops, looks like George was posting as I was replying.
sekonda40
Hi Guys

Thank you for you reply's.

George,

The reason that I was using the change event is because the user will be selecting the date from a calendar so it will only enter once. The tempvar event is coming from the main form (see the attached pic), but due to the relationships I can not get the data to be held in the second form, frmCompetencyEvaluation, so I chose to tempvar and hold the value in an unbound textbox, txtDuration ( I only have it displaying currently so that I can prove what stages are working), as you can see from the picture it is holding the value correctly once the action has occurred.


Sorry my original post was a bit woolly, so txtDateAchieved holds the date achieved and txtDuration holds the amount of days as a number before the expiration of this training. So in essence I want txtDateAchieved + txtDuration = txtExpiration unless txtDuration = 0 then do nothing.

DB,

I have just tried your code in the control source for txtDuration and it errored with a '2448, You can't assign a value to this object'. And yes duration represents the number of days before the training expires.


Many Thanks

Ross

Click to view attachment
theDBguy
Hi Ross,

Are you able to share a copy of your database with dummy data?
sekonda40
Hi Db

Not a problem

Cheers

Ross

Click to view attachment
Jeff B.
Are you working with Date/Time values (i.e., fields in tables with datatype = date/time)?

Are the controls on the forms 'bound' or 'unbound'?

More info, please...
sekonda40
Hi Jeff

Yes, the following fields are date/time values:

Date Achieved
Expiration

Duration is a Number value

The form's are bound to:-

frmAddComp - tblEmployeeCompetency
frmCompetencyEvaluation - tblCompetencyEvaluation

The only item that is unbound is txtDuration which is held on frmCompetencyEvaluation. This item is purely a place holder to ensure that the value of the duration of training to be added to the original date is being pulled through to the form (once the form is working that text box and a lot of other text boxes will be hidden as that info is of no value to the end user).


Cheers


Ross
theDBguy
Hi Ross,

Thanks for uploading a sample. Check out the attached modified version to see if it's what you want.

Hope it helps...
sekonda40
DB

Many thanks for your help, this has solved the mystery and all with one line of code!!! I have made a slight change to the code so that when the value of competency duration is 0 then nothing is added on as these competencies are open ended.


If Me.Parent.txtCompetencyDuration = 0 Then
Else
Me.txtExpiration = DateAdd("d", Me.Parent.txtCompetencyDuration, Me.txtDateAchieved)
End If


Cheers for all your help


Ross
theDBguy
Hi Ross,

You're welcome. We're all happy to assist. Good luck with your project.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.