AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
Question -------------------------
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.
