Expert: Scottgem - 2/25/2005

Question
-------------------------
Followup To
Question - Scott,
Everything is set up but I am getting run-time errors. The message, Run-time error '2110'; Microsoft Access can't move the focus to the control txtExplanation, happens when the time elapsed between the two date fields exceeds 50 days. When I click on the End button it advances correctly but when I hit the Debug button it takes me to the line in my epxression: Me!txtExplanation.SetFocus.
The message, Run-time error '2465': Microsoft Access can't find the field "txtNextControl" referred to in your expression, happens when the time elapsed between the two date fields does NOT exceed 50 days. I changed "txtNext Control" to the actual name of the next control that I want to advance to but it still errors out. Any suggestions on what I should do next? I feel like I am so close!
-------------------------
Followup To
Question -
Can I set up a calculated field on a form? And if so, how would I do that. Here's what I'm trying to do. I have two date fields I want to compare. If the number of days that has elapsed between the two date fields is greater than 50 then I want the user to enter an explanation in a text field bound to the table that the form is bound to.
Thanks.
Answer -
Sure, this is very easy. You can enter an expression into an unbound control. But the way I would work this is as follows:

1) Set up your tab order so the first date then the second date is entered.

2) Set the tab order so the next field after the second date is an unbound control (named txtDateDiff) with the following expression:

=DateDiff("d",[date1],[date2])  (use the correct control names)

3) Now here is where we get a little tricky. In the Got Focus event of that unbound control use the following code:

Me!txtDateDiff.Requery
If Me!txtDateDiff > 50 Then
Me!txtExplanation.Visible = True
Me!txtExplanation.Setfocus
Else
Me!txtExplanation.Visible = False
Me!txtNextControl.SetFocus
End If

4) In the Lost Focus event of you textbox, use the following code:
If IsNull(Me!txtExplanation) Then
MsgBox "Elapsed time is over 50 days! You must enter an explanation", vbOkOnly
Me!txtExplanation.SetFocus
End If

The result of all this, is to calculate then test the difference. If over the threshold, the explanation control becomes visible and the user is forced to enter something in there.

HTH
Scott<>
Thank you so much for your help. I think I must be missing a step or not executing this properly. I created the unbound control and followed all the steps although this was a first for me writing the code for the Focus events. At any rate, I clicked on [Event Procedure] and typed in the code as you said for Got Focus and Lost Focus, however, it is just calculating the number of days difference between the two date fields in that unbound control. Am I supposed to put in another unbound control so that the user has a place to enter their explanation? Guess I'm not understanding something here... sorry :( Help.
Answer -
Yep, I assumed you already had such a control. I referred to it in the GotFocus event. That's the txtExplanation control. That control should initially have its Visible property set to No. The code will detect if the difference requires an explanation. If it does, the control will be made visible and the given focus. If you textbox is not name txtExplanation, either rename or add one.

HTH
Scott<>

Answer
Yes, NextControl has to be the name of the control you want to go to if the difference is less than 50 days.

You have 2 lines that Set the Focus. I think the problem  is in the last line of the code in step 4. That's what the Debug should show. If it is then eliminate that line.

Scott<>