Change data in a field on a form

[newby2VBA]

I have a contract control number that starts with either AB or TR when it's AB in the "ContractControlNum" field I want the "Contract Status" fields on the form to return 1 and likewise when it's "TR" I want it to return 2.
I place the code below in the "On Current" on the form and it only works when I reopen the form, Not Ideal.
I tryed on After update on the form and nothing.
I tryed on After update on the ContractControlNum field an nothing changed either.
I know the code works just don't know where it should reside!

I'm at a loss for why this does not work.

Dim Lresult As String
If Not IsNull(Me![ContractControlNum]) = True Then
Lresult = Left([ContractControlNum], 2)

If Lresult = "AB" Then
Me![Contract Status] = 1
'1 = In Progress
Else
If Lresult = "TR" Then
Me![Contract Status] = 2
'2 Completed

End If
End If
End If

 

[vbaInet]

Changes to your code:

Dim Lresult As String
If IsNull([ContractControlNum]) = False Then

    Lresult = Left([ContractControlNum], 2)
  
    If Lresult = "AB" Then
        [Contract Status] = 1      ' 1 = In Progress
    ElseIf Lresult = "TR" Then
        [Contract Status] = 2      ' 2 Completed    
    End If
End If

Still put this on the On Current event.

 

[newby2VBA]

Changes to your code:

Dim Lresult As String
If IsNull([ContractControlNum]) = False Then
 
    Lresult = Left([ContractControlNum], 2)
 
    If Lresult = "AB" Then
        [Contract Status] = 1      ' 1 = In Progress
    ElseIf Lresult = "TR" Then
        [Contract Status] = 2      ' 2 Completed    
    End If
End If

Still put this on the On Current event.

Now the form works a little better when I go back and forth on the records it eventually change to reflect what the code is telling it to do.
The bigger issue to me is when I don't move to another record and close the form the data in the table is not updated since the field did not update on the form.

Any Ideas?

 

[vbaInet]

If all you're doing is analysing the data then it shouldn't be saved in your table. The code handles that.

 

[newby2VBA]

I agree the code should be doing that but it isn't. If I have TR and I change to AB I want the Contract Status field to change and since the field is linked to the table if the field does not change nor will it be reflected in the table. At the end of the day I want to run a query from the table to show me what's completed and in progress.

Does this make more sense?

 

[vbaInet]

Put that code on the After Update event of the ContractControlNum control.

 

[newby2VBA]

Thanks, sorry for the delay!

 

[vbaInet]

No worries. Are we all sorted now?

 

[newby2VBA]

No worries. Are we all sorted now?

This piece if good

 

[vbaInet]

Glad you got it working