Solved Update TextBox based on another text box (1 Viewer)

[kobiashi]

hi all

i have two text boxes, TxtOpenStatus and TxtUnitStatus

when the TxtOpenStatus is 0 the TxtUnit Status should display "Serviceable" and if its 1 or above, then it should display "Out Of Service"

heres what ive got so far but it doesnt work, any advies would be appreciated

Private Sub TxtUnitStatus_Change()
    If Me.TxtOpenStatus = 1 Then
       Me.TxtUnitStatus = "Out Of Service"
    Else
        Me.TxtUnitStatus = "Serviceable"
    End If
End Sub

 

[Gasman]

I would have thought that txtUnitStatus would be display only?
So the code should be in the afterupdate event of the txtOpenStatus control?, or some other event for that control.?

Plus you logic appears incorrect?

    If Me.TxtOpenStatus = 0 Then
       Me.TxtUnitStatus = "Serviceable"
    Else
        Me.TxtUnitStatus =  "Out Of Service"
    End If

 

[kobiashi]

I would have thought that txtUnitStatus would be display only?
So the code should be in the afterupdate event of the txtOpenStatus control?, or some other even for that control.?

thats the first way i tried it, still didnt work

 

[Gasman]

thats the first way i tried it, still didnt work

See my update to my post

 

[kobiashi]

See my update to my post

thank for the reply, ive tried that its still not displaying anything, the only thing i havent mentioned incase its causing the probelm, the textbox is unbound.

 

[arnelgp]

you are using the Change event of TxtUnitStatus.
while you should use the Change event of TxtOpenStatus:

Private Sub TxtOpenStatus_Change()
Dim iVal As Integer
iVal = Val(Me.TxtOpenStatus.Text & "")
    If iVal  Then
       Me.TxtUnitStatus = "Out Of Service"
    Else
        Me.TxtUnitStatus = "Serviceable"
    End If
End Sub

 

[kobiashi]

ive tried all of the suggestions, but nothing is displaying, but i also get no errors, not sure what im doing wrong.

not sure if this makes a difference but both these fields are on a sub form, displayed on a main form, but both forms are not bound, TxtUnitStatus is an unbound text,

 

[arnelgp]

both these fields are on a sub form, displayed on a main form, but both forms are not bound

can you post the form and subform to see what you mean.

 

[kobiashi]

can you post the form and subform to see what you mean.

is a screen shot ok?

 

[kobiashi]

here is the form and sub form plus tables and queries

 

[MajP]

That will not work with unbound controls in a continuous form. You either need a dlookup and calculated control or make a calculated control in the query

SELECT tblunits.unitno,
       subsubqrytrainsall.ceventstatus,
       Count(tbleventtrackerspncode.spncodetype_idfk)           AS
       CountOfSPNCodeType_IDFK,
       Count(tbleventtrackbtcode.btcode_idfk)                   AS
       CountOfBTCode_IDFK,
       Iif([ceventstatus] = 1, "out of service", "serviceable") AS Status
FROM   tblunits
       LEFT JOIN ((((tbleventtracker
                     LEFT JOIN subsubqrytrainsall
                            ON tbleventtracker.eventtracker_id =
                   subsubqrytrainsall.eventtracker_id)
                    LEFT JOIN tbleventtrackbtcode
                           ON tbleventtracker.eventtracker_id =
                              tbleventtrackbtcode.eventtracker_idfk)
                   LEFT JOIN tbleventtrackerspncode
                          ON tbleventtracker.eventtracker_id =
                             tbleventtrackerspncode.eventtracker_idfk)
                  RIGHT JOIN tbleventpartlocation
                          ON tbleventtracker.eventtracker_id =
                             tbleventpartlocation.eventtracker_idfk)
              ON tblunits.unit_id = tbleventpartlocation.units_idfk
GROUP  BY tblunits.unitno,
          subsubqrytrainsall.ceventstatus,
          Iif([ceventstatus] = 1, "out of service", "serviceable");

Now you can bind directly to the column Status

 

[arnelgp]

your Event will not fire, because the query is Group Query (read-only).
better include the status in the query.
see SubQryTrainsAll query and your subform.

 

[MajP]

your Event will not fire, because the query is Group Query (read-only).

Just to be clear, even if this event could fire, it will NEVER Ever work with an unbound control set in code. All unbound controls will show the same value if you set it in code.
You either add to the query as we both showed or make a calculated control. The calculated control is done basically the same way doing an iif. like;
=IIf(Val([cEventStatus] & ""),"Out of Service","Serviceable")

I missed the part that it was 1 (or above) so use @arnelgp form for the iif.

 

[Pat Hartman]

MajP's solution to do this in a query is the optimal one. When the OpenStatus field is changed, Access will automagically update the UnitStatus for you.

A couple of things you need to know.
1. The Change event would not be used for something like this in any case because the change event runs multiple times. once for each character typed.
2. Using an unbound control for UnitStatus will cause a single value to show on ALL visible rows. It will be the value that is appropriate for the CURRENT record.
3. Viewing an existing record, the UnitStatus would not be set so you would need code in multiple form events such as the Current event so you could set the value for an existing record.

An alternative to the query, would be to use the same IIf() as the ControlSource for the unbound control:

=IIf(.....)

This method will show the correct value for each visible row the same as the query method. The methods are essentially equal but I would probably use the query method since I tend to reuse queries and that means I don't need to keep recreating the IIf() expression.

 

[kobiashi]

Gents thank you very much for this solution, the query one worked a treat, i had tried the IIF(), but missed out the VAL part of the function.


every day is a school day