Trouble adding code on date fields (1 Viewer)

PaulD2019

Registered User.
Local time
Today, 23:08
Joined
Nov 23, 2019
Messages
75
Hi all, I'm trying to add a couple of features on one of the databases I built for use at work, one doesn't work at all & the second isn't working exactly how I want it to so I thought I would kill two birds with one stone in one thread. I've done some googling & searched on here but haven't had any luck.

Problem 1
The first thing I would like to add is code so when a selected status is selected it checks to see if two date fields are empty, if they are a MsgBox pops up telling the user to select a date, I am working with the following

cboJobStatus - Combo box to select the status of the job which has options like "Works Booked", "Quoted", "Invoiced" etc
StartDate - Short Date field to select the start date of the job once it has been booked in
FinishDate - Short Date field to select the finish date of the job

What I would like to have happen is when "Works Booked" is selected on the combo box if the StartDate & FinishDate fields are empty a message box pops up, to try & get it working I thought I would just try the StartDate field first, I tried the below code but it doesn't work

Code:
Private Sub cboJobStatus_Change()
    If Me.cboJobStatus.Value = "Works Booked" & IsNull(Me.StartDate) Then
        MsgBox "The works have been booked in, please add the start date of the works", vbInformation, "Start Date Required"
    End If
End Sub

I tried it on the AfterUpdate event of the combo box but it didn't work on that either

Edit - nothing happens with this code, no errors

Problem 2
The second thing I would like to add is for after the FinishDate is added the field is checked to make sure the FinishDate isn't before the StartDate & the opposite way around, when the StartDate is added a check is made to make sure the StartDate isn't after the FinishDate, I tried the below code on the AfterUpdate event of the FinishDate field, this code works but not until the focus goes to another field on the form

Code:
Private Sub FinishDate_AfterUpdate()
   If Me.FinishDate.Value < Me.StartDate Then
      MsgBox "The finish date can't be before the start date", vbInformation, "Date Error"
          Me.FinishDate.Value = vbNullString
              MsgBox "Please reselect the finish date of the works", vbInformation, "Finish Date Required"
                Forms!frmWorksDetails!FinishDate.SetFocus
           Me.FinishDate.BackColor = RGB (249, 237, 237)
  End If
End Sub


Private Sub FinishDate_LostFocus()
    Me.FinishDate.BackColor = RGB(255, 255, 255)
End Sub


Private Sub StartDate_AfterUpdate()
   ElseIf Me.StartDate.Value > Me.FinishDate Then
      MsgBox "The start date can't be after the finish date", vbInformation, "Date Error"
          Me.StartDate.Value = vbNullString
              MsgBox "Please reselect the start date of the works", vbInformation, "Start Date Required"
                Forms!frmWorksDetails!StartDate.SetFocus
           Me.StartDate.BackColor = RGB (249, 237, 237)
  End If
End Sub


Private Sub StartDate_LostFocus()
    Me.StartDate.BackColor = RGB(255, 255, 255)
End Sub

Any help would be appreciated, thanks
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:08
Joined
Aug 30, 2003
Messages
36,118
For starters, "doesn't work" isn't very helpful. Is there an error, if so what is it? If incorrect behavior, how so?

For the first, is it possible the bound column of the combo is an ID number rather than the text? The after update would be the appropriate event.
 

PaulD2019

Registered User.
Local time
Today, 23:08
Joined
Nov 23, 2019
Messages
75
For starters, "doesn't work" isn't very helpful. Is there an error, if so what is it? If incorrect behavior, how so?

For the first, is it possible the bound column of the combo is an ID number rather than the text? The after update would be the appropriate event.
Apologies, the combo box bound column is the actual job status ("Works Booked", "Quoted", "Invoiced" etc) not an ID field, nothing happens at all when the combo box is updated/changed, there isn't an error or anything like that
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:08
Joined
Aug 30, 2003
Messages
36,118
No apology necessary, just letting you know. So that code looks okay. Do you know how to set a breakpoint so you can what values VBA is seeing? Or can you attach the db here?
 

PaulD2019

Registered User.
Local time
Today, 23:08
Joined
Nov 23, 2019
Messages
75
I haven't done that before (still learning), unfortunately the database is a bit of a mess at the moment as I am working on rebuilding it & getting rid of stuff that isn't right on there & adding new features, it would take me ages to clear all of the data & images from it.

I've just made a quick database with the fields on & attached it, its the same on that too with the status combo code not doing anything & you have to click on another field before the date code kicks in, is that ok?
 

Attachments

  • TestDatabase.accdb
    552 KB · Views: 227

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:08
Joined
Aug 30, 2003
Messages
36,118
I'm blind. "&" is a concatenation operator, you want "And":

If Me.cboJobStatus.Value = "Works Booked" And IsNull(Me.StartDate) Then
 

PaulD2019

Registered User.
Local time
Today, 23:08
Joined
Nov 23, 2019
Messages
75
I'm blind. "&" is a concatenation operator, you want "And":

If Me.cboJobStatus.Value = "Works Booked" And IsNull(Me.StartDate) Then
Thank you pbaldy, that works now :) , I just need to figure out the other problem now
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 23:08
Joined
Jul 9, 2003
Messages
16,245
you have to click on another field before the date code kicks in, is that ok?

I have my own solution to this problem:-

Nifty Date Picker

I use a Nifty Date Picker form that can be setup to check to see if the the end date is after the beginning date.

https://www.niftyaccess.com/nifty-date-picker/

There's a free version, and a paid version.

To add the ability to automatically check the end date is after the start date you will need the CallCalled Class module to add a simulated "After Update Event"

CallCalled Class here:-


I did a playlist on OOP's programming using the Call Called Class module in an object orientated way which you can find here:-

 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:08
Joined
Aug 30, 2003
Messages
36,118
On the second issue, does it work as expected if you type in a date? I've run into the issue of using the date picker not firing the update event. If so, I've gotten around it by saving the record in the textbox change event:

Code:
    If IsDate(Me.StartDate.Text) Then
        Me.Dirty = False
    End If
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:08
Joined
Aug 30, 2003
Messages
36,118
You could probably also fire the textbox after update event instead of saving the record.
 

PaulD2019

Registered User.
Local time
Today, 23:08
Joined
Nov 23, 2019
Messages
75
I have my own solution to this problem:-

Nifty Date Picker

I use a Nifty Date Picker form that can be setup to check to see if the the end date is after the beginning date.

https://www.niftyaccess.com/nifty-date-picker/

There's a free version, and a paid version.

To add the ability to automatically check the end date is after the start date you will need the CallCalled Class module to add a simulated "After Update Event"

CallCalled Class here:-


I did a playlist on OOP's programming using the Call Called Class module in an object orientated way which you can find here:-

Thank you Uncle Gizmo, I'll have a look at those links
 

PaulD2019

Registered User.
Local time
Today, 23:08
Joined
Nov 23, 2019
Messages
75
On the second issue, does it work as expected if you type in a date? I've run into the issue of using the date picker not firing the update event. If so, I've gotten around it by saving the record in the textbox change event:

Code:
    If IsDate(Me.StartDate.Text) Then
        Me.Dirty = False
    End If
Thank you again pbaldy, it works now
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:08
Joined
Feb 19, 2002
Messages
42,977
Problem #1
Validation of this type does not belong in the Change event. The Change event runs multiple times. It runs once for each character. A better choice would be the control's BeforeUpdate event but the best choice would be the Form's BeforeUpdate event. The reason I prefer the form's BeforeUpdate event is because it doesn't run until immediately before the record is saved so by that time, all the data the user is going to enter or change is done and you won't have to worry about him jumping around the form and doing data entry out of order.

Problem #2
For the reasons I indicated in my previous answer, MOST validation should be done in the Form's BeforeUpdate event. That way you don't have to get all convoluted in you checking logic. It is much more straight forward. The code goes into a single procedure. You check the first date to make sure it is populated. You check the second date to make sure it is populated and then you compare the dates.
Code:
If IsDate(Me.StartDate) Then
    'put additional validation here as a sanity check to ensure that something like 1/1/220 isn't entered
Else
    msgbox "Start Date is required", VBOKOnly
    Cancel = True
    Me.StartDate.SetFocus
    Exit Sub
End If
If IsDate(Me.FinishDate) Then
    'put additional validation here as a sanity check to ensure that something like 1/1/220 isn't entered
Else
    msgbox "Finish Date is required", VBOKOnly
    Cancel = True
    Me.FinishDate.SetFocus
    Exit Sub
End If

If Me.FinishDate >= Me.StartDate Then
Else
    Msgbox " Start Date must be less than or equal to Finish Date", vbOKOnly
    Cancel = True
    Me.StartDate.SetFocus
    Exit Sub
End if
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:08
Joined
Aug 30, 2003
Messages
36,118
The change event was used to get around the fact that the update events don't fire if you use the date picker. I typically use an input mask so no picker, so maybe there's a better alternative.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 23:08
Joined
Jul 9, 2003
Messages
16,245
the update events don't fire if you use the date picker.
That's the problem my class module solves. You click on the control and it opens the "Nifty Date Picker" form. The date picker form initializes the Call Called Class Module. The Class captures information from the calling form. The "CallCalled" class module checks the calling form to see if it contains the the public functions fPassBackRun.

Once you have selected the date, the Pop-Up Nifty Date Picker form closes. If the function fPassBackRun is present in the Calling form, the Class runs the function fPassBackRun in which you place the code you would normally put in the after update event of the control that called it.
 

IbrBadri

Member
Local time
Tomorrow, 01:08
Joined
May 24, 2020
Messages
35
Format the date entries field
dd/mm/yy or
mm/dd/yy or
dd\/mm\/yy
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 23:08
Joined
Jul 9, 2003
Messages
16,245
Once you have selected the date, the Pop-Up Nifty Date Picker form closes. If the function fPassBackRun is present in the Calling form, the Class runs the function fPassBackRun in which you place the code you would normally put in the after update event of the control that called it.

MajP showed me a method which may be able to upgrade this process, using WithEvents. I haven't got around to trying "WithEvents" in a Class Module yet..... One step at a time!

I just realized that the term "With Events" means just that! Instead of just passing an object through and manipulating that, you can also pass an object and have the ability to use its Events.

Why not just have this ability built into a normal object variable?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:08
Joined
Feb 19, 2002
Messages
42,977
Let me try again,
When your validation code involves two or more fields, the best place to put it is in the FORM's BeforeUpdate event. If you are validating presence, the ONLY viable event is the FORM's BeforeUpdate event because none of the control level events will even run if the control never obtains the focus. By the time the Form's BeforeUpdate event runs, all data entry is complete so you don't have to worry about having one or more of the fields still empty when you do the validation. If a field is empty, it an error, period and you set the cancel argument to true to prevent Access from saving the record. None of the code in the original post will prevent bad records from being saved and isn't that the objective? Giving people nice error messages does NOT protect the data. You have to prevent the record from being saved and the only event where you can do that is the FORM's BeforeUpdate event. You can do certain, limited validation in a control's BeforeUpdate event but you almost always have to repeat the validation in the Form's BeforeUpdate event also.

Your choice is -
put all the code into the FORM's BeforeUpdate event (as I did for you in an earlier post) where it will be effective in ensuring that both dates are entered and they are in the proper relationship
OR
put your code in multiple other events, some of which won't even fire and close your eyes to the problem of saving invalid data.

It seems such a simple choice - use the correct event for final validation OR flail around.
 

isladogs

MVP / VIP
Local time
Today, 23:08
Joined
Jan 14, 2017
Messages
18,186
Pat
As @pbaldy stated back in post #15, when entering dates using a date picker control, update events do not trigger.
Therefore you cannot validate dates entered using the date picker using either the Form_BeforeUpdate event or even the BeforeUpdate event of the control.
Whilst your point is absolutely correct in general, it is NOT applicable in this instance

EDIT - Oops. I got this wrong - see posts 21 & 22
 
Last edited:

Users who are viewing this thread

Top Bottom