If... Then problems

[mdwilcher]

This code is supposed to change fields in the database based on the contents of another field, but it's not working right. When the field named Sign_Up_Status is changed to "Client Signed", the field named CaseStatusID should change to "QIP" automatically. I have included a copy of the module for examination. It looks correct to me, but I am just a beginner. Please help.

Private Sub Sign_Up_Status_Change()

' 09/06/04 - JHB: When a case is changed to Client Signed, it will set the case status to QIP so that they automatically show up on QIP reports. This also checks to make sure that there isn't already a CaseStatus set (file was already set to active, etc.).

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT Intake.ID, Intake.CaseStatusID FROM Intake WHERE [Intake.ID] = " & Me("ID"))
If IsNull(rs("CaseStatusID")) And Me.Sign_Up_Status = "Client Signed" Then

CurrentDb.Execute "UPDATE Intake SET CaseStatusID = 'QIP' WHERE ID = " & Me("ID")


End If

If Me.Sign_Up_Status = "Client Signed" Then
Me.NumberofPhotosTaken.Enabled = True
Me.AdditionalMileage.Enabled = True
Else
Me.NumberofPhotosTaken.Enabled = False
Me.AdditionalMileage.Enabled = False
End If



End Sub

 

[KeithG]

Does the user update this fields Value on a form? Then when the user updates the field on the form you want to update the value in CasestatusId to QIP?

 

[mdwilcher]

Yes. It's actually 2 different forms. When the user changes the value of Sign_Up_Status to Client Signed, then CaseStatusID on another field should change to QIP

 

[KeithG]

Are the fields in the same table?

 

[mdwilcher]

Yes. Both in one table named "Intake"

 

[KeithG]

I would add CaseStatusID as an invisible field on your form, then use the AfterUpdate event on the SignUpStatus field to check wether the Statue was changed to Client Signed and if so update CaseStatusID field to QIP