preventing users from changing date field

  • Thread starter Thread starter CEV
  • Start date Start date
C

CEV

We have a Date of Birth field in a few different forms that are all linked
to the same field in a table. What I would like is for users to be able to
enter a date in this field for a new record, but then prevent it from being
changed after that. I am finding that this field in a couple of the forms is
accidentally getting clicked on which makes the calendar popo up and the
user is just clicking any where on the calendar thus changing the date. This
is not hapenning to just one user which is why I am trying to do this. Is
there a way to do this?

Thanks,

CEV
 
In the Current event for the form put code like the following.

Me.txtTheDateControl.Locked = Not Me.NewRecord

Change control names to suit your controls.

Jeanette Cunningham
 
We have a Date of Birth field in a few different forms that are all linked
to the same field in a table. What I would like is for users to be able to
enter a date in this field for a new record, but then prevent it from being
changed after that. I am finding that this field in a couple of the forms is
accidentally getting clicked on which makes the calendar popo up and the
user is just clicking any where on the calendar thus changing the date. This
is not hapenning to just one user which is why I am trying to do this. Is
there a way to do this?

Thanks,

CEV
Click to expand...

You can put code in the control's BeforeUpdate event like:

Private Sub controlname_BeforeUpdate(Cancel as Integer)
If Not Me.NewRecord Then
Cancel = True
End If
End Sub

You may want to use MsgBox to pop up a message and ask if the user really
wants to change the date (they might be legitimately correcting a data entry
error!)

John W. Vinson [MVP]
 
Thanks for the response Jeanette, but did as you suggested and I am still
able to click on the field and the calendar pops up allowing me to change
the date. Here is what it looks like:

Private Sub Form_Current()
On Error Resume Next
Me![ImageFrame].Picture = Me![ImagePath]
Me![Date of Birth].Locked = Not Me!NewRecord
End Sub

Thanks,

CEV
 
That sounds alot better. How do I use the MsgBox or where is there a good
tutorial on it?

Thanks,

CEV
 
That sounds alot better. How do I use the MsgBox or where is there a good
tutorial on it?
Click to expand...

Open the VBA editor (e.g. type ctrl-G), press F1 to open Help, and search for
MsgBox.... the help file can be confusing but the information is all there.

Try

Private Sub controlname_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
If Not Me.NewRecord Then
iAns = MsgBox("Do you really want to change the birthdate?", vbYesNo)
If iAns = vbNo Then
Cancel = True
End If
End If
End Sub

John W. Vinson [MVP]
 
Here is what I have for the Date control:

Private Sub Date_of_Birth_BeforeUpdate(Cancel As Integer)
Dim iAns As Integer
If Not Me.NewRecord Then
iAns = MsgBox("Do you really want to change the birthdate?", vbYesNo)
If iAns = vbNo Then
Cancel = True
End If
End If
End Sub

Private Sub Date_of_Birth_MouseDown(Button As Integer, Shift As Integer, X
As Single, Y As Single)
Set cboOriginator = Date_of_Birth
ocxCalendar.Visible = True
ocxCalendar.SetFocus
If Not IsNull(cboOriginator) Then
ocxCalendar.Value = cboOriginator.Value
Else
ocxCalendar.Value = Date
End If
End Sub

It still only does the same as it was doing before. It opens the calendar
and I am able to select a date and it will put the new date in without ever
asking me if I am sure I want to change it. Can you see that I am doing
anything wrong?

Thanks,

CEV
 
Private Sub Date_of_Birth_BeforeUpdate(Cancel As Integer)
Dim iAns As Integer
If Not Me.NewRecord Then
iAns = MsgBox("Do you really want to change the birthdate?", vbYesNo)
If iAns = vbNo Then
Cancel = True
End If
End If
End Sub

Private Sub Date_of_Birth_MouseDown(Button As Integer, Shift As Integer, X
As Single, Y As Single)
Set cboOriginator = Date_of_Birth
ocxCalendar.Visible = True
ocxCalendar.SetFocus
If Not IsNull(cboOriginator) Then
ocxCalendar.Value = cboOriginator.Value
Else
ocxCalendar.Value = Date
End If
End Sub

It still only does the same as it was doing before. It opens the calendar
and I am able to select a date and it will put the new date in without ever
asking me if I am sure I want to change it. Can you see that I am doing
anything wrong?
Click to expand...

I'm apparently missing the point of what you're doing. What is the Control
Source of cboOriginator? Into what field are you storing the date?

You may need to move the If Not Me.NewRecord block into the MouseDown event so
that the calendar value only gets transferred when you're on the new record.

John W. Vinson [MVP]
 
Sorry, I'm not really up to speed with the code stuff. The date is being
stored in the Date of Birth field. Here's the new code I have:

Private Sub Date_of_Birth_MouseDown(Button As Integer, Shift As Integer, X
As Single, Y As Single)
If Not Me.NewRecord Then
iAns = MsgBox("Do you really want to change the birthdate?", vbYesNo)
If iAns = vbNo Then
Cancel = True
End If
End If

Set cboOriginator = Date_of_Birth
ocxCalendar.Visible = True
ocxCalendar.SetFocus
If Not IsNull(cboOriginator) Then
ocxCalendar.Value = cboOriginator.Value
Else
ocxCalendar.Value = Date
End If
End Sub

This almost works just like I want it. What happens now is when I click on
the form field, the message pops up asking if I really want to change the
date. But no matter if I hit Yes or No, the calendar pops up and I have to
click on a date. The only thing that is missing now is I would like it to
just go back to the form if the user clicks on No.

I really appreciate the help,

CEV
 
Hi Cev

You can try to put
If iAns = vbNo Then
Cancel = True
End Sub
End If

The End Sub will prevent the code continuing if you wish not to change the
birth date
Sorry, I'm not really up to speed with the code stuff. The date is being
stored in the Date of Birth field. Here's the new code I have:

Private Sub Date_of_Birth_MouseDown(Button As Integer, Shift As Integer, X
As Single, Y As Single)
If Not Me.NewRecord Then
iAns = MsgBox("Do you really want to change the birthdate?", vbYesNo)
If iAns = vbNo Then
Cancel = True
End If
End If

Set cboOriginator = Date_of_Birth
ocxCalendar.Visible = True
ocxCalendar.SetFocus
If Not IsNull(cboOriginator) Then
ocxCalendar.Value = cboOriginator.Value
Else
ocxCalendar.Value = Date
End If
End Sub

This almost works just like I want it. What happens now is when I click on
the form field, the message pops up asking if I really want to change the
date. But no matter if I hit Yes or No, the calendar pops up and I have to
click on a date. The only thing that is missing now is I would like it to
just go back to the form if the user clicks on No.

I really appreciate the help,

CEV
[quoted text clipped - 33 lines]
John W. Vinson [MVP]
Click to expand...
Click to expand...
 
Sorry CEV

Not End Sub, Should be Exit Sub
Hi Cev

You can try to put
If iAns = vbNo Then
Cancel = True
End Sub
End If

The End Sub will prevent the code continuing if you wish not to change the
birth date
Sorry, I'm not really up to speed with the code stuff. The date is being
stored in the Date of Birth field. Here's the new code I have:
Click to expand...
[quoted text clipped - 33 lines]
Click to expand...
 
Thanks for replying Ben

I changed it to Exit Sub as you said as shown below:

Private Sub Date_of_Birth_MouseDown(Button As Integer, Shift As Integer, X
As Single, Y As Single)
If Not Me.NewRecord Then
iAns = MsgBox("Do you really want to change the birthdate?", vbYesNo)
If iAns = vbNo Then
Cancel = True
Exit Sub
End If

Set cboOriginator = Date_of_Birth
ocxCalendar.Visible = True
ocxCalendar.SetFocus
If Not IsNull(cboOriginator) Then
ocxCalendar.Value = cboOriginator.Value
Else
ocxCalendar.Value = Date
End If
End Sub

But now what hapenns is when I click the date field I get the error :
"Compile error: Block If without End If" and it opens the VB window. Any
suggestions?

Thanks,

CEV


Benjamins via AccessMonster.com said:
Sorry CEV

Not End Sub, Should be Exit Sub
Hi Cev

You can try to put
If iAns = vbNo Then
Cancel = True
End Sub
End If

The End Sub will prevent the code continuing if you wish not to change the
birth date
Sorry, I'm not really up to speed with the code stuff. The date is being
stored in the Date of Birth field. Here's the new code I have:
Click to expand...
[quoted text clipped - 33 lines]
John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 
Private Sub Date_of_Birth_MouseDown(Button As Integer, Shift As Integer, X
As Single, Y As Single)
If Not Me.NewRecord Then
iAns = MsgBox("Do you really want to change the birthdate?", vbYesNo)
If iAns = vbNo Then
Cancel = True
Exit Sub
End If

Set cboOriginator = Date_of_Birth
ocxCalendar.Visible = True
ocxCalendar.SetFocus
If Not IsNull(cboOriginator) Then
ocxCalendar.Value = cboOriginator.Value
Else
ocxCalendar.Value = Date
End If
End Sub

But now what hapenns is when I click the date field I get the error :
"Compile error: Block If without End If" and it opens the VB window. Any
suggestions?
Click to expand...

Count IF's and End If's. One easy way is to go through the code line by line,
mentally adding 1 when you find an IF, subtracting 1 for each End If. You
should get to 0 at the end.

You need another End If after the first Exit Sub line.
 
Back
Top