Check value that was updated in combobox 2

[08211987]

I would think this would be simple. I am trying to determine if Request_Status (a combobox) is updated to Completed or Rejected and if it is to update a date field on the form (Completed) to today's date if the table field for the completed date (tbl_PSSA_Requests.Completed) is blank.

I am getting a Run-Time error 13 Type Mismatch on the first If statement below. The first time I got the error I didn't have a variable and checked the form field directly. Am I not referencing the the text box of the combobox correctly?

Below is my code?
Private Sub Request_Status_AfterUpdate()
Dim status_value As String
status_value = Me.Request_Status.Text
If status_value = "Completed" Or "Rejected" Then
If tbl_PSSA_Requests.Completed Is Null Then
Me.Completed = Date
Me.Completed.Requery
End If
End If
End Sub

Thank you.

 

[SkipVought]

hi,

If status_value = "Completed" Or status_value = "Rejected" Then

But I like this better...

Select Case status_value
  Case "Completed", "Rejected"
     If tbl_PSSA_Requests.Completed Is Null Then
        Me.Completed = Date
        Me.Completed.Requery
     End If
  Case Else
     'nothing else
End Select

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[08211987]

Thanks Skip, that's very clean.
I received an error on this statement - "If tbl_PSSA_Requests.Completed Is Null Then" - (run time error 424 Object Required) and I realized I needed to define the table I was referencing. But instead I decided to use the field on the form that is the field in the table and replaced the statement with
"If Me.Completed Is Null Then" and I am getting the same error. Can you not reference another field within the Case statement?

So now the code looks like this:
Private Sub Request_Status_AfterUpdate()
Dim status_value As String
status_value = Me.Request_Status.Text
Select Case status_value
Case "Completed", "Rejected"
If Me.Completed Is Null Then
Me.Completed = Date
Me.Completed.Requery
End If
Case Else
'nothing else
End Select
End Sub

 

[dhookom]

You should rarely use the Text property of a control. Either use the Value property of use nothing since Value is the default property.
I would try:

Private Sub Request_Status_AfterUpdate()
    Dim status_value As String
    status_value = Me.Request_Status.Value
    Select Case status_value
        Case "Completed", "Rejected"
            If IsNull(Me.Completed) Then
                Me.Completed = Date
                Me.Completed.Requery
            End If
        Case Else
            'nothing else
    End Select
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[08211987]

Excellent, thanks to you both!