• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Trouble adding code on date fields (1 Viewer)

PaulD2019

Registered User.
Local time
Today, 09:01
Joined
Nov 23, 2019
Messages
46
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, 01:01
Joined
Aug 30, 2003
Messages
34,601
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, 09:01
Joined
Nov 23, 2019
Messages
46
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, 01:01
Joined
Aug 30, 2003
Messages
34,601
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, 09:01
Joined
Nov 23, 2019
Messages
46
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: 4

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:01
Joined
Aug 30, 2003
Messages
34,601
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, 09:01
Joined
Nov 23, 2019
Messages
46
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, 09:01
Joined
Jul 9, 2003
Messages
11,982
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, 01:01
Joined
Aug 30, 2003
Messages
34,601
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, 01:01
Joined
Aug 30, 2003
Messages
34,601
You could probably also fire the textbox after update event instead of saving the record.
 

PaulD2019

Registered User.
Local time
Today, 09:01
Joined
Nov 23, 2019
Messages
46
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, 09:01
Joined
Nov 23, 2019
Messages
46
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, 04:01
Joined
Feb 19, 2002
Messages
29,724
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, 01:01
Joined
Aug 30, 2003
Messages
34,601
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, 09:01
Joined
Jul 9, 2003
Messages
11,982
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.
 

Users who are viewing this thread

Top Bottom