How To Auto-complete a field with a date?

[greeni91]

Hi All,

I am currently upgrading a problem report I did a little while back and my boss asked if I could:

• Auto Complete "Completion Date" field with today's date
• Grey out a select amount of fields for an ECR
• Lock feedback field when form has been signed off

I have been able to complete the second bulletpoint from the list but I am having trouble writing code for both other bullet points.

Problem 1: Auto Complete "Completion Date" field

I have three 3 fields at the bottom of my form which are linked to a lookup field. These fields return names to the boxes.

What I am trying to do is set up an if statement that will say:

If Me.QCSignOff.Value > 0 Then
   If Me.ManuSignOff.Value > 0 Then
      If Me.EngSignOff.Value > 0 Then
      Me.CompletionDate.Value = (Today's Date)
      Else
      Me.CompletionDate.Value = ""
      End If
   End If
End If

Problem 3: Lock Feedback field when form has been Signed Off

This problem is directly linked with the problem above, in the fact that I want the Feedback field to disable when QCSignOff, ManuSignOff and EngSignOff have a value entered into their respective fields.

I left this out of the first problem because I want to deal with one of them at a time.


Thanks in advance for the Help...
(Sorry for messiness)

/Sandy

[Delerna]

Your If statement appears to say the correct thing. You didn't say what the problem was.

Your code sample says (Today's Date)
Does this mean you don't know the fuction to get it?

=Now() will set it to todays date and time
=Date() will set it to todays date


As to locking the feedback control

me.controlname.enabled=false
 

will do that

[greeni91]

I am trying to get today's date to automatically fill in the CompletionDate field on my form. The fields QCSignOff, ManuSignOff and EngSignOff are lookup fields in a table that returns names of my bosses when filled in on the form, so I am not sure if the .Value > 0 statements are correct or not.

I have tried tweaking my code abit and nothing seems to work. It doesn't even disable the Feedback field which makes me think that the .Value > 0 statement isn't correct.

I think I may be missing an important piece of code from the code I have written but I can't put my finger on what it is. I thought to start with it was maybe defining a variable at the start of the code, but alas that didn't work either...

When I put the Date() function into my code as Me.CompletionDa te.Value = Date(), VBA automatically drops the parenthesis at the end of Date.

I don't know whether it would be better if I wrote this code in Expression Builder...

Any more help is greatly appreciated.

/Sandy

[missinglinq]

disable when QCSignOff, ManuSignOff and EngSignOff have a value entered into their respective fields.

Your code such as

If Me.QCSignOff.Va lue > 0 Then

should be

If Not IsNull(Me.QCSig nOff) Then

and the same for your other If statements.

Linq ;0)>

[greeni91]

I have re-written my code as MissingLinq told me to do but my feedback field will not disable and no date will fill the CompletionDate field. I don't know how to write the code to have the Date() incorporated into it. Every time I try to write it with the parenthesis, VBA drops them at the end of Date.

I don't know whether I have the code in the right procedure, I have written it in the after update for the completion date and I took a dump of the code and pasted it to the form_current event.

My code as current is this, can anybody see something wrong in the code?:

Private Sub CompletionDate_AfterUpdate()
If Not IsNull(Me.QCSignOff) Then
    If Not IsNull(Me.ManuSignOff) Then
        If Not IsNull(Me.EngSignOff) Then
        Me.Feedback.Enabled = False
        Me.CompletionDate.Value = Date
        Else
        Me.Feedback.Enabled = True
        Me.CompletionDate.Value = " "
        End If
    Else
    End If
Else
End If
End Sub

/Sandy

[greeni91]

I played around a little bit with my code this morning and made a few changes to the code provided above.

The problem with the last code was that it was locking the Feedback field when information was in the QCSignOff, ManuSignOff and EngSignOff boxes. The code would not put the current date into the CompletionDate field.

I have proven out this code myself and it seems to work ok. It only does it on the record you are on and once everything has been removed from the boxes mentioned above the date disappears from the CompletionDate field and the Feedback field is unlocked/enabled, it also saves all the information, i.e. You can leave the database and the information remains.

My new code is as follows:

Private Sub CompletionDate_Enter()
Dim MyDate
MyDate = Date
If Not IsNull(Me.QCSignOff) Then
    If Not IsNull(Me.EngSignOff) Then
        If Not IsNull(Me.ManuSignOff) Then
        Me.CompletionDate.Value = MyDate
        Me.Feedback.Enabled = False
        Else
        Me.CompletionDate.Value = Null
        Me.Feedback.Enabled = True
        End If
    Else
    Me.CompletionDate.Value = Null
    Me.Feedback.Enabled = True
    End If
Else
Me.CompletionDate.Value = Null
Me.Feedback.Enabled = True
End If
End Sub

The way this is set-up is that when all the information has been set in the three boxes, you will need to click on or tab to the CompletionDate box for the code to activate.

I am not entirely sure if you click on the box on another occasion when all the information is complete whether it will add the new date to the CompletionDate field... I will check it out and post back.

/Sandy