Update one text box based on another text box

[TinaR]

I'm using Access 2007.
I have three date fields we use for follow up:
FU1
FU2
FU3
Then I have a text box I've named Status and a check box I've named Not
Interested.

The idea is for us to enter a date in FU1 after the first follow up is
completed. Once the date is entered in FU1, the Status text box is updated to
read "First Follow Up". Same goes for FU2 and FU3.
If the Not Interested check box is checked off, the Status is updated to "Not
Interested". This all works.
My problem is if any of the dates are removed from any of the follow up date
fields, the Status goes blank and doesn't reflect the correct status based on
the latest followup field that has a date.

I think I need to requery at some point but I'm not sure where to start. I
posted my code below. Thanks in advance for your help.
Tina

Private Sub FU1_AfterUpdate()
If Len(Trim(Nz(Me.FU1, ""))) = 0 Then
Me.Status = ""
Else
Me.Status = "First Follow Up"
End If
End Sub
------------------------------------------
Private Sub FU2_AfterUpdate()
If Len(Trim(Nz(Me.FU2, ""))) = 0 Then
Me.Status = ""
Else
Me.Status = "Second Follow Up"
End If
End Sub
------------------------------------------
Private Sub FU3_AfterUpdate()
If Len(Trim(Nz(Me.FU3, ""))) = 0 Then
Me.Status = ""
Else
Me.Status = "Third Follow Up"
End If
End Sub
------------------------------------------
Private Sub Not_Interested_Click()
If Me.Not_Interested = True Then
Me.Status = "Facility Not Interested"
Else
If Me.Not_Interested = False Then
Me.Status = ""
End If
End If
End Sub

 

[KARL DEWEY]

Why not poll this into one but put it in reverse order. I do not do much VBA
so I would need to buils it one line of code at a time and test but the idea
is something like this --
Private Sub Status_Change_Click()
If Me.Not_Interested = True Then
Me.Status = "Facility Not Interested"
Exit
Else
If Len(Trim(Nz(Me.FU3, ""))) > 0 Then
Me.Status = "Third Follow Up"
Exit
Else
If Len(Trim(Nz(Me.FU2, ""))) > 0 Then
Me.Status = "Second Follow Up"
Exit
Else
If Len(Trim(Nz(Me.FU1, ""))) > 0 Then
Me.Status = "First Follow Up"
Else
Me.Status = ""
Exit
End If
End If
End If
End If
End Sub

 

[John W. Vinson]

I'm using Access 2007.
I have three date fields we use for follow up:
FU1
FU2
FU3

And you will NEVER, EVER, under ANY circumstances need a *fourth* followup?

Sorry, but your table design is wrong. If you have a one (incident) to many
(followups) relationship, the proper design is to have two tables in a one to
many relationship; your followups table would have a link to the primary key
of this table, a sequential FollowupNo number field, a date field, and
information about the status or other information about this followup.

Then I have a text box I've named Status and a check box I've named Not
Interested.

The idea is for us to enter a date in FU1 after the first follow up is
completed. Once the date is entered in FU1, the Status text box is updated to
read "First Follow Up". Same goes for FU2 and FU3.

The Status textbox on the mainform can be based on a query counting the number
of rows in the followups table.