Required field / error with null value / can't move focus

[kiwipedia]

Hi all

I have a combo-box control [Insertion] which I've set as a required field. It's a date and uses a calendar as coded below. All works fine unless the user deletes a pre-existing value (leaving a 0-length string I guess) and then clicks in the Insertion control.

This results in Runtime error '2110' with the message that "can't move the focus to the control Calendar0"

I think this is because I've made Insertion a required field - certainly the problem is "resolved" if I make Insertion not required.

But I do want it to be a required field...any ideas? I did try loading a default value (today's date) into the field after MouseDown if the value was null or zero length string but that didn't work and anyway I don't like the idea of deliberately putting a false value into the field.
Access 2003, Windows XP

Private Sub Insertion_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Calendar0.Visible = True
Calendar0.SetFocus
If Not IsNull(Insertion) Then
    Calendar0.Value = Insertion.Value
Else
    Calendar0.Value = Date
 
End If
End Sub
Private Sub Calendar0_Click()
If Calendar0.Value > Date Then
MsgBox "Insertion Date cannot be in the future."
            Calendar0.SetFocus
            Calendar0.Value = Date
ElseIf Calendar0.Value < Me.Parent.DOB Then
    Call MsgBox("Insertion Date cannot Precede Date of Birth", , "NICU Line Infection Database")
            Calendar0.SetFocus
            Calendar0.Value = Me.Parent.DOB
  Else
  Insertion.Value = Calendar0.Value
  Insertion.SetFocus
Calendar0.Visible = False
End If
End Sub
 

Thanks - Michael

[MMcCarthy]

Hi Michael,

Don't set the field to required, just make it so they can't save the record unless the date if entered.

Mary

[kiwipedia]

Hi Michael,

Don't set the field to required, just make it so they can't save the record unless the date if entered.

Mary

You are messing with my mind!

Michael.

[MMcCarthy]

You are messing with my mind!

Michael.

... unless the date is entered. sorry.

Just check for the date in the save/close event or maybe in the current event whichever is more suitable. In other words if they try to add a record without entering a date you can popup a message box saying please enter date to proceed.

[maxamis4]

I don't see why you don't set the code in the after update event of the combo box. What this means is that after the combo box is updated, it will return the value you want.


I can't follow your code because you don't use typical programming conventions. I don't mean to be rude just trying to help you out. Could repost the variable with labels so I can assist you with this.

example:

variable1 = my combo box
variable2 = my text box
variable3 = my button

thanks

[kiwipedia]

... unless the date is entered. sorry.

Just check for the date in the save/close event or maybe in the current event whichever is more suitable. In other words if they try to add a record without entering a date you can popup a message box saying please enter date to proceed.

Thanks - I tried that idea using a few different events - in the end the unload event of the Parent form seemed to be best:

Private Sub Form_Unload(Cancel As Integer)
    If IsNull(Forms!frmentry.subfrmlines![Insertion].Value) Then
    MsgBox ("Please Enter the Line Insertion Date")
    Cancel = True
    Me!subfrmlines.SetFocus
Me!subfrmlines.Form!Calendar0.Visible = True
Me!subfrmlines.Form!Calendar0.SetFocus
Me!subfrmlines.Form!Calendar0.Value = Date
    End If
End Sub
 

Cheers - Michael.

[MMcCarthy]

Glad you got it working Michael.

[jordimarsa]

First of all, I prefer always using before or after update events in order to tirgger them always. The mouse events can be avoided by using the keyboard instead.

Secondly, I think that there is a missunderstandi ng on how to change values in a form. You do not really need to set focus on another control you can just change the value property.

Finally, about the error, you can do several things taking into account that you are using a required field. If the field is required and it has been already filled you cannot change it by a null value. The only way to have a null value in a required field is before inserting a record and before attempting to change it.

A good way to avoid the error is by using the BeforeUpdate event and if the value is null use the method "Me.[Insertion].Undo" to retake the last value introduced. But a required field means exactly that you cannot save the record without a value. It's in your hand which value you prefer to leave if the user decide to do something forbiden. In datasheet mode I tend to show the error and set the focus to the field that has been missed, but you can undo the change or even set another value more suitable like the current date. If you want to do something particular, please explain me how is your form and what exactly you expect it to behave for the user and I will try to help you.