I have a table that has a few simple fields. Transaction ID (TID populated with Autonumber), Date, Transaction Status (Integer), Status, and Notes. Status is a lookup field that pulls its values from another table. It shows the text of the status but stores the status ID as an integer. Notes is a memo field. Usually when we have a certain status, the Note field generally has the same text. It does change often enough that I can't have Status and Notes linked. However, I want the Notes field in my form for this table to autopopulate with the most common entries based on the status chosen when adding a new TID. Let me give you an example.
I create a new TID. I enter the date then the Status. At this point in time when I leave the Status control, I want the notes field to autopopulate with a default based on the status. Here is the code I have created but it's not working. I have tried replacing the Case number, i.e. Case 132, with "132" and the status text "Closed" but none of them work either. Any assistance would be helpful.
Private Sub Status_Ctrl_LostFocus()
Dim NoteDetail As String
Dim OffStat As Integer
OffStat = Me.Status_Ctrl.Value
If Me!Details = Null Or Me!Details = "" Then
Select Case OffStat
Case 132 'Offer Closed
NoteDetail = "Offer Closed."
Case 133 'Offer Failed
If Me.Parent!EMCust_Ctrl = 32 Then
NoteDetail = "Offer rejected. EM returned to Buyer."
Else
NoteDetail = "Offer rejected."
End If
Case 134 'Offer Accepted
NoteDetail = "Offer Accepted."
Case 164 'Offer Presented
NoteDetail = "Offer Presented. EM held for acceptance."
End Select
With Me!Details_Ctrl
.Value = NoteDetail
.SetFocus
End With
End If
End Sub
Set a break point on the OffStat = line and step through the code one line at a time with the F8 key. This condition is suspect: If Me!Details = Null ... use If IsNull(Me!Details) if you want to check whether Me!Details is Null. (It can never be equal to Null because nothing can ever be equal to Null, not even another Null.) – HansUp Nov 22 at 20:07