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
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
Any help would be appreciated, thanks
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: