require data entry in bound Date control 1

[THWatson]

Using Access 2000

To keep it simple, I am going to try to do this by not using the actual form and subform names.

On a form is a subform. Let's call it fsubA
fsubA has 2 nested subforms, fsubB and fsubC

When the user goes to fsubA, the first field is a Date field. Back in the table, this field is "required."

Providing the user makes an entry in that field everything goes fine. However, I am trying to cover the possibility of his simply clicking into one of the subforms, fsubB or fsubC, without entering a date. Then when he goes to save the record, everything goes away in fsubA and orphaned stuff gets left in the two nested subforms.

For whatever reason, the field being set as "required" in the subform has no effect.

The problem is also that I can't set a default value for that date field, as then if I add a new record in the main form an error occurs because the main form thinks a record is already in fsubA

What I am trying to do is cover all the possible pitfalls here.

Tom

 

[jedel]

Without trying to be too generic. It seems to me that your end state is that whenever someone wants to add a new record, your date field needs to be filled out.

Question
Is the date in the field the day the record is raise? If so, then make sure that there is only one way to add a record, ie a button and in the code, make it enter the date. ie me.field=date

If not, I would suggest creating a popup form with a date field specifically asking for date, prompting the user.

The thought is if you require your user to do something mandatory, try doing automatically and take the option away from the user.

Without more detail, I really can't help you much further

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[THWatson]

jedel
You are right about the end state...the date fields needs to be completed.

You are also right about the date in the field being the date the record is raised...at least, that would be the normal...although the user might enter stuff a day or so later in which case he could edit the date.

I have tried a number of other solutions. The pop-up form with a date field might well be the way to go.

Thanks.

Tom

 

[maxhugen]

Are the records in fsubB and fsubC related to the record in fsubA?

Sounds like you may need to force a save of fsubA, perhaps in the OnActivate events of fsubB and fsubC, so the required field is enforced...

Max Hugen
Australia

 

[Remou]

How about the subform control exit event?

Private Sub fsubA_SubformControl_Exit(Cancel As Integer)
If IsNull(Me.[fsubA_SubformControl].Form.ReqDate) Then
    Cancel = True
    Me.[fsubA_SubformControl].Form.ReqDate.SetFocus
End If
End Sub

 

[THWatson]

Remou
That's perfect! Exactly what I was looking for. That holds the user right there until the Date is filled in.

Tom

 

[THWatson]

Remou
Well...upon further testing...that's only partly perfect.

The code assumes, just by virtue of being in the ReqDate control, that the user wants to enter a new record, and he is held there until he makes an entry.

The fsubA is on a tab page, so the user will always be in that ReqDate control when he clicks on the tab.

So the code is a bit restrictive.

I am trying to figure out a way to use this code only when the user actually wants to make a new entry.

Tom

 

[Remou]

Who decides? If it is the user, a messagebox should suit.

Private Sub fsubA_SubformControl_Exit(Cancel As Integer)
If IsNull(Me.[fsubA_SubformControl].Form.ReqDate) Then
  If MsgBox("No date. Continue?", vbYesNo) = vbNo Then

    Cancel = True
    Me.[fsubA_SubformControl].Form.ReqDate.SetFocus
<...>

 

[THWatson]

Remou
I had already tried that. Trouble is this...

The Main form is on tab page 1. This subform, fsubA, is on tab page 2. So every time you go to a different record on the main form, the message box pops up. So it's not wonderfully user friendly.

I have been working at disabling the 2 nested subforms, fsubB and fsubC, until the ReqDate is filled in...providing that a new record is in fact desired.


Tom

 

[THWatson]

To tie this thread off...

Here's the process that actually works.

On the Before Insert event of fsubB and fsubC:

Private Sub Form_BeforeInsert(Cancel As Integer)
   If IsNull(Me.Parent!ReqDate) Then
       MsgBox "Please enter the required date before entering data here!", vbExclamation
       Cancel = True
       Me.Parent!ReqDate.SetFocus
   End If
End Sub

Tom