MsgBox appears if Combobox holds certain values

[hedges98]

Right, I have two combo boxes - let's call them cboStatus and cboResolutionDate

Basically, what I want to happen, is when a user selects a certain option from cboStatus and cboResolutionDate is blank, a message will pop up and inform the user that they need to enter a resolution date.

I can quite happily make a message box pop up when a user selects the appropriate status but can't figure out how to combine this with cboResolutionDate being blank/null/empty.

This is one of my attempts...

Private Sub cboStatus_AfterUpdate()
If Me!cboResolutionDate = "" And Me!cboStatus.ListIndex = 3 Or Me!cboResolutionDate = "" And Me!cboStatus.ListIndex = 4 Then
MsgBox "Please enter a closure date", vbOKOnly, "Closure date missing!"
End If
End Sub

I've tried changing it about a bit but the end result is always the same. It's probably a simple solution and I'm missing something really obvious.

I was also thinking I could throw in a SetFocus to cboResolutionDate?

Thanks in advance!

[TheSmileyCoder]

The issue is that cboResultion="" wont work when cboResolution is null
Null and an empty string / 0 length string are NOT the same thing

There are a few different ways to work around this.

me.cboResolutionDate & ""=""

or

nz(me.cboResolutionDate;"")=""

These basicly yield the same result. The first will concatanate your strings. If cboResolution is null the result will be null+"" = ""
The second says if its null (the Nz function) then take the value of the second argument (""), and if not null then use the first argument.

So modifying your code:

Private Sub cboStatus_AfterUpdate() 
If isnull (Me.cboStatus) then
  'You may or may not need to handle this case, where a user 
  'Removes a value entered.
 
Else
  'Is a data value entered?
  If (me.cboResolutionDate & "")="" And (me.cboStatus.ListIndex=3 or Me.cobStatus.ListIndex=4) then
    'Field is empty
      MsgBox "Please enter a closure date", vbOKOnly, "Closure date missing!" 
      me.cboResolutionDate.SetFocus
  End If
End If
End Sub 

[TheSmileyCoder]

Assuming you have the date in a textbox and the resolution is always on the same day, you could simply do

Me.tbResolutionDate=Date()

Date() returns the current date

I usually use

Me.tbResolutionDate=Now()

Now() returns the current Data & Time
but then format the field to "YYYY-MM-DD". I find the extra bit of time information can come in handy.

[hedges98]

Awesome stuff! Thanks buddy
I just used the middle If statement from your code as the status combo box can be left blank.

Cheeeers!