Change value of a text box

[JackD]

Hi,

I have a form where the user puts dates into 2 text boxes. What I want to happen is to have a third text box have it's value be set to 'Yes' if the first 2 text boxes has a date in them. If either of the first 2 boxes is blank, I want the third box to have it's value changed to 'No'

There is no additional criteria to consider pertaining to the date range or anything like that, just the proviso that both text boxes are populated at the same time to make the third boxs' value 'Yes', else it should be 'No' (Both these text boxes are populated by the user selecting a date from a pop up calendar)

I have tried the following (this is attached to both the first 2 text boxes gofocus events, I have tried this on the after update even too with no luck)

Private Sub RTWI_PR_GotFocus()
If Len([RTWI_PR] & [selfcertrecd] & "") <> 0 Then
Me.PaperWorkReturned = "Yes"
Else
Me.PaperWorkReturned = "No"
End If
End Sub

This almost works but the status of the Yes No textbox changes if i enter or delete a value in either of the first 2 date boxes.

Anyone kick me into the direction of a solution or offer a more sensible way to do this..Xmas rum and cokes are great but not so good for the senses...that's my excuse and Ill stick to that for the foreseeable future...at least into retirement in 30 years time!

Thanks

 

[Uncle Gizmo]

My guess is that you want the calculation to take place after you have added a date with the calendar form.

The events of the controls on the form, (the text boxes) are not triggered by the update from the calendar form, this is normal, and I have developed a calendar form which solves this problem which you can find out more about in the following link. In particular have a look at video number four, as this is particularly relevant to your problem.

A free Calendar Form, simple to use for beginners and has advanced features for Power Users.

 

[ListO]

Hey

I think you've got the logic of your If statement backwards. It looks to me that if either [RTWI_PR] OR [selfcertrecd] has an entry then it will change PaperWorkReturned to "Yes".

I'd suggest the following instead.

Private Sub RTWI_PR_GotFocus()
If Len([RTWI_PR] & "") > 0 AND Len([selfcertrecd] & "") > 0 Then
  Me.PaperWorkReturned = "Yes"
Else
  Me.PaperWorkReturned = "No"
End If
End Sub

 

[boblarson]

And I'm going to make a statement that you will probably not like, but it is most likely the way it should be - you shouldn't be storing that information anyway. You can get that information from a query at any time and it does not need to be stored. It is just you probably don't have a clue as to how to use a query to get to what you want.

In the QBE grid (where you build a query) you can create a field that does the same thing here for reporting.

MyNewField:IIf(IsNull([DateField1]) OR IsNull([DateField2]),"No","Yes")

 

[JackD]

Thank you for the assistance Listo, it worked a treat.

Bob, I agree 100% that a query should be used for applying criteria to harvest corresponding data from tables for reports or otherwise. However this is just a cosmetic change I wanted for the user front end form, a visual indicator that they had completed an action in their administrative duty. The resulting Yes No text box is only for use on the form at that time.

I am appreciative of the time and knowledge expended by anyone I come into contact with, it's all part of my life's learning and I do try to take onboard all I can and then hopefully apply it in a meaningful way when an opportunity in the future arises.

Uncle Gizmo, sadly our internet wardens at work have a tight leash on the filters here and I could not view your website. I will certainly make my way there from home and do some reading up from your link.

Thanks again everyone

 

[Uncle Gizmo]

>>>work have a tight leash on the filters<<<

yes, I have noticed this problem before, I have thought of two solutions,

1) Copy the website information onto a CD and sell it.
2) Do a text and picture version of the information and post it in forums like this one.

Your observations comments criticisms would be welcomed.

 

[boblarson]

Bob, I agree 100% that a query should be used for applying criteria to harvest corresponding data from tables for reports or otherwise. However this is just a cosmetic change I wanted for the user front end form, a visual indicator that they had completed an action in their administrative duty. The resulting Yes No text box is only for use on the form at that time.

My expression could be used as a control source and therefore no code is necessary at all. Just put this in the unbound text box's control source and then you don't need any code:

=IIf(IsNull([DateField1]) OR IsNull([DateField2]),"No","Yes")

 

[datAdrenaline]

Slight alternative for the expression presented by Bob. Use a principle called the Law of Propogating Nulls

You can change the expression in the control source to:

=IIf(IsNull([DateField1] + [DateField2]),"No","Yes")

... Or ... you can change the Format property of the text box to "Yes/No" and then just have the Control Source expression as ...

=IsNull([DateField1] + [DateField2])

....

Note ... the expression proposed by Bob is completely legit. I am providing the alternative just to add info ... not override .. ...

 

[boblarson]

Note ... the expression proposed by Bob is completely legit. I am providing the alternative just to add info ... not override .. ...

The benefits to the forum experience. We ALL get to learn new things as we go along. Thanks Brent for the additional ways to do it.