Solved Ensure Data Entry

[Emma35]

Hi All,
I have a form which has three controls which must be populated or else the database would be useless. The fields are
TodaysDate
cboSelName
cbo_PickTime

I've tried the code below which seemed to work one day and then not work the following day which i found strange ? Can anybody see something obvious ?

Thanks

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord And Me.TodaysDate <= Date And TimeValue(Me.TimeNow) > #10:00:00 AM# Then
        MsgBox "Bookings can NOT be made after 10:00am" & vbCrLf & _
            "Please book for tomorrow.", , "Booking Cancelled"
        Cancel = True
        Me.Undo
        Me.cboSelName.SetFocus
        End If
        
        If IsNull(TodaysDate.Value) Then
  MsgBox "You must select a date."
  Cancel = True
  TodaysDate.SetFocus
   End If
        
        If IsNull(cboSelName.Value) Then
  MsgBox "You must enter your name."
  Cancel = True
  cboSelName.SetFocus
    End If


If IsNull(cbo_PickTime.Value) Then
  MsgBox "You must select a time slot."
  Cancel = True
  cbo_PickTime.SetFocus
  End If
End Sub

 

[arnelgp]

maybe change their position in the code:

Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(TodaysDate.Value) Then
        MsgBox "You must select a date."
        Cancel = True
        TodaysDate.SetFocus
        Exit Sub
    End If
        
    If IsNull(cboSelName.Value) Then
        MsgBox "You must enter your name."
        Cancel = True
        cboSelName.SetFocus
        Exit Sub
    End If


    If IsNull(cbo_PickTime.Value) Then
        MsgBox "You must select a time slot."
        Cancel = True
        cbo_PickTime.SetFocus
        Exit Sub
    End If
    
    If Me.NewRecord And Me.TodaysDate <= Date And TimeValue(Me.TimeNow) > #10:00:00 AM# Then
        MsgBox "Bookings can NOT be made after 10:00am" & vbCrLf & _
            "Please book for tomorrow.", , "Booking Cancelled"
        Cancel = True
        Me.Undo
        Me.cboSelName.SetFocus
    End If
        
End Sub

 

[Emma35]

Thanks arnelgp...i gave it a try but it still lets me move to the subform without any warning messages

 

[Gasman]

Perhaps they are not Null but ZLS ?

 

[Emma35]

So how would i change the code if that were the case ?

 

[Gasman]

Something along the lines of

If Nz(TodaysDate.Value,"") = "" Then

 

[Emma35]

Thanks for the suggestion but it still lets me move to the subform without filling in the text box

 

[Gasman]

Thanks for the suggestion but it still lets me move to the subform without filling in the text box

Well then you need to start walking through the code and use breakpoints to examine exactly what you have in those controls and follow the logic path being used.
Is that even the correct event, if you move to a subform? I have no idea? Walking through the code will tell you though.

 

[Emma35]

The main form has the three controls i mentioned in the original post....and the subform just allows the user to add some more detail. It's just that it's no good having the subform details filled out if there is no name and date in the main form. I'm sure it's the correct event as i've used this code before, which is why i'm confused as to why it's not working in this instance !

 

[isladogs]

You could make these required fields.

 

[Gasman]

The main form has the three controls i mentioned in the original post....and the subform just allows the user to add some more detail. It's just that it's no good having the subform details filled out if there is no name and date in the main form. I'm sure it's the correct event as i've used this code before, which is why i'm confused as to why it's not working in this instance !

That may well be, but if it was me, I'd be looking to see if the form is behaving as I *think* it should?
Upload the DB for someone to look at.?

 

[Isaac]

Your control names aren't exactly the same as the underlying bound column names, are they?

I usually test controls using:

If ("" & Controlname)=""

or else test the length of same.

 

[Deleted member 147267]

AFAIK, if you have 3 bound controls on a form and you open the form and go right to the subform, the main form before update event won't run - there's nothing to update. Is this what's happening?

Or do you have some bound controls on the main form, one or more is getting edited but some are not? In that case the event should run. If it does but your code is not working as expected, that is a different issue.

If controls are edited but they are not bound, same thing - nothing to update.

You could make the table fields required as noted (assuming they're bound), but I'd be looking at why the event doesn't run some times. Put a break on the event and see if it fires. If it doesn't, and the controls are bound and edited, then I'd go to the property sheet in design view and click on the ellipses for the event just to make sure you then end up looking at the procedure. Sometimes they are not connected, but I very much doubt that is a 'sometimes' thing - at least not on the same db on the same pc.

 

[Isaac]

+1 vote for that approach, I think Gasman/Micron are giving sound advice. Step #1 is to set the breakpoint in the BeforeUpdate code to find out if it's firing at all. If it is firing, then set more breakpoints to determine the value of controls at that time. But it looks a lot like it might not even be firing.
This is one of those situations where the developer's expectations are wholly dependent on what "should/might" happen when something gains/loses focus, which can muddle things a bit. Just had a convo about that earlier today.

Your expectation seems to be that anytime you "go to the subform", that BeforeUpdate event will fire...which if true, is probably a bit too broad of an expectation, especially if the form is not dirtied, as mentioned by others.

Forcing code to go into Break mode and then performing further tests while in Break mode is one of the single most valuable tools available!

 

[Pat Hartman]

You have combined the test for date and time and that may be what is causing your issue. It is hard to tell because we don't have any data to work with.

    If Me.NewRecord Then
        if Me.TodaysDate < Date Then
            msgbox "Bookings may not be made for previous dates.", vbOKOnly
            Cancel = True
            Me.TodaysDate.Undo
            Me.TodaysDate.SetFocus
            Exit Sub
        Else
            If Me.TodaysDate = Date Then
                If TimeValue(Me.TimeNow) > #10:00:00 AM# Then
                    MsgBox "Bookings can NOT be made after 10:00am" & vbCrLf & _
                    "Please book for tomorrow.", , "Booking Cancelled"
                    Cancel = True
                    Me.TimeNow.Undo
                    Me.TimeNow.SetFocus
                end If
            Else
                '' what to do if date > today
            End If
        End If
    End If

PS, arne graciously corrected your alignment issues. Even though the compiler doesn't care, people make many more mistakes with coding when they are sloppy about aligning it in meaningful ways. However, he didn't correct your field references. The most efficient way to reference a control is with:

Me.somefieldname

.Value is the default property and so can be omitted for clarity. using Me. gives you intellisense which will be very helpful in preventing coding errors. For example, once you get used to seeing it, when it doesn't pop up, you will immediately know you have made a typo.

Controls have THREE data properties.
Me.somefieldname.Text -- the property you should use when coding events such as on Change because you want to refer to the type in buffer.
Me.somefieldname.Value or Me.somename -- the current data property of the control. This can be different from .Text
Me.somefieldname.OldValue -- the data value retrieved from the table when the form is loaded. For new records, this property will always be null. For existing records it will only be null if the underlying record contains null for this field.

Always know the data property you want since they can all be different depending on what control your code is running in.

 

[arnelgp]

on Main form, use this code:

Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(TodaysDate.Value) Then
        MsgBox "You must select a date."
        Cancel = True
        TodaysDate.SetFocus
        Exit Sub
    End If
        
    If IsNull(cboSelName.Value) Then
        MsgBox "You must enter your name."
        Cancel = True
        cboSelName.SetFocus
        Exit Sub
    End If


    If IsNull(cbo_PickTime.Value) Then
        MsgBox "You must select a time slot."
        Cancel = True
        cbo_PickTime.SetFocus
        Exit Sub
    End If
    
    If Me.NewRecord And Not IsTimeAllowed() Then
        MsgBox "Bookings can NOT be made after 10:00am" & vbCrLf & _
            "Please book for tomorrow.", , "Booking Cancelled"
        Cancel = True
        Me.Undo
        Me.cboSelName.SetFocus
    End If
        
End Sub

Public Function IsTimeAllowed() As Boolean
IsTimeAllowed = Not (Me.TodaysDate <= Date And TimeValue(Me.TimeNow) > #10:00:00 AM#)
End Function

while on the SubForm, add this code:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Cancel = Not Me.Parent.IsTimeAllowed
    If Cancel Then
        MsgBox "Bookings can NOT be made after 10:00am" & vbCrLf & _
            "Please book for tomorrow.", , "Booking Cancelled"
    End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Cancel = Not Me.Parent.IsTimeAllowed
    If Cancel Then
        MsgBox "Bookings can NOT be made after 10:00am" & vbCrLf & _
            "Please book for tomorrow.", , "Booking Cancelled"
        Me.Undo
    End If

End Sub

 

[Emma35]

Thanks for the suggestions and advice guys. I think it's probably better if i just attach a stripped down version and let you have a look first hand.
arnelgp....i added the code but i still lets me skip to the subform without any message

 

[bob fitz]

In tbl_OrderDetails set the OrderID field to Required = Yes and remove any Default Value setting

 

[Minty]

I am sure form your description a that Micron's suggestion about nothing being updated is the correct one. The before update event doesn't fire because nothing has been updated.
I think the simple answer would be to disable the sub-form until there is data entered in the main form.

 

[Emma35]

I just gave it a try bob....still lets me jump to the subform with the main form blank. I've just noticed that if i enter a name and then leave the Time blank, it prompts me to enter a time ? If i then enter a Time and go back and remove the name, it will prompt me to enter a name. It's like everything starts working once i actually enter some information on the main form.